Saturday, June 19, 2010

Brute force method of deleting all objects in database based on naming convention

I did this to clean out from my database all the tables which were created by DotNetNuke.

Run the query below to get the delete object script. Then run it repeatedly till nothing is left. Because there might be dependencies between objects, some objects might be left behind the first time you run this.

select dropCmd= 'if object_id(''' + name + ''') is not null  drop ' +
case when xtype= 'P' then 'procedure'
 when xtype = 'U' then 'table'
 when xtype = 'FN' or xtype = 'TF' then 'function'
 when xtype = 'V' then 'view'
 else ' ******unhandled object type ****** 'end + ' dbo.' +  name
 from sysobjects where name like 'dnn%_'

No comments: