记录sql server 的批量删除主外键的sql语句

b.name TableName,a.name TypeName,a.* from sysobjects a inner join sysobjects b on a.parent_obj=b.id and b.xtype=‘U‘ --删除约束 F外键、PK主键、D 约束、UQ 唯一约束 declare @tableName varchar(max),@typeName varchar(max) declare fk_cursor cursor for select b.name TableName,a.name TypeName from sysobjects a inner join sysobjects b on a.parent_obj=b.id and b.xtype=‘U‘ where a.xtype=‘PK‘ open fk_cursor fetch next from fk_cursor into @tableName,@typeName while @@FETCH_STATUS = 0 begin exec (‘ALTER TABLE [dbo].[‘+@tableName+‘] DROP CONSTRAINT [‘+@typeName+‘]‘) fetch next from fk_cursor into @tableName,@typeName end close fk_cursor deallocate fk_cursor

select b.name TableName,a.name IndexName from sys.sysindexes a right join sys.sysobjects b on a.id=b.id where b.xtype=‘U‘ and a.name is not null

--删除IX 索引
declare @tableName varchar(max),@indexName varchar(max)
declare index_cursor cursor for select b.name TableName,a.name IndexName from sys.sysindexes a right join sys.sysobjects b on a.id=b.id where b.xtype=‘U‘ and a.name is not null

open index_cursor

fetch next from index_cursor into @tableName,@indexName
while @@FETCH_STATUS = 0
begin 
    exec (‘DROP INDEX [‘+@indexName+‘] ON [dbo].[‘+@tableName+‘]‘)
    fetch next from index_cursor into @tableName,@indexName
end
close index_cursor
deallocate index_cursor



select b.name TableName,a.name ColumnName,a.* from syscolumns a 
inner join sysobjects b on b.id=a.id and b.xtype=‘U‘
inner join systypes c on a.xtype=c.xtype and c.name=‘uniqueidentifier‘

--修改uniqueidentifier的类型为nvarchar(max)
declare @tableName varchar(max),@columnName varchar(max)
declare change_type_cursor cursor for select b.name TableName,a.name ColumnName from syscolumns a 
inner join sysobjects b on b.id=a.id and b.xtype=‘U‘
inner join systypes c on a.xtype=c.xtype and c.name=‘uniqueidentifier‘

open change_type_cursor
fetch next from change_type_cursor into @tableName,@columnName
while @@FETCH_STATUS =0
begin
    exec (‘ALTER TABLE [dbo].[‘+@tableName+‘] ALTER COLUMN [‘+@columnName+‘] nvarchar(max) NOT NULL‘)
    fetch next from change_type_cursor into @tableName,@columnName
end
close change_type_cursor
deallocate change_type_cursor

 

记录sql server 的批量删除主外键的sql语句

标签：记录   har   lte   open   cat   pen   pes   --   bsp