December 2, 2009 at 3:02 am
In general developers always facing a problem when any application developed , tested and ready for Production. You find lots of data store in database table during testing most of the data are useless and now you want to delete all those garbage data before going live or some time you may want to deleted data from selective table only.
Solution 1:
you want to delete / truncate those data using simple SQL.
Solution 1 is ok in the senerio if the total no. of tables within 5 to 15 then no issue, you can run simple delete / truncate SQL query to complete your job. But imagine if you working on a project which have which have lots of database tables i.e. 50, 100, 200 .... more than that. In my senerio I can imagine 1800 tables of an ERP solution. Now the problem is arise here, how you identify the whether the tables from those you want to perform the clean up operation
1. wether those table relate with another table / tables with a foreign key relationship or not!
2. Analysis / Study the database diagram to know the relationship behavior.
3. make a list of those tables.
4. prepare final list those tables from which you want to perform the clean up operation.
5. take the decision on which table you want to run delete / truncate statement e.t.c.
To overcome this problem I found bellow solution is very useful for me. Thats why I want to share my code. Check it out.
[p]
/* ################# Execution Steps ##################################
1. Create bellow SP [stp_CleanGarbageData]
2. Create bellow Fn [GetTableName]
3. Replace Table_1 & Table_2, Table_3 ... Table_n with original table Name, from which you don't want to delete the garbage data.
4. Run SP to clean garbage data.
Ex: exec stp_CleanGarbageData 'Table_1,Table_2,Table_3,..Table_n'
*/
[/p]
Stored Procedure Code
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[stp_CleanGarbageData]
@TableNames varchar(8000)
AS
BEGIN
DECLARE @DynamicString varchar(8000)
EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
BEGIN
PRINT ''--DELETED TABLE NAME--''
PRINT ''----------------------''
PRINT ''?''
END
ELSE
BEGIN
PRINT ''--TRUNCATED TABLE NAME--''
PRINT ''----------------------''
PRINT ''?''
END
'
-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
Set @DynamicString = '
IF (object_id(''?'') not in (select object_id(Id) from dbo.GetTableName(''' + @TableNames + ''')))
BEGIN
IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
BEGIN
DELETE FROM ?
END
ELSE
BEGIN
TRUNCATE TABLE ?
END
END
'
EXEC sp_MSForEachTable @DynamicString
-- enable referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
END
GO
Function
CREATE FUNCTION dbo.GetTableName
(
@Ids varchar(5000)
)
RETURNS @TempTable TABLE (Id varchar(8000))
AS
BEGIN
IF(@Ids is not null)
begin
DECLARE @Id varchar(5000), @Pos int
SET @Ids = LTRIM(RTRIM(@Ids))+ ','
SET @Pos = CHARINDEX(',', @Ids, 1)
IF REPLACE(@Ids, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @Id = LTRIM(RTRIM(LEFT(@Ids, @Pos - 1)))
IF @Id <> ''
BEGIN
INSERT INTO @TempTable (Id) VALUES (CAST(@Id AS varchar(8000))) --Use Appropriate conversion
END
SET @Ids = RIGHT(@Ids, LEN(@Ids) - @Pos)
SET @Pos = CHARINDEX(',', @Ids, 1)
END
END
end
else
INSERT INTO @TempTable (Id) VALUES (null)
RETURN
END
Note: May be this solution is not suitable for every body. May be they found much better solution, or use different tools to do that. I just want to share my code , if some body founds its interesting.
So waiting for positive feed back.
Regards,
Shubha
🙂 Happy Coding
December 2, 2009 at 8:26 pm
I typically don't have a use for such a thing but, if you want feedback, submit it to the webmaster of this site as an article. You'll have to gussy it up a bit, though.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 2, 2009 at 11:39 pm
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply