cursor

[747576]

Hi there,

I am writing a cursor which counts the number of records for a particular table. The cursor gets the table name from a metatable. At the moment the cursor just prints the sql statement but doesn't execute it, can someone help with the syntax.

thanks

DECLARE @tablename AS VARCHAR(50)

DECLARE Record_Count CURSOR FOR
SELECT TABLENAME
FROM METATABLE
WHERE TABLETYPE = 1

OPEN Record_Count

FETCH NEXT FROM Record_Count
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN

select ''+@tablename+', count(*) from '+@tablename+''

FETCH NEXT FROM Record_Count
INTO @tablename
END

CLOSE Record_Count
DEALLOCATE Record_Count
GO

 

[fredericofonseca]

you almost got it so full solution (sing sysobjects table. change accordingly

select * from sysobjects

DECLARE @tablename AS VARCHAR(220)
DECLARE @sql AS VARCHAR(2250)

DECLARE Record_Count CURSOR FOR
SELECT [name]
FROM sysobjects
WHERE xtype = 'U'

OPEN Record_Count

FETCH NEXT FROM Record_Count
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN

select @sql = 'select ''' +@tablename+''' as [table name], count(*) from '+@tablename+''
--print @sql
execute ( @sql     )
    FETCH NEXT FROM Record_Count
    INTO @tablename
END

CLOSE Record_Count
DEALLOCATE Record_Count
GO

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com