/****** Object: StoredPRocedure [dbo].[usp_DeleteDataBase_Data] Script Date: 02/12/2017 21:45:12 ******/IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_DeleteDataBase_Data]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[usp_DeleteDataBase_Data]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOIF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_DeleteDataBase_Data]') AND type in (N'P', N'PC'))BEGINEXEC dbo.sp_executesql @statement = N'-- =============================================-- Author: -- Create date: -- Description: -- EXEC [usp_DeleteDataBase_Data] ''IPVA_Apple''-- =============================================CREATE PROCEDURE [dbo].[usp_DeleteDataBase_Data] @DataBase nvarchar(50)ASBEGINSET NOCOUNT ON;declare @a int,@b int,@c intdeclare @count_a int,@count_b int,@count_c intdeclare @Name_A nvarchar(50),@Name_AA nvarchar(50),@Name_B nvarchar(50),@Name_C nvarchar(50)declare @SQL nvarchar(max)create table #TableNameA(ID INT IDENTITY(1,1),table_name nvarchar(50),fk_name nvarchar(50),reference_table_name nvarchar(50),fk_list_number int)create table #TableNameB(ID INT IDENTITY(1,1),Name nvarchar(50))create table #TableNameC(ID INT IDENTITY(1,1),Name nvarchar(50))insert into #TableNameASELECT table_name,fk_name,reference_table_name,fk_list_numberFROM (SELECT object_name(f.object_id) AS fk_name,object_name(f.parent_object_id) AS table_name,object_name(f.referenced_object_id) AS reference_table_name,k.parent_column_id AS fk_list_numberFROM sys.foreign_keys fINNER JOIN sys.foreign_key_columns kon f.object_id = k.constraint_object_id and f.referenced_object_id = k.referenced_object_id) t select @count_a=COUNT(*) from #TableNameAset @a=1while(@a<=@count_a)beginselect @Name_AA=table_name from #TableNameA where ID=@aif not exists(select * from #TableNameB where Name =@Name_AA)begininsert into #TableNameB select @Name_AAendset @a=@a+1endset @a=1while(@a<=@count_a)beginselect @Name_A=reference_table_name from #TableNameA where ID=@aif not exists(select * from #TableNameB where Name =@Name_A)begininsert into #TableNameB select @Name_Aendset @a=@a+1endinsert into #TableNameC select name from sysobjects where xtype=''U'' select @count_b=COUNT(*) from #TableNameCset @b=1while(@b<=@count_b)beginselect @Name_B=Name from #TableNameC where ID=@bif not exists(select * from #TableNameB where Name = @Name_B)begininsert into #TableNameB select @Name_Bendset @b=@b+1endselect @count_c=COUNT(*) from #TableNameBset @c=1while(@c<=@count_c)beginselect @Name_C=Name from #TableNameB where ID=@cset @SQL=''delete from [''+@DataBase+''].[dbo].[''+@Name_C+''] ''exec(@SQL)set @c=@c+1enddrop table #TableNameAdrop table #TableNameBdrop table #TableNameCEND' ENDGO
