写一个游标给没有主键的表,分别加上主键

具体思路:先查出没有主键的表的名字,然后通过游标给每张表加上主键
代码如下:
use zentrack
go

DECLARE @Table_Name varchar(30), @sql varchar(1000)

DECLARE nameCur CURSOR FOR
/*
SELECT s.name as [Login Name]
FROM sys.server_principals s INNER JOIN sys.database_principals d
ON s.sid = d.sid and default_database_name = ‘zentrack’
*/
SELECT [name]
FROM zentrack.sys.tables
WHERE [name] NOT IN
(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE CONSTRAINT_NAME LIKE ‘PK%’)

OPEN nameCur
FETCH NEXT FROM nameCur INTO @Table_Name
WHILE (@@FETCH_STATUS = 0)
BEGIN

SET @sql = ‘ALTER table ‘ + @Table_Name +’ ADD pri_id int IDENTITY (1,1) PRIMARY KEY ‘;

print @sql
EXEC(@sql)

FETCH NEXT FROM nameCur INTO @Table_Name
END

CLOSE nameCur
DEALLOCATE nameCur

发布日期:
分类:Mysql

发表评论

邮箱地址不会被公开。 必填项已用*标注