USE TEMPORARY
GO
DROP FUNCTION dbo.udf_Test_XML_Columns
GO
CREATE FUNCTION dbo.udf_Test_XML_Columns (@ObjectId INT)
RETURNS TABLE
AS
RETURN
SELECT (SELECT c.name AS ColumnName,
column_id AS ColumnId,
t.name AS TYPE,
c.max_length AS MaxLength
FROM sys.columns c (NOLOCK)
INNER JOIN sys.types t (NOLOCK) ON c.user_type_id = t.user_type_id
WHERE c.OBJECT_ID = @ObjectId
FOR
XML RAW('Columns')) AS ColXML
GO
DROP FUNCTION dbo.udf_Test_XML_Columns_Reader
GO
CREATE FUNCTION dbo.udf_Test_XML_Columns_Reader (@XML XML)
RETURNS TABLE
AS
RETURN
SELECT T.item.value('@ColumnName', 'VARCHAR(256)') AS ColumnName,
T.item.value('@ColumnId', 'INT') AS ColumnId,
T.item.value('@TYPE', 'VARCHAR(256)') AS TYPE,
T.item.value('@MaxLength', 'INT') AS MaxLength
FROM @XML.nodes('/Columns') AS T (item)
GO
SELECT s.name AS SchemaName,
t.name AS TableName
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.tables t (NOLOCK) ON t.SCHEMA_ID = s.SCHEMA_ID
CROSS APPLY dbo.udf_Test_XML_Columns(T.OBJECT_ID) AS C
WHERE t.OBJECT_ID = OBJECT_ID('Lender')
GO
SELECT s.name AS SchemaName,
t.name AS TableName,
cl.*
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.tables t (NOLOCK) ON t.SCHEMA_ID = s.SCHEMA_ID
CROSS APPLY dbo.udf_Test_XML_Columns(T.OBJECT_ID) AS C
CROSS APPLY dbo.udf_Test_XML_Columns_Reader(c.ColXML) cl
WHERE t.OBJECT_ID = OBJECT_ID('Lender')