DBCC FREEPROCCACHE -- 清空执行计划缓存
DECLARE @couName int, @tableName NVARCHAR(50),@sql NVARCHAR(150),@sql1 NVARCHAR(200) --声明变量，需要读取的数据
DECLARE cur CURSOR static --声明静态游标
FOR
select Name From SysObjects Where xtype='U';
OPEN cur --打开游标
FETCH NEXT FROM cur INTO @tableName --取数据
WHILE ( @@fetch_status = 0 ) --判断是否还有数据
BEGIN
set @sql ='select @couName = COUNT(*) from dbo.sysobjects where xtype=''PK'' and parent_obj=(select id from dbo.sysobjects where name=@tableName )';
EXEC sp_executesql @sql,N'@couName int output, @tableName NVARCHAR(50)' ,@couName output, @tableName;
select @tableName as "表名"
if(@couName > 0)
BEGIN
select @tableName as 表名,@couName as 主键
END
else
BEGIN
if(@tableName != 'wmanage' and @tableName != 'strategy' and @tableName !='strain_para')
begin
print('ALTER TABLE "'+ @tableName+ '" ADD pk_id int NOT NULL IDENTITY (1,1), CONSTRAINT PK_'+@tableName+' PRIMARY KEY CLUSTERED (pk_id)')
exec('ALTER TABLE "'+ @tableName+ '" ADD pk_id int NOT NULL IDENTITY (1,1), CONSTRAINT "PK_'+@tableName+'" PRIMARY KEY CLUSTERED (pk_id)')
-- print('ALTER TABLE '+ @tableName+ ' ADD pk_id int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY')
-- exec('ALTER TABLE "'+ @tableName+ ' " ADD pk_id int NOT NULL INT NOT NULL PRIMARY KEY AUTO_INCREMENT "0"')
-- set @sql1 = 'ALTER TABLE @tableName ADD id int identity(1,1) PRIMARY KEY'
-- EXEC sp_executesql @sql1,N'@tableName NVARCHAR(50)',@tableName
end
END
FETCH NEXT FROM cur INTO @tableName --这里一定要写取下一条数据
END
CLOSE cur --关闭游标
DEALLOCATE cur
转载请注明来源，欢迎对文章中的引用来源进行考证，欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论，也可以邮件至 ggchzzz@163.com
