通过在SQL Server 2000中通过T-Sql创建游标来创建脚本对表的唯一约束 [英] To create script the uniue constraints to table by creating cursor through T-Sql in sql server 2000

问题描述

我想通过
创建游标来创建脚本对表的唯一约束 T-Sql.
下面是我的T-Sql代码.
但是当我运行此代码时，它将生成错误msg(即-``@ColName''附近的语法不正确)
我如何通过创建游标来解决下面的代码???
请任何人在蚂蚁指导中帮助我....

我的T-Sql代码:-
-------------

I want to create script the uniue constraints to table by creating cursor through
T-Sql.
Below my T-Sql code is given.
But when i run this code, it is generate the error msg(i.e.-Incorrect syntax near ''@ColName'')
How can i solve this below code through Cursor creating?????
please anybody help me in ant instruction....

My T-Sql code:--
-------------

DECLARE @cONSNAME VARCHAR(50), @ColName sysname, @kEYTYPE VARCHAR(10)

DECLARE objCONS CURSOR FOR 
	(select name, ColName = COL_NAME (OBJECT_ID('table_name'), colid)
	from sysindexes	idx
	inner join sysindexkeys ik on ik.id = idx.id and idx.indid = ik.indid
	where idx.id = 485576768)
OPEN objCONS
FETCH NEXT FROM objCONS INTO @cONSNAME, @ColName, @kEYTYPE
WHILE @@FETCH_STATUS = 0
BEGIN
	IF (@kEYTYPE = 'UNIQUE')
		BEGIN
		IF NOT EXISTS(SELECT * FROM SysObjects WHERE id = 485576768 AND xtype = 'UQ')
			ALTER TABLE ['table_name'] WITH NOCHECK ADD CONSTRAINT [@cONSNAME] UNIQUE NONCLUSTERED
			(@ColName) ON [PRIMARY]
		END

FETCH NEXT FROM objCONS INTO @cONSNAME, @ColName, @kEYTYPE
END
CLOSE objCONS
DEALLOCATE objCONS

推荐答案

尝试一下

Try this

DECLARE @cONSNAME VARCHAR(50), @ColName sysname, @kEYTYPE VARCHAR(10)
 DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)

DECLARE objCONS CURSOR FOR 
	(select name, ColName = COL_NAME (OBJECT_ID('table_name'), colid)
	from sysindexes	idx
	inner join sysindexkeys ik on ik.id = idx.id and idx.indid = ik.indid
	where idx.id = 485576768)
OPEN objCONS
FETCH NEXT FROM objCONS INTO @cONSNAME, @ColName, @kEYTYPE
WHILE @@FETCH_STATUS = 0
BEGIN
	IF (@kEYTYPE = 'UNIQUE')
		BEGIN
		IF NOT EXISTS(SELECT * FROM SysObjects WHERE id = 485576768 AND xtype = 'UQ')		
			SET @SQLString = N'ALTER TABLE [''table_name''] WITH NOCHECK ADD CONSTRAINT [@cONSNAME] UNIQUE NONCLUSTERED
			(@ColName) ON [PRIMARY]'
                   
			SET @ParmDefinition = N'@ColName sysname'
			
			EXECUTE sp_executesql
			@SQLString,
			@ParmDefinition,
			@ColName = @ColName
			
		END
 
FETCH NEXT FROM objCONS INTO @cONSNAME, @ColName, @kEYTYPE
END
CLOSE objCONS
DEALLOCATE objCONS

希望这会有所帮助，如果是，那么请接受并投票答复.任何对此的任何疑问/问题都将受到欢迎.

谢谢&问候
RDBurmon.Sr.软件工程师

Hope this helps , If yes then plz accept and vote the answer. Any queries / questions on this are always welcome.

Thanks & Regards
RDBurmon.Sr.Software Engineer

这篇关于通过在SQL Server 2000中通过T-Sql创建游标来创建脚本对表的唯一约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！