records - 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)

records

Author

Topic

PatDeV
Posting Yak Master

197 Posts

Posted - 2006-08-14 : 09:34:54

Hi all,

i need to return total records from several tables.(around 30)
so how can i return those records without writing separate
select count * from tables

thanks

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-08-14 : 09:55:36

Is this a statis requirement - in which case write SP's that return the necessary data, or an ad hoc reporting requirement?

you will need to do the:
select count(*) from Table_1
...
select count(*) from Table_30

to get accurate data, since the only other way is querying SQL's internal statistics tables, and those may not necessarily be accurate, and are a bad way to do this, since yhou are accessing SQL's internal tables, which should be avoided wherever possible.

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-14 : 10:16:41

If it is a one-time operation, it can be practical to use the undocumented

sp_msforeachtable 'select ''?'' TableName, count(*) RecordCount from ?'

Peter Larsson
Helsingborg, Sweden

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-14 : 10:27:14

U May use the following


DBCC UPDATEUSAGE ('MYDB_Name')

SELECT o.[name], i.rowcnt
FROM sysobjects o
LEFT OUTER JOIN sysindexes i
ON o.[id] = i.[id]
WHERE o.xtype = 'U' AND i.indid < 2 
AND o.[name] in 
(<[List of ur tables seperated by commas] or [Select Col_TblName from Tbl_TblNames]>) 
ORDER BY o.[name]

Srinika

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-08-14 : 10:57:25

alternatively, you could use dynamic SQL to avoid using the MS system tables, and the MS undocumented process (which, btw, will return values for all tables, and not just he set you may want.

have a look at the following:


SET NOCOUNT ON 

DECLARE @TablesToCount TABLE (TableNames SYSNAME)
INSERT INTO @TablesToCount
		SELECT	'table1'
UNION	SELECT	'table2'
UNION	SELECT	'table3'
UNION	SELECT	'table4'
UNION	SELECT	'table5'
UNION	SELECT	'table6'
UNION	SELECT	'table7'
UNION	SELECT	'table8'
UNION	SELECT	'table9'
UNION	SELECT	'table10'
--SELECT [Name] FROM INFORMATION_SCHEMA.TABLES WHERE [Table_Name] in ('')

DECLARE 
	@SQLCommand	VARCHAR (8000),	--the script can be quite large.
	@Debug		INT

SET @SQLCommand = ''
SET @debug = 1
SELECT	@SQLCommand=@SQLCommand+'SELECT COUNT(*) as [CountOutPut_'+TableNames+'] FROM ['+TableNames+'];
' FROM	@TablesToCount
IF @Debug = 1 SELECT @SQLCommand
IF @Debug = 0 EXEC (@SQLCommand)

While this is quite ugly (the hardcoded table), you will note I've commented out the section which allows you to use the SQL approved schema views (these will remain statis across different versions of SQL, as opposed to system tables, which MS can change freely. The advantage of that approac (as with Srinika's posting) is you can reduce the set of table - you don't ahve to report on all tables in the database. If you want to report on all tables, you could simply remove the where criteria, and query the INFORMATION_SCHEMA.TABLES view.

in debug mode, this generates the following as a SQL statement:


SELECT COUNT(*) as [CountOutPut_table1] FROM [table1];
SELECT COUNT(*) as [CountOutPut_table10] FROM [table10];
SELECT COUNT(*) as [CountOutPut_table2] FROM [table2];
SELECT COUNT(*) as [CountOutPut_table3] FROM [table3];
SELECT COUNT(*) as [CountOutPut_table4] FROM [table4];
SELECT COUNT(*) as [CountOutPut_table5] FROM [table5];
SELECT COUNT(*) as [CountOutPut_table6] FROM [table6];
SELECT COUNT(*) as [CountOutPut_table7] FROM [table7];
SELECT COUNT(*) as [CountOutPut_table8] FROM [table8];
SELECT COUNT(*) as [CountOutPut_table9] FROM [table9];

In the execute mode, it would execute those, and run the result to output.

in fact, for your purposes, the table variable is also unnecesary. You could go with:


SET NOCOUNT ON 
DECLARE 
	@SQLCommand	VARCHAR (8000),	--the script can be quite large.
	@Debug		INT
SET @SQLCommand = ''
SET @debug = 1
SELECT	@SQLCommand=@SQLCommand+'SELECT COUNT(*) as [CountOutPut_'+Table_Name+'] FROM ['+Table_Name+'];
' FROM INFORMATION_SCHEMA.TABLES --where Table_Name in ('') or Table_Name like '' -- this is left pto your need
IF @Debug = 1 SELECT @SQLCommand
IF @Debug = 0 EXEC (@SQLCommand)

which generated:


SELECT COUNT(*) as [CountOutPut_spt_fallback_db] FROM [spt_fallback_db];
SELECT COUNT(*) as [CountOutPut_spt_fallback_dev] FROM [spt_fallback_dev];
SELECT COUNT(*) as [CountOutPut_spt_fallback_usg] FROM [spt_fallback_usg];
SELECT COUNT(*) as [CountOutPut_spt_monitor] FROM [spt_monitor];
SELECT COUNT(*) as [CountOutPut_spt_values] FROM [spt_values];
SELECT COUNT(*) as [CountOutPut_MSreplication_options] FROM [MSreplication_options];

on my master db.

PS... w00t - this is my 1000'th post.

CiaO

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources