What's wrong with my code? - 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

General SQL Server Forums

New to SQL Server Programming

What's wrong with my code?

Author

Topic

jeb0323
Starting Member

26 Posts

Posted - 2006-04-25 : 15:45:38

I want to print out the table name and record count whenever a table has record(s), but the statement:
Print 'TableName: '+@TableName +' Row Count: '+CAST(@RCTR AS varchar)
never gets executed.

Any help is appreciated.

******************************************
SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @SQL nvarchar(4000), @RCTR int

SET @TableName = ''
SET @RCTR = 0

WHILE @TableName IS NOT NULL
BEGIN
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_NAME)
), 'IsMSShipped') = 0
)

SET @SQL = ( 'select count(*) from ' + @TableName )
EXEC (@SQL)
SET @RCTR = @@ROWCOUNT
IF @RCTR > 0
Print 'TableName: ' + @TableName + ' Row Count: ' + CAST(@RCTR AS varchar)
END
*********************************

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-04-25 : 16:46:59

try this. I changed your dynamic sql to a call to sp_executesql to use an output variable and slightly changed your method of getting @tablename


DECLARE @TableName nvarchar(256), @SQL nvarchar(4000), @RCTR int

SET @TableName = ''
SET @RCTR = 0

WHILE @TableName IS NOT NULL
BEGIN
	SELECT @TableName = MIN(QUOTENAME(TABLE_NAME))
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_TYPE = 'BASE TABLE'
	AND QUOTENAME(TABLE_NAME) > @TableName
	AND OBJECTPROPERTY(
	OBJECT_ID(QUOTENAME(TABLE_NAME)
	), 'IsMSShipped') = 0

	
	SET @SQL = 'select @rctr = count(*) from ' + @TableName
	EXEC sp_executesql @sql, N'@rctr int output', @rctr output
	IF @RCTR > 0 
	Print 'TableName: ' + @TableName + ' Row Count: ' + CAST(@RCTR AS varchar)
END

Be One with the Optimizer
TG

jeb0323
Starting Member

26 Posts

Posted - 2006-04-26 : 10:47:34

It works perfect. Thanks TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources