Listing 1: Code That Uses a Nested Cursor in a Procedure

SET nocount ON 
GO

[BEGIN CALLOUT A]
USE master
GO
--
-- Using sp_executesql
--
SELECT current_db = DB_NAME()
EXEC sp_executesql @stmt = N'USE pubs; SELECT current_db = DB_NAME();'
SELECT current_db = DB_NAME()
GO
[END CALLOUT A]
[BEGIN CALLOUT B]

--
-- Using EXEC(statement)
--
EXEC ('USE pubs; select current_db = DB_NAME();')
GO
[END CALLOUT B]

--
-- Run dbcc checktable for each user table in each user database.
--
IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'sp_checktables' AND objectproperty(id, 
N'IsProcedure') = 1) 
     DROP PROCEDURE [dbo].[sp_checktables] 
GO 

CREATE PROCEDURE [dbo].[sp_checktables] 
AS 

SET nocount ON 

DECLARE     @db   sysname,
      @own  sysname,
      @tab  sysname, 
      @stmt nvarchar(4000) 

CREATE TABLE #tables
(
      [db]  sysname NOT NULL,
      [own]       sysname NOT NULL,
      [tab] sysname NOT NULL
)

DECLARE db_cursor cursor read_only FOR 
SELECT      name
FROM master.dbo.sysdatabases 
WHERE   name NOT IN (N'master', N'model', N'tempdb')

OPEN db_cursor 

FETCH NEXT FROM db_cursor into @db 
WHILE (@@fetch_status <> -1) 
BEGIN
      SELECT @stmt = N'USE ' + quotename(@db, '[') + '; SELECT db = DB_NAME(), owner = 
user_name(uid), tab = name FROM dbo.sysobjects WHERE ObjectProperty(id, N''IsUserTable'') = 1;'
      SELECT @stmt  -- debug only

      INSERT INTO #tables EXEC(@stmt)
      SELECT * FROM #tables -- debug only
      DECLARE tab_cursor cursor read_only FOR 
      SELECT      own, tab FROM #tables

      OPEN tab_cursor 

      FETCH NEXT FROM tab_cursor into @own, @tab
      WHILE (@@fetch_status <> -1) 
      BEGIN 

            SELECT @stmt = 'use ' + quotename(@db, '[') + N'; dbcc checktable('''+ quotename(@own, '[') + 
'.' + quotename(@tab, '[') + ''', NOINDEX) WITH PHYSICAL_ONLY;' 

            SELECT @stmt  -- debug only
            EXEC  (@stmt)

            FETCH NEXT FROM tab_cursor INTO @own, @tab
      END 

      CLOSE tab_cursor 
      DEALLOCATE tab_cursor 

      TRUNCATE TABLE #tables

      FETCH NEXT FROM db_cursor INTO @db 
END 

DROP TABLE #tables

CLOSE db_cursor 
DEALLOCATE db_cursor 
GO

EXEC dbo.sp_checktables