大家好，我是考100分的小小码 ，祝大家学习进步，加薪顺利呀。今天说一说记录sql server 的批量删除主外键的sql语句[通俗易懂],希望您对编程的造诣更进一步.
![记录sql server 的批量删除主外键的sql语句[数据库教程]](/www.yht7.com/upload/image/images/imgsql/9.jpg "记录sql server 的批量删除主外键的sql语句[通俗易懂]插图")
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 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].[‘[email protected]+‘] DROP CONSTRAINT [‘[email protected]+‘]‘) fetch next from fk_cursor into @tableName,@typeName end close fk_cursor deallocate fk_cursor
代码100分
代码100分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 [‘[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 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].[‘[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
记录sql server 的批量删除主外键的sql语句
原文地址：https://www.cnblogs.com/dazen/p/13307105.html
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/7366.html
