List of all tables in DB, with row count sp

,

This stored procedure will list all user tables in the current database, along with the number of rows in the table.
This version uses dynamic SQL to retrieve the number of rows, to overcome the need for an index on all tables.
I use this to document legacy databases, I find that they may have tables w/o PK, FK, or any Indices. The variants of this sp that use sysindexes table will not report a row count in this situation.

CREATE PROCEDURE sp_RowCount AS
-- DAL 11/22/2000
-- This routine assumes that the current database is the one to build
-- Row Count statements for all user tables
-- Returns a record set of the table name and row count
--
SET NOCOUNT ON
DECLARE @TableName sysname
	, @Rows int
	, @SQL nvarchar(4000)

CREATE TABLE #tablelist 
	(
	TableName varchar(128), 
	Records int 
	)

DECLARE tables_cursor CURSOR LOCAL FAST_FORWARD FOR
	SELECT name
	FROM sysobjects
	WHERE type = 'U' AND name NOT LIKE 'dt%'
	ORDER BY name

OPEN tables_cursor
FETCH NEXT FROM tables_cursor into @TableName

WHILE @@FETCH_STATUS = 0
BEGIN

	SET @SQL = 'SELECT @Rows = COUNT(*) FROM ['+@TableName+']'
	SET @Rows = 0
	EXEC sp_executesql @SQL, N'@Rows int out', @Rows out

	set @SQL = 'INSERT INTO #tablelist (TableName,Records) ' +
		'VALUES ( '+'''' + CONVERT(varchar,@TableName) + ''''+', ' + CONVERT(varchar,@Rows) + ' )'
	-- PRINT @SQL
	EXEC(@SQL)

	FETCH NEXT FROM tables_cursor into @TableName
END

CLOSE tables_cursor
DEALLOCATE tables_cursor

SET NOCOUNT OFF

SELECT * FROM #tablelist ORDER BY TableName

DROP TABLE #tablelist	
GO

Rate

5 (1)

Share

Share

Rate

5 (1)