データベース内のすべてテーブルの行数を出力するプロシージャ

DECLARE @table_name varchar(128), @row_count int, @query nvarchar(255)

DECLARE tables_cursor CURSOR FOR
SELECT
name
FROM
sysobjects
WHERE
type = 'U'
ORDER BY
name

IF object_id('tempdb..#TablesRowCount', 'U') IS NOT NULL
DROP TABLE tempdb..#TablesRowCount

CREATE TABLE #TablesRowCount( TableName varchar (128), Rows int )

OPEN tables_cursor;
FETCH NEXT FROM tables_cursor INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @query = N'SELECT @row_count = COUNT(*) FROM ' + @table_name
EXEC sp_executesql @query, N'@row_count int OUTPUT', @row_count OUTPUT
INSERT INTO #TablesRowCount VALUES (@table_name, @row_count)
FETCH NEXT FROM tables_cursor INTO @table_name
END

SELECT * FROM #TablesRowCount

CLOSE tables_cursor
DEALLOCATE tables_cursor