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