Rowcount of all tables in a database… | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Rowcount of all tables in a database…

Hi, In the query attached below, i tried to find the rowcount of all the tables in a database and insert the result into a table along with the tablename. I was successful with the rowcount. however, when i tried to insert the tablename along with the rowcount, i am not able to do it. Can anyone help me through with this problem? code attached below… DECLARE @@TableName varchar(50)
,@Query varchar(50) CREATE TABLE #TableRowCount (Rowcnt int)
CREATE TABLE TableRowCount (TableName varchar(30), Rowcnt int) DECLARE CursorTableName CURSOR FOR
SELECT Name FROM sysobjects where type = ‘u’ ORDER BY Name OPEN CursorTableName FETCH CursorTableName into @@TableName WHILE @@fetch_status = 0
BEGIN
INSERT INTO #TableRowCount (Rowcnt)
EXECUTE (‘SELECT count(*) FROM ‘ + @@TableName) INSERT INTO TableRowCount
EXECUTE(‘SELECT TOP ‘+ @@fetch_status + ‘ @@TableName, Rowcnt
FROM #TableRowCount’) FETCH CursorTableName INTO @@TableName
END CLOSE CursorTableName
DEALLOCATE CursorTableName Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"
This will more or less give you what you want Insert into yourTable(tableName,rowcount)
Select object_name(id),rows from sysindexes where indid<2
order by object_name(id) Select * from yourTable Madhivanan Failing to plan is Planning to fail
Madhi, what i need is the exact count. the sysindexes table may not be always correct if i am not runnning dbcc dbreindex regularly or my sysindexes is not uptodate or if i dont have any index for my table. is there any other way to get the rowcount??? Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"
Why not you can run DBCC UPDATEUSAGE to get the correct values whenever required. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Just run DBCC UpdateUsage before trying that query<br /><br />Edit : Satya already suggested [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
The database we are using is occupying just over 1 TB of space. nearly 3 tables have just over 1000 million rows and over 40 tables have 10 million or more rows. And not all the above mentioned tables have index. my question is how much time does "DBCC UPDATEUSAGE" eat and does this really work for the table which dont have an index? Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"
Then why do you want updated row count in this case, simply capture from SYSINDEXES table. If you run COUNT(*) from bigger table it will have same performance problem. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
i had a wrong prenotion that, tables which doesnt have an index will not have an entry in sysindexes table. i worked my queries on that myth. Thanks for your suggestions satya and madhi Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"
]]>