Cool Tips‎ > ‎Scripting SQL Server‎ > ‎

Auto-Create Select from Table 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

Code



SELECT qualifiedName as Name, '/* '+qualifiedName+' */'+CHAR(13)+CHAR(10)+REPLACE( REPLACE( STUFF(SelectScript, /*delete final comma line-terminator*/ LEN(SelectScript)-CHARINDEX('|,|', REVERSE(SelectScript)+'|')-1,3 ,'') ,'\n',CHAR(13)+CHAR(10)) ,'|,|',',') /*put in new-lines and convert token to comma*/ 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('') ),'1') ,',||', '') +'\nFROM '+QUOTENAME(OBJECT_SCHEMA_NAME(so.object_ID))+'.' +QUOTENAME(so.name)+COALESCE(' /*' +CONVERT(VARCHAR(300), value)+'*/', '') [SelectScript] 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, 'IsUserTable')<>0)f ORDER BY name





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