Auto-Create Update each 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 UPDATE each table.

Code

SELECT '/* '+qualifiedName+' */'+CHAR(13)+CHAR(10)+REPLACE(REPLACE(REPLACE(REPLACE(STUFF(UpdateScript, /*delete final comma line-terminator*/LEN(UpdateScript)-1-CHARINDEX('|,|',REVERSE(UpdateScript)+'|'),3,''),'\n',CHAR(13)+CHAR(10)),'|,|',',') /*put in new-lines and convert token to comma*/,'&lt;','<'),'&gt;','>') AS TemplateFROM(SELECTso.name AS Name,OBJECT_SCHEMA_NAME(so.object_ID)+'.'+so.name AS qualifiedName,'UPDATE '+QUOTENAME(OBJECT_SCHEMA_NAME(so.object_ID))+'.'+QUOTENAME(so.name)+COALESCE(' /*' +CONVERT(VARCHAR(300), value)+'*/', '')+'\nSET '+REPLACE(COALESCE((SELECT REPLACE ('\n '+sp.name+' = '+'|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.columns spINNER JOIN sys.types t ON sp.user_Type_ID=t.user_Type_IDLEFT OUTER JOIN sys.extended_properties epON sp.object_id = ep.major_IDAND sp.column_ID = minor_IDAND class=1WHERE sp.object_ID=so.object_IDAND column_ID>0ORDER BY column_IDFOR XML PATH('')),'1'),',||', '')+ CASE WHEN OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 1 THEN'\nWHERE '+SUBSTRING((SELECT '\n AND '+ quotename(COL_NAME(ic.object_id,ic.column_id))+'='+REPLACE('&delim;<'+REPLACE(COALESCE(CONVERT(VARCHAR(MAX),value),t.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.index_columns AS ic inner join sys.indexes ion ic.index_ID=i.index_ID and ic.object_ID=i.object_IDinner join sys.columns sp on sp.column_ID= ic.column_IDand sp.object_ID=ic.object_IDINNER JOIN sys.types t ON sp.user_Type_ID=t.user_Type_IDLEFT OUTER JOIN sys.extended_properties ep/* get any extended properties */ON ep.name LIKE 'MS_Description'AND major_ID=ic.object_IDAND minor_ID=sp.column_ID and type=1 AND class=1WHERE is_primary_key=1 AND so.object_id=ic.object_idORDER BY key_ordinalFOR XML PATH('')),11,8000)ELSE '' END [UpdateScript]FROMsys.objects soLEFT OUTER JOIN sys.extended_properties ep/* get any extended properties */ON ep.name LIKE 'MS_Description'AND major_ID=so.object_IDAND minor_ID=0 --for the table WHEREOBJECTPROPERTY(object_id, 'IsUserTable')<>0)fORDER BY name