Cool Tips‎ > ‎Scripting SQL Server‎ > ‎

Auto-Create Select from TVF TSQL Templates

Applicability:

                 SQL Server 2000:        N/A
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Tested
                 SQL Server 2014:        Tested
                 SQL Server 2016:        Tested
                 SQL Server 2017:        Tested

Credits:

Author: Unknown
Date:    10 Jun 2020

Description

For all Tables in a database, generate TSQL templates to SELECT from each table valued function

Code



SELECT '/* '+qualifiedName+' */' +CHAR(13)+CHAR(10)+REPLACE( REPLACE( REPLACE( REPLACE( STUFF(TVFScript, /*delete final comma line-terminator*/ LEN(TVFScript)-CHARINDEX('|,|', REVERSE(TVFScript)+'|')-1,3 ,'') +'\n ('+COALESCE(STUFF(TVFScriptParams, /*delete final comma line-terminator*/ LEN(TVFScriptParams)-CHARINDEX('|,|', REVERSE(TVFScriptParams)+'|')-1,3 ,''),'')+'\n )' ,'\n',CHAR(13)+CHAR(10)) ,'|,|',',') /*put in new-lines and convert token to comma*/ ,'&lt;','<') ,'&gt;','>') AS Template FROM (SELECT so.name AS Name, OBJECT_SCHEMA_NAME(so.object_ID)+'.'+so.name AS qualifiedName, 'SELECT ' +REPLACE( COALESCE( ( SELECT '\n '+QUOTENAME(sp.name)+'|,|'+COALESCE(' /*' +CONVERT(VARCHAR(MAX),value)+'*/','') FROM sys.columns sp LEFT OUTER JOIN sys.extended_properties ep ON sp.object_id = ep.major_ID AND sp.column_ID = minor_ID AND class=1 WHERE sp.object_ID=so.object_ID AND column_ID>0 ORDER BY column_ID FOR XML PATH('') ),'|,|') ,',||', '') +'\nFROM '+QUOTENAME(OBJECT_SCHEMA_NAME(so.object_ID)) +'.'+QUOTENAME(so.name) +COALESCE(' /*' +CONVERT(VARCHAR(300), value)+'*/', '') AS [TVFScript], REPLACE(COALESCE( ( SELECT REPLACE ('\n |delim;<' +REPLACE( COALESCE( CONVERT(VARCHAR(MAX),value) ,REPLACE(sp.name,'@','') ) ,',','') +','+t.name+','+'>|delim;'+'|,|' +COALESCE(' /*'+CONVERT(VARCHAR(MAX),value)+'*/',''),'|delim;', CASE WHEN t.name IN ('char', 'varchar','nchar' ,'nvarchar','text','ntext' ,'datetime','date') THEN '''' WHEN t.precision=0 THEN '''' ELSE '' END) FROM sys.parameters sp INNER JOIN sys.types t ON sp.user_Type_ID=t.user_Type_ID LEFT OUTER JOIN sys.extended_properties ep ON ep.name LIKE 'MS_Description' AND sp.object_id = ep.major_ID AND sp.parameter_ID = minor_ID AND class=2 WHERE sp.object_ID=so.object_ID AND parameter_ID>0 ORDER BY parameter_ID FOR XML PATH('') ),'1') ,',||', '') [TVFScriptParams] FROM sys.objects so LEFT OUTER JOIN sys.extended_properties ep /* get any extended properties */ ON ep.name LIKE 'MS_Description' AND major_ID=so.object_ID AND minor_ID=0 WHERE OBJECTPROPERTY(object_id, 'IsTableFunction')<>0)f





ċ
Andy Hughes,
Jun 10, 2020, 5:23 PM
Comments