清除所有表数据

原创于 2011-04-02 16:05:00 发布 · 768 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#constraints #table #sql #insert #query #join

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)