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

The Transact-SQL script below 1Please ensure you test any script taken from my website on a test/development machine, before running on a production server., 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

Updated: Thursday, 19th December, 2019 — 11:05 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.