Insert Into #TempTable Execute sp_Pkeys tGoods select * from #TempTable drop table #TempTable
DECLARE @tname NVARCHAR(100), @cmdText NVARCHAR(2000) -- 构造游标查找所有未建主键的表名 -- DECLARE C_1 CURSOR FOR SELECT name FROM sysobjects A WHERE type='U' AND NOT EXISTS(SELECT 1 FROM sysobjects B WHERE xtype='PK' AND B.parent_obj=OBJECT_ID(A.name))OPEN c_1 FETCH NEXT FROM c_1 INTO @tname WHILE @@fetch_status=0 BEGIN IF @tname='game' BEGIN -- 针对要修改的表,构造添加主键SQL,并执行修改(此处使用自增类型作主键) SET @cmdText = 'ALTER TABLE '+@tname+' ADD ABD INT IDENTITY(1,1) PRIMARY KEY' EXEC sp_executesql @cmdText END FETCH NEXT FROM c_1 INTO @tname END CLOSE c_1 DEALLOCATE c_1
以上代码在SQL Server 2000中通过
感谢各位、试过了可以通过.非常感谢! DECLARE @tname NVARCHAR(100), @cmdText NVARCHAR(2000) -- 构造游标查找所有未建主键的表名 -- DECLARE C_1 CURSOR FOR SELECT name FROM sysobjects A WHERE type='U' AND NOT EXISTS(SELECT 1 FROM sysobjects B WHERE xtype='PK' AND B.parent_obj=OBJECT_ID(A.name))OPEN c_1 FETCH NEXT FROM c_1 INTO @tname WHILE @@fetch_status=0 BEGIN IF @tname='game' BEGIN -- 针对要修改的表,构造添加主键SQL,并执行修改(此处使用自增类型作主键.) SET @cmdText = 'ALTER TABLE '+@tname+' ADD ABD INT IDENTITY(1,1) PRIMARY KEY' EXEC sp_executesql @cmdText END FETCH NEXT FROM c_1 INTO @tname END CLOSE c_1 DEALLOCATE c_1
Create table #tempTable(
table_qualifier varchar(20),
table_owner varchar(20),
table_name varchar(20),
Column_name varchar(20),
Key_Seq Integer,
PK_Name varchar(20)
)
Insert Into #TempTable
Execute sp_Pkeys tGoods
select * from #TempTable
drop table #TempTable
DECLARE @tname NVARCHAR(100), @cmdText NVARCHAR(2000)
-- 构造游标查找所有未建主键的表名 --
DECLARE C_1 CURSOR FOR
SELECT name FROM sysobjects A WHERE type='U'
AND NOT EXISTS(SELECT 1 FROM sysobjects B WHERE xtype='PK' AND B.parent_obj=OBJECT_ID(A.name))OPEN c_1
FETCH NEXT FROM c_1 INTO @tname
WHILE @@fetch_status=0
BEGIN
IF @tname='game'
BEGIN -- 针对要修改的表,构造添加主键SQL,并执行修改(此处使用自增类型作主键)
SET @cmdText = 'ALTER TABLE '+@tname+' ADD ABD INT IDENTITY(1,1) PRIMARY KEY'
EXEC sp_executesql @cmdText
END
FETCH NEXT FROM c_1 INTO @tname
END
CLOSE c_1
DEALLOCATE c_1
DECLARE @tname NVARCHAR(100), @cmdText NVARCHAR(2000)
-- 构造游标查找所有未建主键的表名 --
DECLARE C_1 CURSOR FOR
SELECT name FROM sysobjects A WHERE type='U'
AND NOT EXISTS(SELECT 1 FROM sysobjects B WHERE xtype='PK' AND B.parent_obj=OBJECT_ID(A.name))OPEN c_1
FETCH NEXT FROM c_1 INTO @tname
WHILE @@fetch_status=0
BEGIN
IF @tname='game'
BEGIN -- 针对要修改的表,构造添加主键SQL,并执行修改(此处使用自增类型作主键.)
SET @cmdText = 'ALTER TABLE '+@tname+' ADD ABD INT IDENTITY(1,1) PRIMARY KEY'
EXEC sp_executesql @cmdText
END
FETCH NEXT FROM c_1 INTO @tname
END
CLOSE c_1
DEALLOCATE c_1