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*/
,'<','<')
,'>','>') AS Template
FROM
(SELECT
so.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 sp
INNER JOIN sys.types t ON sp.user_Type_ID=t.user_Type_ID
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')
,',||', '')
+ 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 i
on ic.index_ID=i.index_ID and ic.object_ID=i.object_ID
inner join sys.columns sp on sp.column_ID= ic.column_ID
and sp.object_ID=ic.object_ID
INNER JOIN sys.types t ON sp.user_Type_ID=t.user_Type_ID
LEFT OUTER JOIN sys.extended_properties ep
/* get any extended properties */
ON ep.name LIKE 'MS_Description'
AND major_ID=ic.object_ID
AND minor_ID=sp.column_ID and type=1 AND class=1
WHERE is_primary_key=1 AND so.object_id=ic.object_id
ORDER BY key_ordinal
FOR XML PATH('')
),11,8000)
ELSE '' END [UpdateScript]
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 --for the table
WHERE
OBJECTPROPERTY(object_id, 'IsUserTable')<>0)f
ORDER BY name