Cast any string directly to XML with auto-escaping characters in SQL Server

Recently I had to look up the definition for a bunch of SQL objects and didn’t want to manually retrieve them manually in SSMS (with Create Scripts) or Visual Studio (by searching the object name in my TFS repository).

Since lazyness and automation are the basis of a well done engineering work, I wanted to create a list, where I could basically click on the object that I needed and see the definition right away, without any tool or having to code something externally, of course.

The issue

DMVs and XML came to the rescue here, since everybody remembers that a XML field in SSMS (or AZDS) is clickable and opens up the XML Code in a new window; BUT, you cannot cast everything right away in XML, as some characters needs to be escaped, and most of those characters are often used in object definitions (<,>,etc…)

How to deal with it

By first creating a XML document with the FOR XML statement, and then casting it as XML in the result set, we obtain what we were looking for:

SELECT s.name AS Schema_Name
    ,  o.name AS Object_Name
    ,  CAST((SELECT m.definition FOR XML PATH('')) as XML) Object_Definition
    ,  o.type_desc
FROM sys.sql_modules m
INNER JOIN sys.objects o
    ON m.object_id = o.object_id
INNER JOIN sys.schemas s
    ON o.schema_id = s.schema_id

As you can see in the ouput XML, special XML characters are escaped automatically, no need to REPLACE(REPLACE(REPLACE(REPLACE
Enjoy!