Auto-Create Select from Table TSQL Templates


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


Author: Unknown

Date: 10 Jun 2020


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


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 TemplateFROM ( AS Name,OBJECT_SCHEMA_NAME(so.object_ID)+'.' AS qualifiedName,'SELECT '+REPLACE(COALESCE((SELECT '\n '+QUOTENAME('|,|'+COALESCE(' /*'+CONVERT(VARCHAR(MAX),value)+'*/','')FROM sys.columns spLEFT OUTER JOIN sys.extended_properties epON sp.object_id = ep.major_IDAND sp.column_ID = minor_ID AND class=1WHERE sp.object_ID=so.object_IDAND column_ID>0ORDER BY column_IDFOR XML PATH('')),'1'),',||', '')+'\nFROM '+QUOTENAME(OBJECT_SCHEMA_NAME(so.object_ID))+'.'+QUOTENAME(' /*'+CONVERT(VARCHAR(300), value)+'*/', '') [SelectScript]FROMsys.objects soLEFT OUTER JOIN sys.extended_properties ep/* get any extended properties */ON LIKE 'MS_Description'AND major_ID=so.object_IDAND minor_ID=0WHEREOBJECTPROPERTY(object_id, 'IsUserTable')<>0)fORDER BY name