记录sqlserver的批量删除主外键的sql语句 [数据库教程]

Z时代
2024-01-10
分类：综合

database

select 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 forselect 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].[‘[email protected]+‘] DROP CONSTRAINT [‘[email protected]+‘]‘)
    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 forselect 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 [‘[email protected]+‘] ON [dbo].[‘[email protected]+‘]‘)
    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 forselect 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].[‘[email protected]+‘] ALTER COLUMN [‘[email protected]+‘] nvarchar(max) NOT NULL‘)
    fetch next from change_type_cursor into @tableName,@columnName
end
close change_type_cursor
deallocate change_type_cursor