一日一步 之SQLServer数据库的表操作

????? 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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!