May 18, 2006 at 9:33 am
Is there a SQL command that will let me find the number rows of every table in a SQL server database? In other words a command or sp that when run would output the number of rows in every table in one go. I have a SQL database that has gotten rather large and would like to figure out which table is using all of the space without having to go through Enterprise Manager or QA and look at them individually.
Thanks!
Ken
May 18, 2006 at 9:41 am
You can use an undocumented procedure
sp_msforeachtable 'SELECT ''?'',COUNT(*) FROM ?'
also you could create a temp table and insert the results to it
Far away is close at hand in the images of elsewhere.
Anon.
May 18, 2006 at 9:46 am
Another (documented) option & rather fast...
DBCC
SHOWCONTIG WITH TABLERESULTS
May 18, 2006 at 9:50 am
Run this through QA, output to File. Open the file in QA and run. Or, output to text and cut/paste into new QA window and run.
set nocount on
SELECT 'CREATE TABLE #tmpTableCount (TableName varchar(50),TableCount int)'
SELECT 'INSERT INTO #tmpTableCount (TableName, TableCount) SELECT ''' + [Name] + ''' ,COUNT(*) FROM ' + [Name]
FROM SYSOBJECTS
WHERE xtype = 'U'
SELECT 'SELECT TableName, TableCount FROM #tmpTableCount'
SELECT 'DROP TABLE #tmpTableCount'
May 18, 2006 at 10:22 am
Thanks! those did the trick
Ken
May 22, 2006 at 4:04 am
If an approximatve value is enough for you then this would give you what you want:
selectO.name,
rowcnt
fromsysindexes I join sysobjects O on O.id = I.id
whereindid < 2
andO.type = 'U'
order
byO.name
You can improve the exactness by running dbcc updateusage (0) with count_rows
Bye
Gabor
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply