Cursor Problem - 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 2005 Forums

Transact-SQL (2005)

Cursor Problem

Author

Topic

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-04-22 : 09:06:14

Never been a fan of Cursors but trying to being in different options for finding table counts,

i've got to

DECLARE @Count INT
DECLARE @TableName VARCHAR(100)

DECLARE TableCount CURSOR
FOR

SELECT
Table_Name
FROM
Information_schema.tables
WHERE
Table_Type = 'Base Table'
AND
Table_Name NOT LIKE 'Confli%'
AND
Table_Name NOT LIKE 'Remove%'
AND
Table_Name NOT LIKE 'MS%'
AND
Table_Name NOT LIKE 'sys%'
AND
Table_Name NOT LIKE 'Temp%'

OPEN TableCount

declare @TableName varchar(50)
declare @Count int

FETCH NEXT FROM TableCount INTO @TableName

WHILE @@Fetch_status = 0
BEGIN

SELECT @Count = (SELECT Count(*) FROM @TableName)

PRINT @TableName +' '+ Cast(@Count as char(10))

FETCH NEXT FROM TableCount into @TableName

END

CLOSE TableCount

DEALLOCATE TableCount

but then this is returning error:

Msg 1087, Level 15, State 2, Line 31
Must declare the table variable "@TableName".

but this has been declared so not sure now what the problem is, only being a junior dba I was wondering are you able to use a variable in a from statement or would i need to use dynamic sql which is another area along with cursors i've never really delved into so any help would be appricated - otherwise it all a learning curve

Thanks guys

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-22 : 09:20:50

quote:
Originally posted by NeilG

SELECT @Count = (SELECT Count(*) FROM @TableName)

declare @tablename varchar(20)
set @tablename = 'tallynumbers'

DECLARE @SQL NVARCHAR(1000), @i BIGINT

SET @SQL = 'SELECT @rc = COUNT_BIG(*) FROM ' + QUOTENAME(@TableName)

EXEC sp_executesql @SQL, N'@rc BIGINT OUT', @rc = @i OUT

select @i

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-22 : 09:31:06

[code]SELECT QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) AS TableName,
i.records AS TotalRecords
FROM sys.tables AS t
INNER JOIN (
SELECT id,
MAX(RowCnt) AS records
FROM sysindexes
GROUP BY id
) AS i ON i.id = t.[object_id][/code]

E 12°55'05.63"
N 56°04'39.26"

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-04-22 : 09:37:26

Thanks peso i'll have a look at them two

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-04-22 : 09:57:58

how about a naughty way? (using sysobjects and sysindex).

select 
	 'TABLE NAME'= QUOTENAME(SCHEMA_NAME(i.groupid)) + '.' + QUOTENAME(o.name), 'ROWS'=i.rows
from 
	sysobjects o
inner join 
	sysindexes i
on 
	o.id = i.id
where 
	o.type = 'U'      
and 
	i.indid in (0,1)
order by 
	o.name

whoops. returns incorrect schema owner

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-22 : 10:08:54

Note that if you want to query on system tables to know row count, make sure you run this first

DBCC UPDATEUSAGE ('database_name','table_name') WITH COUNT_ROWS.

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources