issue with count - 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)

issue with count

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-11-09 : 13:35:51

I need to combine all these count to once sql statement.so one query gives the count of each table

select count(*) from tbl_table1
select count(*) from tbl_table2
select count(*) from tbl_table3
select count(*) from tbl_table4
select count(*) from tbl_table5
select count(*) from tbl_table6
select count(*) from tbl_table7
select count(*) from tbl_table8

amachanic
SQL Server MVP

169 Posts

Posted - 2004-11-09 : 14:32:58

Is this what you're trying to do:

select count(*) AS theCount, 'table1' AS theTable from tbl_table1
UNION ALL
select count(*) AS theCount, 'table2' AS theTable from tbl_table2
UNION ALL
select count(*) AS theCount, 'table3' AS theTable from tbl_table3
UNION ALL
select count(*) AS theCount, 'table4' AS theTable from tbl_table4
UNION ALL
select count(*) AS theCount, 'table5' AS theTable from tbl_table5
UNION ALL
select count(*) AS theCount, 'table6' AS theTable from tbl_table6
UNION ALL
select count(*) AS theCount, 'table7' AS theTable from tbl_table7
UNION ALL
select count(*) AS theCount, 'table8' AS theTable from tbl_table8

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-09 : 14:51:47

or in a single row
select
table1cnt = (select count(*) from tbl_table1) ,
table2cnt = (select count(*) from tbl_table2) ,
table3cnt = (select count(*) from tbl_table3) ,
table4cnt = (select count(*) from tbl_table4) ,
table5cnt = (select count(*) from tbl_table5) ,
table6cnt = (select count(*) from tbl_table6) ,
table7cnt = (select count(*) from tbl_table7) ,
table8cnt = (select count(*) from tbl_table8)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2004-11-11 : 13:50:47

Or perhaps...


USE Northwind
GO

SET NOCOUNT OFF
DECLARE @sql varchar(8000), @mySQL99 varchar(8000)
DECLARE myCursor99 CURSOR
FOR
SELECT 'SELECT ''' + '[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' 
	+ ''' AS TABLE_NAME, COUNT(*) AS ROW_COUNT FROM [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' AS mySQL99 
FROM INFORMATION_SCHEMA.Tables
 WHERE TABLE_TYPE = 'BASE TABLE'

SELECT @sql = ''

OPEN myCursor99
FETCH NEXT FROM myCursor99 INTO @mySQL99 

WHILE @@FETCH_STATUS = 0
  BEGIN 
	SELECT @sql = @sql + @mySQL99 + ' UNION ALL '
	FETCH NEXT FROM myCursor99 INTO @mySQL99 
  END

CLOSE myCursor99
DEALLOCATE myCursor99

SELECT @sql = LEFT(@sql,LEN(@sql)-11) 
EXEC(@sql)
GO

SET NOCOUNT ON
GO

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources