select * into NewTables from LawInfo where 1=2 --复制表结构
delete newTables --删除表数据
drop table newTables --删除表以及表结构
Alter table MyTableName Drop constraint 约束名称 --删除表关系
Alter table MyTableName Drop column 字段名称 --删除表字段
Alter table MyTableName NoCheck constraint all --禁用所有约束
---------接下来是某小牛的存储过程,以实现查询出表的所有外键关系,并删除-----------
代码如下:
Create proc DeleteSingleTable(@tablename varchar(100))
as
begin
declare @SQL varchar(2000)
declare @constraintName varchar(100)
declare curName cursor for
select name from sysobjects
where xtype = 'f ' and parent_obj =
(select [id] from sysobjects where [name]=@tablename and xtype = 'u ')
open curName
fetch next from curName into @constraintName
while @@fetch_status = 0
begin
set @SQL = 'alter table ' + @tablename + ' drop constraint '
set @SQL = @SQL + @constraintName
exec(@SQL)
fetch next from curName into @constraintName
end
close curName
deallocate curName
end