Reset SQL Schema during your coding sessions

I think that anyone that writes SQL code has coded like the following:

 

if OBJECT_ID('???') IS NOT NULL DROP PROCEDURE ???

GO

 

CREATE PROCEDURE ???

AS

    ...

GO

 

This is quite common for me during the testing phase, since I find myself comfortable in writing a single script that drops & recreates all objects under development… but it is also quite frustrating to copy & paste always the same code only to change the object name.

So I’ve decided to look a way to avoid repeating the same code again and again… the solution I’ve find is to query INFORMATION_SCHEMA to list object I need and then to build dynamic queries to drop those objects.

 

Here follows a short script that drops a certain number of  tables all prefixed by 'wf_%' string, the script drops the constraints first and then the tables:

 

declare @spname sysname

declare spCur cursor

    read_only for

    select 'ALTER TABLE ' + foreign_table + ' DROP CONSTRAINT [' + fk_name + ']'

      from foreign_keys

      where ((primary_table like 'wf_%') or (foreign_table like 'wf_%'))

    UNION

    select  case when table_type = 'view' then 'DROP VIEW ' + table_schema + '.[' + table_name + ']'

                 else 'DROP TABLE ' + table_schema + '.[' + table_name + ']'

            end

        from INFORMATION_SCHEMA.TABLES

        where (table_name like 'wf_%') and table_catalog = db_name()

open spcur

FETCH NEXT FROM spcur INTO @spname

WHILE (@@fetch_status = 0) BEGIN

    exec (@spname)

       FETCH NEXT FROM spcur INTO @spname

END

CLOSE spcur

DEALLOCATE spcur

GO

 

You can also drop any other type of object by referring the right schema tables, for example this is how to drop stored procedures and functions

 

    select 'DROP ' + ROUTINE_TYPE + ' ' + routine_schema + '.[' + routine_name + ']'

        from information_schema.routines

        where (routine_name like 'wf_%') and routine_catalog = db_name()

 

Just to be complete… here the script that creates the foreign_keys view

 

CREATE  view dbo.foreign_keys as

   select cast(f.name  as varchar(255)) as fk_name

    , r.keycnt

    , cast(ft.name as  varchar(255)) as foreign_table

    , cast(f1.name as varchar(255))  as foreign_col1

    , cast(f2.name as varchar(255))  as foreign_col2

    , cast(pt.name as varchar(255))  as primary_table

    , cast(p1.name as varchar(255))  as primary_col1

    , cast(p2.name as varchar(255))  as primary_col2

    from sysobjects f

    inner join sysobjects ft on  f.parent_obj = ft.id

    inner join sysreferences r on f.id =  r.constid

    inner join sysobjects pt on r.rkeyid = pt.id

    inner  join syscolumns p1 on r.rkeyid = p1.id and r.rkey1 = p1.colid

    inner  join syscolumns f1 on r.fkeyid = f1.id and r.fkey1 = f1.colid

    left join  syscolumns p2 on r.rkeyid = p2.id and r.rkey2 = p1.colid

    left join  syscolumns f2 on r.fkeyid = f2.id and r.fkey2 = f1.colid

    where f.type =  'F'

GO

 

Hope that helps