Row count on all tables in the database

Does anyone have a single T-SQL script that could be run against a database that would return the table name and row count for each table?

Code Snippet

declare @.cmd nvarchar(max)

set @.cmd=null

select @.cmd =coalesce(@.cmd +'; ','')+

N'SELECT COUNT(*) AS "'+

quotename(table_catalog)+ N'.'+

quotename(table_schema)+ N'.'+

quotename(table_name)+

N' Count" FROM '+quotename(table_catalog)+ N'.'+

quotename(table_schema)+ N'.'+

quotename(table_name)

frominformation_schema.tables

execsp_executesql @.cmd

|||

Alternate variation:

Code Snippet

declare @.cmd nvarchar(max)

set @.cmd=null

select @.cmd =coalesce(@.cmd +' union all ','')+

N'SELECT '''+

quotename(table_catalog)+ N'.'+

quotename(table_schema)+ N'.'+

quotename(table_name)+

N''' AS TableName, COUNT(*) AS "Rows" '+

N' FROM '+quotename(table_catalog)+ N'.'+

quotename(table_schema)+ N'.'+

quotename(table_name)

frominformation_schema.tables

execsp_executesql @.cmd

|||Thanks for the reply Dale. I get results when running this against my master database but not against my DSS database (which is the one I am really after). Is there a variation that would work for my DSS database?|||

The INFORMATION_SCHEMA.TABLES runs against the current database.

Issue USE DSS; in front of the rest of the code.

|||

A fair estimate can be get from:

-- 2000

use your_db

go

dbcc updateusage (0) withcount_rows

go

select

object_name([id]),

rowcnt

from

sysindexes

where

indid in(0, 1)

andobjectproperty([id],'IsUserTable')= 1

andobjectproperty([id],'IsMSShipped')= 0

go

-- 2005

select

object_name([object_id]),

sum([rows])as rowcnt

from

sys.partitions

where

objectproperty([object_id],'IsUserTable')= 1

andobjectproperty([object_id],'IsMSShipped')= 0

groupby

[object_id]

go

AMB