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 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