Lising of all the tables and their row counts

Recently i needed to delete tables with a lot of records in CRM. i needed to find out the table name and the row count. i was able to do this by the following T SQL Command.


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


i was mostly helped by the following article which contained the above T SQL command. Thanks guys.

http://www.sqlservercentral.com/scripts/Miscellaneous/30765/