2005 automatically generate empty scripts for the database __ database

--Automatically generate the database's empty script (SQL05 environment)--use test--Specify the data library--go if object_id (' f_clear ', ' TF ') is not null drop function f_clear go/****** *************************************************************************************************************** %% Function Name: f_clear% input parameter:%% output parameter:%% function: *********************************** *************************************************************************************************************** % write: Roy (China Wind) *********************************************************************************** /create Function F_clear () returns @T Table (ID INT identity,clear_sql nvarchar (), tablename sysname) as begin; with Cterk (Lev,fkeyid,rkeyid) as (select 1,
A.fkeyid,a.rkeyid from Sys.sysforeignkeys A where does exists (select 1 from Sys.sysforeignkeys where Rkeyid=a.fkeyid) UNION ALL select B.lev+1,a.fkeyid,a.rkeyid from Sys.sysforeIgnkeys A join Cterk B on B.rkeyid=a.fkeyid), CTERK2 as (select Lev,objectid,row=row_number () over (partition by objecti D ORDER by Lev DESC) from (select Max (Lev) as Lev,fkeyid as ObjectID to Cterk GROUP by Fkeyid UNION ALL select Max (Lev +1,rkeyid as ObjectID from Cterk GROUP by Rkeyid) as a) Insert @T SELECT [clear_sql]=case if Lev=1 or B.objectid is Null then ' Truncate table ' +quotename (a.name) when c.object_id was not null then ' Delete ' +quotename (a.name) +char R (d) + ' if @ @rowcount >0 or ident_current (' +a.name+ ') >1 ' +char (+) + ' DBCC +CHAR (' Checkident "(a).  Name, ' "] + ', reseed,0) ' Else ' Delete ' +quotename (a.name) end,[tablename]=a.name from sysobjects a left join CTERK2 B on A.id=b.objectid and B.row=1 left join Sys.identity_columns C in a.id=c.object_id where xtype= ' U ' ORDER by case when B.O Bjectid is null then 0 else 1 End,b.lev ASC return end Go SELECT a.[clear_sql] from F_clear () as a INNER J OIN Sys.tables as B on b.name = A.tablename ORDER BY a.ID go drop function f_clear