Record Count of each table in a Database

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 299
USE DatabaseName
GO
IF OBJECT_ID('TableCounts') IS NULL
CREATE TABLE TableCounts
(
Cnt int,
TableName varchar(100)
)
DECLARE @SQL NVARCHAR(MAX)
SELECT @sql = 'insert TableCounts' + CHaR(10) + CHAR(13)+ querystring from (select ' select ISNULL(SUM(1), 0), '+ ''''+ table_schema + '.'+table_name + ''' from '+ table_schema + '.'+table_name + ' UNION ALL '
FROM information_schema.tables for xml path('')) s(querystring);
SET @sql = LEFT ( @sql, LEN(@sql)-LEN('UNION ALL'))
SELECT DATALENGTH(@sql)
EXEC(@SQL)
GO
SELECT * FROM TableCounts Order by TableName
DROP TABLE TableCounts

Comments or Responses

Login to post response