Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • Last Modified:

SQL CURSOR PROBLEM

I need to a script that uses a cursor and dynamic SQL to output one row from each base user table in the AP database and specifically exclude the table named “dtproperties” from the result set.
Here is what i got but its not wroking because ofhte declare statements.

declare onerow CURSOR
STATIC FOR

SELECT TableName = MIN(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND
      TABLE_NAME <> 'dtproperties'

EXEC ('SELECT COUNT(*) AS CountOf' + TableName +
      ' FROM ' + TableName)

open onerow
fetch next from onerow
while @@fetch_status = 0
fetch next from onerow
close onerow
deallocate onerow
0
tagtekin
Asked:
tagtekin
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hi tagtekin,

Please see the alternative methods down..

DECLARE @tableName varchar(120)
declare onerow CURSOR
STATIC FOR

SELECT  TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND
      TABLE_NAME <> 'dtproperties'

open onerow
fetch next from onerow Into @TableName
while @@fetch_status = 0
BEGIN
    EXEC ('SELECT COUNT(*) AS CountOf' + @TableName +
          ' FROM ' + @TableName)
    fetch next from onerow Into @TableName
END
close onerow
deallocate onerow
GO


exec sp_MSForEachTable "SP_SpaceUsed '?' "
GO
exec sp_MsForEachTable "SELECT COUNT(*) AS CountOf  FROM   ? " 

Aneesh
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now