SQL Tip: Row count on all tables in the database

The problem: You are migrating data from an old system into a new SQL Server database. You need to clear the database to re-run the migration for testing. Time goes on, tables are added and removed and you not sure if your SQL clear script is getting all the tables.

The Solutions: Run your clear script and check to see if any tables still have rows. This code will show you a row count for every table in your SQL database.

declare @cmd nvarchar(max)

set @cmd=null

select @cmd = coalesce(@cmd + ' union all ', '') +

N'SELECT ''' +

quotename(table_catalog) + N'.' +

quotename(table_schema) + N'.' +

quotename(table_name) +

N''' AS TableName, COUNT(*) AS "Rows" ' +

N' FROM ' + quotename(table_catalog) + N'.' +

quotename(table_schema) + N'.' +

quotename(table_name)

from information_schema.tables

exec sp_executesql @cmd