Record Counts from Tables - 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)

Record Counts from Tables

Author

Topic

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-09-14 : 19:19:08

Trying to get all of the record counts from every table where my condition is true.

Tried using inline statements like:
Select @PrmRecCount = Count(*) from ' + @MyTableName
kept getting the must declare error.

Declare @r as Integer, @MyTableName sysname,
@sSql as nvarchar(1000), @PrmTableName NVARCHAR(80), @PrmRecCount Integer

SET @PrmTableName = N'@MyTableName sysname'
Set @r = 1

DECLARE cursor_ProjectIDTables CURSOR FAST_FORWARD
FOR Select Top 10 o.Name from sysobjects o, syscolumns c where o.id = c.id and c.Name = 'PROJECT_ID' and o.xtype = 'U'
OPEN cursor_ProjectIDTables

FETCH NEXT FROM cursor_ProjectIDTables INTO @MyTableName
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
Set @sSql = 'Select @PrmRecCount = Count(*) from ' + @MyTableName

DECLARE cursor_RecCnt CURSOR FAST_FORWARD
FOR @sSql
OPEN cursor_RecCnt
FETCH NEXT FROM cursor_RecCnt INTO @PrmRecCount
CLOSE cursor_ProjectIDTables
DEALLOCATE cursor_ProjectIDTables

Print @sSql
END

FETCH NEXT FROM cursor_ProjectIDTables INTO @MyTableName

END

CLOSE cursor_ProjectIDTables
DEALLOCATE cursor_ProjectIDTables
GO

Surf On Dude!

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-09-14 : 19:34:45

The inner cursor for record count will not compile and errors on @sSql

Surf On Dude!

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-09-14 : 19:43:28

Clarification:
You're only looking for record counts for every table containing a column called 'PROJECT_ID'?

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-09-14 : 19:46:30

Correct.

Surf On Dude!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-14 : 19:50:14

Does this need to be part of an application or you can you just use the output of this:

SELECT 'SELECT COUNT(*) FROM ' + TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'PROJECT_ID'

Tara

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-09-14 : 19:57:04

I actually need the record count result in a variable...

DECLARE cursor_RecCnt CURSOR FAST_FORWARD
FOR SELECT COUNT(*) FROM dtproperties
OPEN cursor_RecCnt
FETCH NEXT FROM cursor_RecCnt INTO @PrmRecCount
CLOSE cursor_ProjectIDTables
DEALLOCATE cursor_ProjectIDTables

Surf On Dude!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-14 : 20:01:21

You are probably getting an error because COUNT(*) returns an int. You need to convert to varchar in order to concatenate it in your dynamic sql string.

Use the INFORMATION_SCHEMA views instead of the system objects!

Tara

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-09-14 : 20:02:29

Attempting to resolve using something like this
Declare @r as Integer
SET @r = (SELECT COUNT(*) FROM DELIVERY_SLATE)
Print Cast(@r as VarChar(10))
--Return @r
My Select statement would come from your previous post:
SELECT 'SELECT COUNT(*) FROM ' + TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'PROJECT_ID'

Surf On Dude!

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-09-14 : 20:29:33

How do I execute the "Select Count(*)" and return it back to a variable when the select is it self generated at run time?

Surf On Dude!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-15 : 07:21:35

count of rows for tables containing "PROJECT_ID"

USE <mydb>
CREATE TABLE ##thetable( thetable SYSNAME, thecount INT )

EXEC sp_execresultset N'
SELECT ''INSERT ##thetable SELECT '''''' + TABLE_NAME + '''''',COUNT(*) FROM '' + TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = ''PROJECT_ID''
'
SELECT * FROM ##thetable

DROP TABLE ##thetable

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources