SQL Server table data count in Excel?

[ansarij]

Hello All, I am new to SQL server and VBA, would like to import the table rows count to my excel spread sheet. I have 2000 tables in MS SQL Server.
My objective is to get the table name from column A of spreadsheet, execute count(*) query for table and return the value to next column in spreadsheet.

Thanks
Ansarij

 

[Colin Legg]

Hi and welcome to the forum.

This is quite a difficult task for someone who is new to both VBA and SQL Server. I'll give you some key words to research:

You can use a For Each... Next loop to iterate through the cells containing the table names. In the loop you can use each table name to dynamically build a T-SQL query string and then use ADO to connect to your SQL server database to execute the query and return the row count.

That said, I don't particularly like the COUNT(*) approach you have suggested. If you just want a row count of all tables in your database then you can write much more efficient queries than using COUNT(*) for every table. Table names and row counts are housed in sys.objects and sys.partitions. Google is your friend. Use SSMS to write and test your query first before trying to replicate it in Excel.