====== Fun with column metadata ======

{{tag> tsql dynamic}}

===== Metadata =====
<code tsql>
DECLARE @tableName NVARCHAR(260) = 'myTable';

DROP TABLE IF EXISTS #columns;

SELECT
  OBJECT_SCHEMA_NAME(o.[object_id]) AS TableSchema
, o.[name] AS TableName
, c.[name] AS ColumnName
, c.column_id AS ColumnOrdinal
, t.[name] AS TypeName
, c.max_length AS [MaxLength]
, c.scale
, c.[precision]
, c.is_nullable
, IIF(ic.index_column_id IS NULL, 0, 1) AS IsPkColumn
, ic.index_column_id AS PkOrdinal
, UPPER(t.[name]) 
    + COALESCE('(' + 
	    CAST(
	      CASE 
		      WHEN t.[name] LIKE '%char' AND c.max_length = -1 THEN 'MAX'
		      WHEN t.[name] LIKE 'n%char' THEN CAST(c.max_length/2 AS VARCHAR)
		      WHEN t.[name] LIKE '%char' THEN CAST(c.max_length AS VARCHAR)
		      WHEN t.[name] LIKE '%binary' THEN CAST(c.max_length AS VARCHAR)
	      END AS VARCHAR) + ')', '') AS SqlType
, CASE
    WHEN dc.[definition] LIKE '((%))' THEN SUBSTRING(dc.[definition], 3, LEN(dc.[definition]) - 4)
    WHEN dc.[definition] LIKE '(%)' THEN SUBSTRING(dc.[definition], 2, LEN(dc.[definition]) - 2)
    ELSE dc.[definition] 
  END AS DefaultValue
INTO #columns
FROM sys.objects o
  INNER JOIN sys.columns c ON c.[object_id] = o.[object_id]
  INNER JOIN sys.types t 
    ON t.system_type_id = c.system_type_id
    AND t.system_type_id = t.user_type_id
  LEFT JOIN sys.indexes i 
    ON i.[object_id] = o.[object_id]
    AND i.is_primary_key = 1
  LEFT JOIN sys.index_columns ic
    ON ic.[object_id] = i.[object_id]
    AND ic.index_id = i.index_id
    AND ic.column_id = c.column_id
  LEFT JOIN sys.default_constraints dc 
    ON dc.parent_object_id = o.[object_id]
    AND dc.parent_column_id = c.column_id
WHERE o.[object_id] = OBJECT_ID(@tableName)
ORDER BY ColumnOrdinal;

SELECT * FROM #columns;
</code>

===== Temporary tables =====
<code tsql>
with cols AS (
SELECT 
  [name] AS ColumnName
FROM tempdb.sys.columns 
WHERE [object_id] = OBJECT_ID('tempdb..#tmp')
)
select *
into #columns
from cols

</code>

This works in Azure SQL DB too!
===== Table diff =====

<code tsql>UPDATE #columns
SET PkOrdinal = COALESCE(PkOrdinal, 5000)

DECLARE @joinCols NVARCHAR(MAX) = ''
DECLARE @selectCols NVARCHAR(MAX) = ''
DECLARE @orderCols NVARCHAR(MAX) = ''
DECLARE @sql NVARCHAR(MAX) = ''

SELECT 
  @joinCols += '
    AND (old.' + QUOTENAME(ColumnName) + ' = new.' + QUOTENAME(ColumnName) + ' OR old.' + QUOTENAME(ColumnName) + ' IS NULL AND new.' + QUOTENAME(ColumnName) + ' IS NULL)'
, @selectCols += '
, COALESCE(old.' + QUOTENAME(ColumnName) + ', new.' + QUOTENAME(ColumnName) + ') AS ' + QUOTENAME(ColumnName)
, @orderCols += '
, COALESCE(old.' + QUOTENAME(ColumnName) + ', new.' + QUOTENAME(ColumnName) + ')'
FROM #columns
ORDER BY PkOrdinal

SELECT TOP 1
  @sql = 'WITH old AS (
  SELECT 
    ''OLD'' AS Version
  , *
  FROM oldTable
), new AS (
  SELECT 
    ''NEW'' AS Version
  , *
  FROM ' + QUOTENAME(TableSchema) + '.' + QUOTENAME(TableName) + '
)
SELECT
  COALESCE(old.[Version], new.[Version]) AS [Version]' + @selectCols + '
FROM old
  FULL OUTER JOIN new
    ON 1 = 1' + @joinCols + '
WHERE old.[Version] IS NULL
OR new.[Version] IS NULL
ORDER BY
  ' + SUBSTRING(@orderCols, 5, LEN(@orderCols))
FROM #columns

PRINT @sql
</code>

<WRAP center round tip 80%>
If the resulting SQL statement is large, you may need to [[tsql:print_big]].
</WRAP>