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
