俺这临时DBA没有权力，但有些东东，还得龟腚一下。以后严禁使用游标。不让用，得有个替代的办法吧。这个方法别人用了N年，俺也用了N年，CSDN的贴子里也贴了N年，俺再多贴一次，也未不可。
-----------------------
-- 查找未关闭的游标。
-----------------------
CREATE TABLE ##游标 ( Var_DBName VarChar(255),Var_ProcName VarChar(255) );
DECLARE @Var_DbNaem VarChar(255);
DECLARE @NVr_Cmd NVarChar(4000);
SELECT @Var_DbNaem=MIN([NAME]) from master..sysdatabases
WHILE NOT @Var_DbNaem IS NULL BEGIN
SET @NVr_Cmd='
INSERT INTO ##游标(Var_ProcName,Var_DBName)
SELECT distinct 名称,''' +@Var_DbNaem + '''
FROM (
select
b.Name as 名称
,a.Name as 参数
,c.Text as 语句
from
[' +@Var_DbNaem + '].DBO.syscolumns a
left join
[' +@Var_DbNaem + '].DBO.sysobjects b on a.ID=b.ID
join
[' +@Var_DbNaem + '].DBO.syscomments c on c.ID=b.ID
where
b.xtype in(''FN'',''IF'',''TF'',''P'') AND C.TEXT LIKE ''%CURSOR%'' AND (NOT C.TEXT LIKE ''%DEALLOCATE%'')
) AS T'
PRINT @NVR_CMD
EXECUTE(@NVR_CMD);
SELECT @Var_DbNaem=MIN([NAME]) from master..sysdatabases WHERE [NAME]>@Var_DbNaem
END
SELECT * FROM ##游标
DROP TABLE ##游标
GO
USE TEST
GO
CREATE TABLE LX1(
INT_I INT,
VAR_A VARCHAR(32),
VAR_B VARCHAR(32)
)
GO
INSERT INTO LX1 VALUES(1,'A','E')
INSERT INTO LX1 VALUES(2,'B','F')
INSERT INTO LX1 VALUES(3,'C','G')
INSERT INTO LX1 VALUES(4,'D','H')
GO
---------------------------
--原表能确定唯一的单一主键。
--如果是多主键，可以在表变量中添加自增字段（IDENTITY）或字符型的组合字段（ KEY1 + '|' + KEY2 + '| + KEY3 ... ) 。
---------------------------
DECLARE @INT_I INT
DECLARE @VAR_A VARCHAR(32)
DECLARE @VAR_B VARCHAR(32)
DECLARE @TAB_LX TABLE (INT_I INT,VAR_A VARCHAR(32),VAR_B VARCHAR(32))
INSERT INTO @TAB_LX SELECT * FROM LX1
SELECT @INT_I=MIN(INT_I) FROM @TAB_LX
WHILE NOT @INT_I IS NULL BEGIN
SELECT @VAR_A=VAR_A,@VAR_B=VAR_B FROM @TAB_LX WHERE INT_I=@INT_I
--提取值
PRINT @VAR_A + ' ' + @VAR_B
SELECT @INT_I=MIN(INT_I) FROM LX1 WHERE INT_I>@INT_I
END
GO
---------------------------
--原表没有唯一主键
--可在表变量中添加一个自增编号。
---------------------------
DECLARE @INT_ID INT
DECLARE @VAR_A VARCHAR(32)
DECLARE @VAR_B VARCHAR(32)
DECLARE @TAB_LX TABLE (INT_ID INT IDENTITY(1,1) ,INT_I INT,VAR_A VARCHAR(32),VAR_B VARCHAR(32))
INSERT INTO @TAB_LX (INT_I,VAR_A,VAR_B) SELECT * FROM LX1
SELECT @INT_ID=MIN(INT_ID) FROM @TAB_LX
WHILE NOT @INT_ID IS NULL BEGIN
SELECT @VAR_A=VAR_A,@VAR_B=VAR_B FROM @TAB_LX WHERE INT_ID=@INT_ID
---提取值。
PRINT @VAR_A + ' ' + @VAR_B
SELECT @INT_ID=MIN(INT_ID) FROM @TAB_LX WHERE INT_ID>@INT_ID
END
GO
