SQL: Count number of records in each table within a specific list of databases

The Transact-SQL script below 1, returns a list of tables for a specific list of database, and a count of the number of records within each table.

USE Master
GO
--Table to hold the list of databases:
CREATE TABLE #DatabasesToCount
( DBName VARCHAR(100) NOT NULL PRIMARY KEY)

CREATE TABLE #TableRecordCount
( DBName VARCHAR(100), TableName VARCHAR(100), RecordCount INT)

INSERT INTO #DatabasesToCount (DBName) VALUES ('Database1') -- Change to specific database 1
INSERT INTO #DatabasesToCount (DBName) VALUES ('Database2') -- Change to specific database 2
INSERT INTO #DatabasesToCount (DBName) VALUES ('DatabaseX') -- Change to specific database 3

DECLARE @Database VARCHAR(100)
DECLARE @TableName VARCHAR(500)
DECLARE @SQL NVARCHAR(1000)

DECLARE db CURSOR LOCAL FAST_FORWARD
FOR SELECT QUOTENAME(DBName) FROM #DatabasesToCount

OPEN db
FETCH NEXT FROM db INTO @Database

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @SQL = 'DECLARE tbl CURSOR GLOBAL FAST_FORWARD FOR '
	SET @SQL = @SQL + 'SELECT QUOTENAME(S.name) + ' + CHAR(39) + '.' + CHAR(39) + ' + QUOTENAME(T.name) '
	SET @SQL = @SQL + 'FROM ' + @Database + '.sys.tables T '
	SET @SQL = @SQL + 'JOIN ' + @Database + '.sys.schemas S ON T.schema_id = S.schema_id '
	SET @SQL = @SQL + 'ORDER BY 1'
	PRINT @SQL
	EXECUTE sp_executeSQL @SQL

	OPEN tbl
	FETCH NEXT FROM tbl INTO @TableName
	WHILE @@FETCH_STATUS = 0
	BEGIN
		  SET @sql = 'INSERT INTO #TableRecordCount (DBName, TableName, RecordCount) '
		  SET @sql = @sql + 'SELECT ''' + @Database + ''' AS DBName, ''' + @TableName + ''' AS TableName, COUNT(*) AS CNT FROM ' + @Database + '.' + @TableName
		  EXEC (@sql)
		  FETCH NEXT FROM tbl INTO @TableName
	END
	CLOSE tbl
	DEALLOCATE tbl

	FETCH NEXT FROM db INTO @Database
END
CLOSE db
DEALLOCATE db

SELECT * FROM #TableRecordCount

DROP TABLE #TableRecordCount
DROP TABLE #DatabasesToCount
GO
  1. Please ensure you test any script taken from my website on a test/development machine, before running on a production server.
Updated: Friday, 6th April, 2018 — 10:49 am

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.