SQL server 批量添加主键脚本

–SQL批量添加主键脚本

–操作提示：运行此脚本前请记得备份您要操作的数据库
–实现功能：执行脚本后数据库中的所有数据表都会有一个主键
–添加规则1:主键名称为ID(可自己修改),数据类型为整形自增一
–添加规则2:如果数据表已有主键，则不添加
–添加规则3:如果数据表没主键但已存在自增一的标识列，则设置该标识列为主键
–添加规则4:如果数据表没主键且已存在ID列，则会删除掉该列，记得要检查该ID列是否包含重要数据，如果有的话，请执行脚本前改名。

–脚本代码开始
declare @ColumnName nvarchar(250)
set @ColumnName=’ID’ –要新增的主键列名,可自己修改

declare @TableName nvarchar(250)–游标中取出的数据表名
declare @TableID INT–游标中取出的数据表编号
declare @IdentityColumnName nvarchar(250)–数据表的已有标识列名称

–声明读取数据库所有数据表名和编号的游标
declare mycursor1 cursor for select name,id from dbo.SysObjects WHERE OBJECTPROPERTY(ID, ‘IsUserTable’) = 1 order by name
–打开游标
open mycursor1
–从游标里取出数据赋值到我们刚才声明的数据表名变量和数据表编号变量中
fetch next from mycursor1 into @TableName,@TableID
–如果游标执行成功
while (@@fetch_status=0)
begin

–判断当前数据表是否存在主键,如果数据表已有主键则不添加
IF NOT EXISTS (select * from information_schema.key_column_usage where TABLE_NAME=”+@TableName+”)
begin
–如果数据表中已经存在标识列，则将标识列设置为主键

if Exists(Select top 1 1 from sysobjects Where objectproperty(id, ‘TableHasIdentity’) = 1 and upper(name) = upper(@TableName))
begin
Select @IdentityColumnName=name from syscolumns Where id = @TableID and columnproperty(id, name, ‘IsIdentity’) = 1
print ‘当前数据表[‘+@TableName+’]没有主键但有标识列[‘+@IdentityColumnName+’]’
exec (‘ALTER TABLE [‘+@TableName+’] ADD CONSTRAINT PK_’+@TableName+’ PRIMARY KEY CLUSTERED (‘+@IdentityColumnName+’) ON [PRIMARY]’)
print ‘成功设置数据表[‘+@TableName+’]已有标识列[‘+@IdentityColumnName+’]为主键’
end
else
begin
print ‘当前数据表[‘+@TableName+’]没有主键和标识列’
IF EXISTS (select * from syscolumns Where ID=OBJECT_ID(N'[‘+@TableName+’]’) and name=”+@ColumnName+”)
begin
–如果已有ID列设置有索引，则删除数据表@TableName中指定字段@ColumnName对应的所有约束
declare @ConstraintName varchar (250)–定义当前查询的约束变量

–声明读取数据表中指定字段对应的所有约束列表游标
declare mycursor2 cursor for select name from sysobjects left join sysconstraints on sysconstraints.constid=sysobjects.id where parent_obj=OBJECT_ID(”+@TableName+”) and colid=(select colid from syscolumns where id=OBJECT_ID(”+@TableName+”) and OBJECTPROPERTY(id, N’IsUserTable’) = 1 and upper(name)=upper(@ColumnName))
–打开游标
open mycursor2
–从游标里取出数据赋值到约束名称变量中
fetch next from mycursor2 into @ConstraintName
–如果游标执行成功
while (@@fetch_status=0)
begin
–删除当前找到的约束
exec (‘ALTER TABLE [‘+@TableName+’] DROP CONSTRAINT [‘+@ConstraintName+’]’)
print ‘已成功删除数据表[‘+@TableName+’]字段[‘+@ColumnName+’]对应的约束[‘+@ConstraintName+’]’
–用游标去取下一条记录
fetch next from mycursor2 into @ConstraintName
end
–关闭游标
close mycursor2
–撤销游标
deallocate mycursor2

–如果存在列[ID]则先删除该列
exec (‘ALTER TABLE [‘+@TableName+’] DROP COLUMN ‘+@ColumnName+”)
–添加数据表的主键列ID
exec (‘ALTER TABLE [‘+@TableName+’] ADD ‘+@ColumnName+’ [int] IDENTITY(1,1) NOT NULL PRIMARY KEY’)
end
else
begin
–如果存在列[ID]则直接添加主键列ID
exec (‘ALTER TABLE [‘+@TableName+’] ADD ‘+@ColumnName+’ [int] IDENTITY(1,1) NOT NULL PRIMARY KEY’)
end

print ‘成功设置数据表[‘+@TableName+’]列[‘+@ColumnName+’]为主键’
end
end
else
begin
print ‘当前数据表[‘+@TableName+’]已有主键’
end
–用游标去取下一条记录
fetch next from mycursor1 into @TableName,@TableID
end

–关闭游标
close mycursor1
–撤销游标
deallocate mycursor1
–脚本代码结束