SQL: List of database tables, with count of records.

Use the script below 1, to return a list of database tables (the schema and table name) along with a count of the records within each table. This can come in useful if you are trying to compare databases.

DECLARE @TableName VARCHAR(100)
DECLARE @Schema VARCHAR(100)
DECLARE @SQL VARCHAR(1000)
DECLARE TableCursor CURSOR FAST_FORWARD FOR
SELECT Name, schema_name(schema_id) AS SchemaName FROM sys.objects WHERE type = 'U' ORDER BY SchemaName, Name
OPEN TableCursor
SET NoCount ON
CREATE TABLE #tabrowcount
( SchemaName VARCHAR(200),
  TableName VARCHAR(200),
  TableRowCount INT)

FETCH NEXT FROM TableCursor INTO @TableName, @Schema
WHILE @@FETCH_STATUS = 0
BEGIN
 SET @SQL = 'INSERT INTO #tabrowcount (SchemaName, TableName, TableRowCount) '
 SET @SQL = @SQL + 'SELECT ''' + @Schema + ''' AS sch, ''' + @TableName + ''' AS tbl, COUNT(*) AS rows '
 SET @SQL = @SQL + 'FROM [' + @Schema + '].[' + @TableName + ']'
 EXEC (@SQL)
 FETCH NEXT FROM TableCursor INTO @TableName, @Schema
END

CLOSE TableCursor
DEALLOCATE TableCursor

SELECT * FROM #tabrowcount

DROP TABLE #tabrowcount

As SQL Server 2000 doesn’t use Schema Names, I have adapted the above code to work with SQL Server 2000:

DECLARE @TableName VARCHAR(100)
DECLARE @SQL VARCHAR(1000)
DECLARE TableCursor CURSOR FAST_FORWARD FOR
SELECT Name FROM sysobjects WHERE type = 'U' ORDER BY Name
OPEN TableCursor
SET NOCOUNT ON
CREATE TABLE #tabrowcount
( TableName VARCHAR(200),
  TableRowCount INT)
 
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
 SET @SQL = 'INSERT INTO #tabrowcount (TableName, TableRowCount) '
 SET @SQL = @SQL + 'SELECT ''' + @TableName + ''' AS tbl, COUNT(*) AS rows '
 SET @SQL = @SQL + 'FROM [' + @TableName + ']'
 EXEC (@SQL)
 FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor
DEALLOCATE TableCursor

SELECT * FROM #tabrowcount

DROP TABLE #tabrowcount
  1. Please ensure you test any script taken from my website on a test/development machine, before running on a production server.

Leave a Reply

Your e-mail address will not be published. Required fields are marked *

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