Table RecordCounts - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Table RecordCounts

Author

Topic

itsmarkdavies
Starting Member

14 Posts

Posted - 2006-03-08 : 09:20:43

Can anyone give me some T-SQL to do an insert into a table of all the TableNames and RecordCounts of ALL User Tables in a given SQL Server Database please ?

What I want is something like this :-

Table1 0
Table2 12
Table3 356
Table4 0
....etc

I have managed to do it with a Cursor but it is a bit untidy and slow, and I am sure someone will have something better.

Thanks,

mark.davies@npower.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-08 : 09:33:50

This is more or less give you correct result

select s.name,i.rows from sysobjects s inner join sysindexes i
on s.id=i.id where i.indid<2 and s.xtype='u' order by s.name

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-08 : 09:39:58

you could build a dynamic sql from information_schema views and the run it.

or run this in your db:


select	distinct o.name, i.rows 
from	sysindexes i
		join sysobjects o on i.id = o.id
where	o.xtype = 'U' and o.name <> 'dtproperties'
		and i.indid < 2

this need your statistics to be up to date.

Go with the flow & have fun! Else fight the flow

itsmarkdavies
Starting Member

14 Posts

Posted - 2006-03-08 : 09:40:15

Thanks Madhivanan, I was aware of this method but I need to be exact and this does not always return the correct results.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-08 : 09:41:05

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-08 : 09:50:14

quote:
Originally posted by itsmarkdavies

Thanks Madhivanan, I was aware of this method but I need to be exact and this does not always return the correct results.

Then you should use Select count(*)

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-08 : 09:55:54

Feel free to use a cursor to loop through the names of the tables in your database. But definitely don't use a cursor to calculate the row counts!

just declare your cursor, loop through table names, SELECT COUNT(*) from each, and off you go.

The cursor will in no way be a bottleneck for your performance if used this way.

itsmarkdavies
Starting Member

14 Posts

Posted - 2006-03-08 : 10:05:55

Thanks for that Dr. Cross Join. I've sussed it now. See code below for anyone interested :-

CREATE TABLE #TablesList(Server_Name VARCHAR(10), Table_Name VARCHAR(50), Record_Count NUMERIC(15, 0))

DECLARE @TableName VARCHAR(50)

DECLARE MyTables CURSOR FOR
SELECT LTrim(RTrim(CONVERT(CHAR(50), OBJ.[Name]))) FROM sysobjects OBJ
WHERE OBJ.Type = 'U' ORDER BY OBJ.[Name] ASC

OPEN MyTables
FETCH NEXT FROM MyTables INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('INSERT #TablesList SELECT @@SERVERNAME, ''' + @TableName + ''', COUNT(*) FROM ' + @TableName)
FETCH NEXT FROM MyTables INTO @TableName
END

CLOSE MyTables
DEALLOCATE MyTables

SELECT * FROM #TablesList
DROP TABLE #TablesList

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-08 : 18:12:25

Non cursor solution

declare @sql nvarchar(500)

create table #TablesList(DataBase_Name varchar(10), Table_Name varchar(50), Record_Count NUMERIC(15, 0))

select @sql = 'select ''insert into #TablesList select db_name(), '''''' + name + '''''', count(*) from '' + name from sysobjects where type = ''U'''
print @sql

exec master..xp_execresultset @sql, N'test'

select * from #TablesList order by DataBase_Name, Table_Name

drop table #TablesList

----------------------------------
'KH'

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-08 : 18:39:31

just keep in mind that the "non-cursor solution" gives you absolutely no advantage over the cursor solution.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-09 : 05:08:50

i guess that this falls into that 1% when cursors are ok

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources