The Exceptional EXCEPT clause

Ok, I exaggerate, but it can be useful…

I came across some ‘poorly-written’ stored procedures on a SQL server recently, that were using sp_xml_preparedocument.

Unfortunately these procs were  not properly removing the memory allocated to XML structures – ie they were not subsequently calling sp_xml_removedocument…

I needed a quick way of identifying on the server how many stored procedures this affected..

Here’s what I used..

EXEC sp_msforeachdb 'USE ?
FROM syscomments s1
WHERE [text] LIKE ''%sp_xml_preparedocument%''
FROM syscomments s2
WHERE [text] LIKE ''%sp_xml_removedocument%'' ‘

There’s three nice features about the code above…

1. It uses sp_msforeachdb. There’s a nice blog on this statement here

2. It uses the EXCEPT clause.  So in the above query I get all the procedures which include the sp_xml_preparedocument string, but by using the EXCEPT clause I remove all the procedures which contain sp_xml_removedocument.  Read more about EXCEPT here

3. It can be used to quickly identify incorrect usage of sp_xml_preparedocument. Read more about this here

The above query isn’t perfect – I’m not properly parsing the SQL text to ignore comments for example - but for the quick analysis I needed to perform, it was just the job…

Published Wednesday, February 2, 2011 11:03 PM by steveh99999
Filed under: ,


# Twitter Trackbacks for The Exceptional EXCEPT clause - Steve Hindmarsh's SQL Blog [] on

Pingback from  Twitter Trackbacks for                 The Exceptional EXCEPT clause - Steve Hindmarsh's SQL Blog         []        on