Query total number of records in a schema

Please help out the neophyte!

I'm trying to query a database for the total number of records in each table owned by a particular schema in SQL Server 2000. I can query INFORMATION_SCHEMA.TABLES for a list of the tables in a particular schema, but how do I pass these table names to variable that can be used to count(*) the records in each? Can anyone show me how this should be done using simple TSQL?

Thanks.

Chris

Give this a shot:

Code Snippet

DECLARE @.TableName varchar(100)

DECLARE @.SQL nvarchar(MAX)

DECLARE SchemaCounter CURSOR FOR

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

OPEN SchemaCounter

FETCH NEXT FROM SchemaCounter INTO @.TableName

WHILE @.@.FETCH_STATUS = 0

BEGIN

SET @.SQL = 'SELECT COUNT(*) [' + @.TableName + ' COUNT] FROM [' + @.TableName + ']'

EXEC sp_executesql @.SQL

FETCH NEXT FROM SchemaCounter INTO @.TableName

END

CLOSE SchemaCounter

DEALLOCATE SchemaCounter

|||There is a couple of ways to get it through T-SQL, but I would not call them "simple" in neither case:

Code Snippet

select o.name, i.rowcnt
from sysindexes i
inner join sysobjects o on o.id = i.id
where o.type = 'U'
and i.indid in (0,1)

Or this one (still works in MSSQL2005):

Code Snippet

exec sp_MStablespace 'MyTableName'

The only drawback of these methods is that they used undocumented or deprecated features of SQL Server. Only hope you know what the word "undocumented" really means in MSSQL.


PS There is also a way to use sp_MSForEachTable system stored proc, but it's also undocumented .|||

Hey...

rowcnt in sysindexes

It is very dangerous to use. One can not assure it gives exact number of rows and I am not sure if we can use this or not?

Regards,

Subbu

|||

cannot trust rowcnt in sysindexes. what do u say ?

|||

Yes.. Here the stright forward query (without undocumented sps)

Code Snippet

Select

OBJ.NAME [TableName],

IND.RowCnt as [RowCount]

From

dbo.sysindexes IND

Join sysobjects OBJ on IND.ID = OBJ.ID and indid < 2 And OBJ.Type='U'

order By 1

|||

Hey, the neophyte thanks you for your help. A nice simple solution to my problem.

Chris

|||RowCnt is not updated in "real time". It not ever guaranteed to be 100% accurate. However, if you only want a very reliable "ballpark" number, it works really well. I use it all the time with the caviat that the number is a guidline, rather than "in-stone."