SQL:多个游标嵌套实例

SQL Server 2008R2 环境多个游标嵌套使用例子。

打印系统的用户表以及表的列清单

/*
Author: Samrv8
Date: 2023-1-19
Description: 打印系统的用户表以及表的列清单
环境： SQL SERVER 2008R2 
*/
DECLARE C1 CURSOR FOR 
SELECT TB.NAME AS TABLE_NAME, TB.OBJECT_ID 
  FROM SYS.TABLES  TB
 WHERE TB.TYPE= 'U' 
-- AND TB.NAME IN ( 'EBCFG','SEWST')
 ORDER BY TB.NAME ;

/*
 -- SWEI	994102582
DECLARE C2 CURSOR FOR 
 SELECT COL.NAME AS COLUMN_NAME 
  FROM SYS.ALL_columns COL,
 SYS.TABLES  TB
  WHERE TB.OBJECT_ID = COL.OBJECT_ID
   AND TB.TYPE= 'U'
   --AND COL.OBJECT_ID = 994102582
   AND TB.NAME = @TABLE_NAME  
ORDER BY COL.OBJECT_ID, COL.COLUMN_ID;
*/
DECLARE @TABLE_NAME NVARCHAR(32);  -- 表名
DECLARE @COLUMN_NAME NVARCHAR(32);  -- 列名
DECLARE @OBJECT_ID BIGINT;  -- 对象ID
DECLARE @FETCH_STATUS1 INT;  -- 表游标状态 
DECLARE @FETCH_STATUS2 INT;  -- 列游标状态

OPEN C1 
FETCH NEXT FROM C1 INTO @TABLE_NAME ,@OBJECT_ID
SET @FETCH_STATUS1 = @@FETCH_STATUS;
WHILE @FETCH_STATUS1 = 0 
BEGIN 
   PRINT 'SELECT TOP 90 * FROM '+ @TABLE_NAME +' ;';
   PRINT '/*';
    -- SWEI	994102582
DECLARE C2 CURSOR FOR 
 SELECT COL.NAME AS COLUMN_NAME 
  FROM SYS.ALL_columns COL,
 SYS.TABLES  TB
  WHERE TB.OBJECT_ID = COL.OBJECT_ID
   AND TB.TYPE= 'U'
  -- AND COL.OBJECT_ID = 994102582
  AND TB.NAME = @TABLE_NAME  
ORDER BY COL.OBJECT_ID, COL.COLUMN_ID;

   OPEN C2 
   FETCH NEXT FROM C2 INTO @COLUMN_NAME 
   SET @FETCH_STATUS2 = @@FETCH_STATUS;
   WHILE @FETCH_STATUS2 = 0 
   BEGIN 
     PRINT ''+ @COLUMN_NAME + ':';
	 FETCH NEXT FROM C2 INTO @COLUMN_NAME;
	 SET @FETCH_STATUS2 = @@FETCH_STATUS;
   END ; -- END C2; 
   close C2;                   --关闭游标
   deallocate C2;
    PRINT '*/';
FETCH NEXT FROM C1 INTO @TABLE_NAME ,@OBJECT_ID; 
SET @FETCH_STATUS1 = @@FETCH_STATUS;
END ; -- END C1;
close C1                   --关闭游标
deallocate C1;