清除所有表数据

lsfa1234

于 2011-04-02 16:05:00 发布

阅读量768

点赞数

CC 4.0 BY-SA版权

分类专栏： sql 文章标签： constraints table sql insert query join

本文链接：https://blog.youkuaiyun.com/lsfa1234/article/details/6298617

sql 专栏收录该内容

8 篇文章

订阅专栏

Declare @t table(query varchar(2000),tables varchar(100))
Insert into @t
    select 'Truncate table ['+T.table_name+']', T.Table_Name from INFORMATION_SCHEMA.TABLES T
    left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
    on T.table_name=TC.table_name
    where (TC.constraint_Type ='Foreign Key' or TC.constraint_Type is NULL) and
    T.table_name not in ('dtproperties','sysconstraints','syssegments') and
    Table_type='BASE TABLE'
Insert into @t
    select 'delete from ['+T.table_name+']', T.Table_Name from INFORMATION_SCHEMA.TABLES T
        left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
       on T.table_name=TC.table_name where TC.constraint_Type ='Primary Key' and T.table_name <>'dtproperties'and Table_type='BASE TABLE'
Declare @sql varchar(8000)
Select @sql=IsNull(@sql+' ','')+ query from @t
print(@sql)
Exec(@sql)