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.
[codesyntax lang=”sql”]
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[/codesyntax]