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!
[…] Emanuele Meazzo shows how you can auto-escape XML characters using T-SQL: […]
How might you apply your solution to the following:
SELECT
[ColumnName] = [X2].[N].value(‘local-name(.)’, ‘NVARCHAR(128)’),
[ColumnValue] = [X2].[N].value(‘text()[1]’, ‘NVARCHAR(MAX)’)
FROM ( SELECT TOP 1 * FROM [Centers] FOR XML AUTO, ELEMENTS, TYPE ) AS X1(X)
CROSS APPLY X1.X.nodes(‘//*[text()]’) AS X2(N);
Nevermind… simply changing select top 1 * with select top 1 col1 = string_escape(col1, ‘json’)….
DECLARE
@Sql varchar(max) = ”,
@ColumnList varchar(max) = ”,
@TableSchema varchar(100) = ‘dbo’,
@TableName varchar(max) = ‘TaxInfos’,
@ExecSql nvarchar(max) = ”;
SELECT @ColumnList = @ColumnList + CONCAT(‘, ‘, [COLUMN_NAME], ‘ = ‘, ‘STRING_ESCAPE(CAST(‘, [COLUMN_NAME], ‘ AS VARCHAR(MAX)),”json”)’)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE [TABLE_NAME] = ‘TaxInfos’
SELECT @Sql = CONCAT(‘SELECT TOP 1’, SUBSTRING(@ColumnList, 2, LEN(@ColumnList)), ‘ FROM ‘, @TableSchema, ‘.’, @TableName)
SELECT @ExecSql = CONCAT(‘
SELECT
[ColumnName] = [X2].[N].value(”local-name(.)”, ”NVARCHAR(128)”),
[ColumnValue] = [X2].[N].value(”text()[1]”, ”NVARCHAR(MAX)”)
FROM (‘,
@Sql,
‘ FOR XML AUTO, ELEMENTS, TYPE ) AS X1(X) CROSS APPLY X1.X.nodes(”//*[text()]”) AS X2(N)’)
EXECUTE sp_executesql @ExecSql;