Monday, May 13, 2013

Sql Server: Delete all the objects from the database which are created by user, and not in template database.

Delete all the objects from the database which are created by user, and not in template database.


This kind of scenario exists generally in dev environment. When we have one template database and our db in dev environment should contain only objects which are present in template database. But every day, some db developer works on original database and creates some objects, which he forgets to delete. In such case every day, this operation will be performed and objects created by the developer for their experiment will be deleted.

In the below script AdventureWorks2008 is my template database and sampleadventure is database in dev environment. So sampleadventure should only contain objects present in AdventureWorks2008 database.

I have used cursor for this. Collate is optional, try without collate. I have used collate because the collation was different for both database.


DECLARE @name nVARCHAR(255) ,@object_id int ,@type nVARCHAR(10), @prefix nVARCHAR(255) , @sql nVARCHAR(255)

DECLARE curs CURSOR FOR
   SELECT o.object_id as objectid,o.name as name,o.type as type
    FROM sampleadventure.sys.objects  o
    WHERE o.NAME   NOT IN (SELECT name collate Latin1_General_CI_AS_KS_WS                        FROM  AdventureWorks2008.sys.objects)
    and
    o.type IN ('U', 'P', 'FN', 'IF', 'TF', 'V', 'TR')          
    ORDER BY name

OPEN curs
FETCH NEXT FROM curs INTO @object_id, @name, @type

WHILE @@FETCH_STATUS = 0
BEGIN
  
    SET @prefix = CASE @type 
        WHEN 'U' THEN 'DROP TABLE'
        WHEN 'P' THEN 'DROP PROCEDURE'
        WHEN 'FN' THEN 'DROP FUNCTION'
        WHEN 'IF' THEN 'DROP FUNCTION'
        WHEN 'TF' THEN 'DROP FUNCTION'
        WHEN 'V' THEN 'DROP VIEW'
        WHEN 'TR' THEN 'DROP TRIGGER'
    END

    SET @sql = @prefix + ' ' + @name
    PRINT @sql
    EXEC(@sql)
    FETCH NEXT FROM curs INTO @name, @type
END

CLOSE curs
DEALLOCATE curs

Please provide your feedback for the post, if you find this post useful.

No comments:

Post a Comment