List of all tables in DB, with row count sp

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