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…