Thursday, 16 October 2008

How to cleanup SQL Server Service Broker

If you need to cleanup or reset Service Broker, you may find useful this short script that shuts down all open conversations and cleans up the Service Broker queue.


declare @u uniqueidentifier

select top 1 @u=conversation_handle from sys.conversation_endpoints

while @u is not null begin


  set @u = null

  select top 1 @u=conversation_handle from sys.conversation_endpoints



Hope that helps.

Monday, 13 October 2008

Reset SQL Schema during your coding sessions

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










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_%'))


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

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



        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


CLOSE spcur




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(  as varchar(255)) as fk_name

    , r.keycnt

    , cast( as  varchar(255)) as foreign_table

    , cast( as varchar(255))  as foreign_col1

    , cast( as varchar(255))  as foreign_col2

    , cast( as varchar(255))  as primary_table

    , cast( as varchar(255))  as primary_col1

    , cast( as varchar(255))  as primary_col2

    from sysobjects f

    inner join sysobjects ft on  f.parent_obj =

    inner join sysreferences r on =  r.constid

    inner join sysobjects pt on r.rkeyid =

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

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

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

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

    where f.type =  'F'



Hope that helps




Saturday, 4 October 2008

CLR Via C# Second Edition (by Jeffrey Richter)

This is absolutely the best book I've read on programming CLR. It is focused on CLR features and capabilities and C# is only a way to access them, so you will find a lot of real-world tips&tricks that will route you on the right way, so avoiding a lot of common errors. The best chapter, IMO, is the one that explain how multithreading works, what to do and what not to do, and how to avoid CLR bugs... what yoy want more ? buy this book, have a quick read, and keep it on your desk.
If you still are not convinced, go to and read reviews...