The Exceptional EXCEPT clause
- by steveh99999
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 ? SELECT DB_NAME(),OBJECT_NAME(s1.id) FROM syscomments s1 WHERE [text] LIKE ''%sp_xml_preparedocument%'' EXCEPT SELECT DB_NAME(),OBJECT_NAME(s2.id) 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…
