USE tempdb
GO
IF OBJECT_ID('dbo.udf_Test_XML_Columns') IS NOT NULL DROP FUNCTION dbo.udf_Test_XML_Columns
IF OBJECT_ID('dbo.udf_Test_XML_Reader_Will_Be_CLR') IS NOT NULL DROP FUNCTION dbo.udf_Test_XML_Reader_Will_Be_CLR
IF OBJECT_ID('dbo.udf_Test_All') IS NOT NULL DROP FUNCTION dbo.udf_Test_All
GO
-- FOR A GIVEN OBJECTID CREATE AN XML FOR ALL OF IT'S 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
-- THIS FUNCTION (WILL BE CLR) WILL DO COMPLEX LOOPING FOR THE GIVEN DATASET AND WILL RETURN ONE ROW WITH MULTIPLE COLUMNS OUT
-- THE CLR FUNCTION WILL NOT MAKE A DATABASE CALL FROM WITHIN
-- ASSUME ABOUT 150 ROWS AND 12 COLUMNS ARE PASSED TO THIS FUNCTION, MAINLY NUMERIC, SOME DATES AND FEW VARCHARS
GO
CREATE FUNCTION dbo.udf_Test_XML_Reader_Will_Be_CLR (@XML XML) -- IS THERE ANY WAY TO USE SOMETHING MORE EFICIENT THAN XML FOR THIS ?
RETURNS TABLE
AS
RETURN
-- THIS FUNCTION WILL BE IN CLR, THERE MAY NEED TO BE MULTIPLE FOR EACH LOOPS RAN AGAINST THE RECORDSET PASSED INTO IT AND
-- THE OUTPUT WILL BE RETURNED IN SINGLE ROW MULTIPLE COLUMNS.
SELECT MAX(T.item.value('@ColumnName', 'VARCHAR(256)')) AS ColumnName,
MAX(T.item.value('@ColumnId', 'INT')) AS ColumnId,
MAX(T.item.value('@TYPE', 'VARCHAR(256)')) AS TYPE,
MAX(T.item.value('@MaxLength', 'INT')) AS MaxLength
FROM @XML.nodes('/Columns') AS T (item)
GO
--THIS FUNCTION WILL COMBINE THE FUNCTIONS BEFORE AS NOTICED IT'S STILL AN INLINE FUNCTION, THE QUERY OPTIMIZER TREATS THAT WAY
GO
CREATE FUNCTION dbo.udf_Test_All (@ObjectId INT)
RETURNS TABLE
AS
RETURN
SELECT *
FROM dbo.udf_Test_XML_Reader_Will_Be_CLR((SELECT ColXML FROM dbo.udf_Test_XML_Columns (@ObjectId)))
GO
SELECT TOP 10 s.name AS SchemaName,
t.name AS TableName,
cl.*
-- BELOW UPDATE STATEMENT IS HYPOTETICAL, THE IDEA IS GET THE SINGE ROW RECORDSET AND MAKE THE UPDATE AGAINST COLUMNS ON TABLE t
-- UPDATE t SET ColumnName = cl.ColumnName, ColumnId = cl.ColumnId, Type = cl.Type, MaxLength = cl.MaxLength
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.tables t (NOLOCK) ON t.SCHEMA_ID = s.SCHEMA_ID
CROSS APPLY dbo.udf_Test_All(T.OBJECT_ID) cl
--WHERE t.object_id = OBJECT_ID('Lender')