Sometimes you want to be able to dynamically construct table definitions to perform different actions, such as creating a copy of a table on a different database, compare table schema, sync table data where the tables are not guarenteed to have the same schema, generate update, merge, select and insert statements and not have to keep adjusting the code as the table schema drifts.

Below we will go through a few of these scenarios, but first lets define the “black magic” that the rest of the examples will build upon.

Black Magic#

These are the exact same query but produce a #Source and #Destination temp tables that the examples will use.

Select Source Schema#

1
DECLARE
2
  @TableName SYSNAME = 'MyTable',
3
  @TableSchema SYSNAME = 'dbo'
4

5
IF OBJECT_ID('tempdb..#Source') IS NOT NULL
6
  DROP TABLE #Source
7

8
SELECT
9
  [ColumnId] = sc.[column_id],
10
  [ColumnName] = sc.[name] COLLATE DATABASE_DEFAULT,
11
  -- adjust timestamp type to varbinary
12
  [Type] = CASE WHEN st.[user_type_id] = 189 THEN 'varbinary' ELSE st.[name] END COLLATE DATABASE_DEFAULT,
13
  -- adjust nvarchar length to half what is reported since it takes 2 bytes to store unicode, and adjust timestamp to the translated varbinary length
14
  [Length] = CASE WHEN sc.[max_length] = -1 THEN -1 WHEN st.[user_type_id] = 231 THEN sc.[max_length] / 2 WHEN st.[user_type_id] = 189 THEN 85 ELSE sc.[max_length] END,
15
  [Precision] = sc.[precision],
16
  [Scale] = sc.[scale],
17
  [IsNullable] = sc.[is_nullable]
18
INTO #Source
19
FROM [sys].[objects] so
20
JOIN [sys].[columns] sc
21
ON so.[object_id] = sc.[object_id]
22
JOIN [sys].[types] st
23
ON sc.[user_type_id] = st.[user_type_id]
24
JOIN [sys].[schemas] ss
25
ON so.[schema_id] = ss.[schema_id]
26
WHERE so.[name] = @TableName AND ss.[name] = @TableSchema

Select Destination Schema#

1
DECLARE
2
  @TableName SYSNAME = 'MyTable',
3
  @TableSchema SYSNAME = 'dbo'
4

5
IF OBJECT_ID('tempdb..#Destination') IS NOT NULL
6
  DROP TABLE #Destination
7

8
SELECT
9
  [ColumnId] = sc.[column_id],
10
  [ColumnName] = sc.[name] COLLATE DATABASE_DEFAULT,
11
  -- adjust timestamp type to varbinary
12
  [Type] = CASE WHEN st.[user_type_id] = 189 THEN 'varbinary' ELSE st.[name] END COLLATE DATABASE_DEFAULT,
13
  -- adjust nvarchar length to half what is reported since it takes 2 bytes to store unicode, and adjust timestamp to the translated varbinary length
14
  [Length] = CASE WHEN sc.[max_length] = -1 THEN -1 WHEN st.[user_type_id] = 231 THEN sc.[max_length] / 2 WHEN st.[user_type_id] = 189 THEN 85 ELSE sc.[max_length] END,
15
  [Precision] = sc.[precision],
16
  [Scale] = sc.[scale],
17
  [IsNullable] = sc.[is_nullable]
18
INTO #Destination
19
FROM [sys].[objects] so
20
JOIN [sys].[columns] sc
21
ON so.[object_id] = sc.[object_id]
22
JOIN [sys].[types] st
23
ON sc.[user_type_id] = st.[user_type_id]
24
JOIN [sys].[schemas] ss
25
ON so.[schema_id] = ss.[schema_id]
26
WHERE so.[name] = @TableName AND ss.[name] = @TableSchema

Example — Create Table Copy#

Create a copy of the table using the source schema information possibly injecting extra columns in the copy.

1
DECLARE
2
  @TableName SYSNAME = 'MyTable',
3
  @TableSchema SYSNAME = 'dbo',
4
  @IsDebug BIT = 0
5

6
DECLARE
7
  @DataType NVARCHAR(200),
8
  @Length INT,
9
  @Precision INT,
10
  @Scale INT,
11
  @IsNullable INT,
12
  @ColumnName NVARCHAR(200),
13
  @Statement NVARCHAR(MAX) = ''
14

15
DECLARE x CURSOR FAST_FORWARD FOR
16
  SELECT [ColumnName], [Type], [Length], [Precision], [Scale], [IsNullable]
17
  FROM #Source
18
  ORDER BY [ColumnId]
19

20
OPEN x
21
FETCH NEXT FROM x INTO @ColumnName, @DataType, @Length, @Precision, @Scale, @IsNullable
22
WHILE @@FETCH_STATUS = 0
23
BEGIN
24
  DECLARE @NullClause NVARCHAR(10) = ' NULL'
25
  IF @IsNullable = 0
26
  BEGIN
27
    SELECT @NullClause = ' NOT NULL'
28
  END
29

30
  IF @DataType = 'timestamp'
31
  BEGIN
32
    SELECT @DataType = 'varbinary'
33
    SELECT @Length = 85
34
  END
35

36
  IF @DataType IN('char','varchar','binary','varbinary','nvarchar','nchar')
37
  BEGIN
38
    SELECT @Statement = @Statement + '[' + @ColumnName + '] ' + @DataType + '(' + ISNULL(NULLIF(CONVERT(NVARCHAR,@Length), '-1'), 'MAX') + ')' + @NullClause + ','
39
  END
40
  ELSE IF @DataType IN('decimal','numeric')
41
  BEGIN
42
    SELECT @Statement = @Statement + '[' + @ColumnName + '] ' + @DataType + '(' + CONVERT(NVARCHAR,@Precision) + ',' + CONVERT(NVARCHAR,@Scale) + ')'  + @NullClause + ','
43
  END
44
  ELSE
45
  BEGIN
46
    SELECT @Statement = @Statement + '[' + @ColumnName + '] ' + @DataType + @NullClause + ','
47
  END
48
FETCH NEXT FROM x INTO @ColumnName, @DataType, @Length, @Precision, @Scale, @IsNullable
49
END
50
CLOSE x
51
DEALLOCATE x
52

53
SELECT @Statement = SUBSTRING(@Statement, 1, LEN(@Statement) - 1)
54

55
DECLARE @Sql NVARCHAR(MAX) = ''
56

57
SELECT @Sql = '
58
CREATE TABLE [{0}].[{1}](
59
{2}
60
)'
61

62
SELECT @Sql = REPLACE(@Sql, '{0}', @TableSchema)
63
SELECT @Sql = REPLACE(@Sql, '{1}', @TableName)
64
SELECT @Sql = REPLACE(@Sql, '{2}', @Statement)
65

66
IF @IsDebug = 1
67
  PRINT (@Sql)
68
IF @IsDebug = 0
69
  EXEC (@Sql)

Example — Schema Diff#

Below are a few ways you can use this information to find;

columns that have changed
columns that are missing from the destination
columns that have been removed from the source and not the destination
columns that both tables have in common

1
IF OBJECT_ID('tempdb..#CHANGED_COLUMNS') IS NOT NULL
2
  DROP TABLE #CHANGED_COLUMNS
3

4
SELECT
5
    [ColumnName],
6
    [Type],
7
    [Length],
8
    [Precision],
9
    [Scale]
10
INTO #CHANGED_COLUMNS
11
FROM #Source
12
EXCEPT
13
SELECT
14
    [ColumnName],
15
    [Type],
16
    [Length],
17
    [Precision],
18
    [Scale]
19
FROM #Destination
20

21

22
IF OBJECT_ID('tempdb..#MISSING_COLUMNS') IS NOT NULL
23
  DROP TABLE #MISSING_COLUMNS
24

25
SELECT
26
    [ColumnName]
27
INTO #MISSING_COLUMNS
28
FROM #Source
29
EXCEPT
30
SELECT
31
    [ColumnName]
32
FROM #Destination
33

34
IF OBJECT_ID('tempdb..#DELETED_COLUMNS') IS NOT NULL
35
  DROP TABLE #DELETED_COLUMNS
36

37
SELECT
38
    [ColumnName]
39
INTO #DELETED_COLUMNS
40
FROM #Destination
41
EXCEPT
42
SELECT
43
    [ColumnName]
44
FROM #SOURCE
45

46
IF OBJECT_ID('tempdb..#SHARED_COLUMNS') IS NOT NULL
47
  DROP TABLE #SHARED_COLUMNS
48

49
SELECT
50
    [ColumnName]
51
INTO #SHARED_COLUMNS
52
FROM #Source
53
INTERSECT
54
SELECT
55
    [ColumnName]
56
FROM #Destination

Example — Sync Destination Schema with Source#

Here we will cursor over the columns that are missing and either create a series of ALTER TABLE statements to bring the destination table in sync with the source.

1
DECLARE
2
  @TableName SYSNAME = 'MyTable',
3
  @TableSchema SYSNAME = 'dbo',
4
  @IsDebug BIT = 0
5

6
IF OBJECT_ID('tempdb..#CHANGED_COLUMNS') IS NOT NULL
7
  DROP TABLE #CHANGED_COLUMNS
8

9
SELECT
10
    [ColumnName],
11
    [Type],
12
    [Length],
13
    [Precision],
14
    [Scale]
15
INTO #CHANGED_COLUMNS
16
FROM #Source
17
EXCEPT
18
SELECT
19
    [ColumnName],
20
    [Type],
21
    [Length],
22
    [Precision],
23
    [Scale]
24
FROM #Destination
25

26
DECLARE
27
  @Statement NVARCHAR(MAX) = '',
28
  @AlterColumnStatement NVARCHAR(MAX) = '',
29
  @ColumnName NVARCHAR(200),
30
  @Sql NVARCHAR(MAX) = ''
31

32
IF EXISTS (SELECT 1 FROM #CHANGED_COLUMNS)
33
BEGIN
34
  DECLARE
35
    @DataType NVARCHAR(200),
36
    @Length INT,
37
    @Precision INT,
38
    @Scale INT
39

40
  DECLARE
41
    @HasExistingColumns INT = 0,
42
    @HasNewColumns INT = 0
43

44
  DECLARE x CURSOR FOR
45
    SELECT [ColumnName], [Type], [Length], [Precision], [Scale]
46
    FROM #COLUMNS
47
    ORDER BY 1
48

49
  OPEN x
50
  FETCH NEXT FROM x INTO @ColumnName, @DataType, @Length, @Precision, @Scale
51
  WHILE @@FETCH_STATUS = 0
52
  BEGIN
53
    DECLARE @Part NVARCHAR(MAX)
54
    DECLARE @AlterColumnStub NVARCHAR(MAX) = '
55
ALTER TABLE [{2}].[{0}]
56
ALTER COLUMN {1}'
57

58
    DECLARE @ColumnExists INT = 0
59
    IF EXISTS(SELECT 1 FROM #Destination WHERE [ColumnName] = @ColumnName)
60
    BEGIN
61
      SELECT @ColumnExists = 1
62
      SELECT @HasExistingColumns = 1
63
    END
64
    ELSE
65
    BEGIN
66
      SELECT @HasNewColumns = 1
67
    END
68

69
    IF @DataType = 'timestamp'
70
    BEGIN
71
      SELECT @DataType = 'varbinary'
72
      SELECT @Length = 85
73
    END
74

75
    IF @DataType IN('char','varchar','binary','varbinary','nvarchar','nchar')
76
    BEGIN
77
      SELECT @Part = '[' + @ColumnName + '] ' + @DataType + '(' + ISNULL(NULLIF(CONVERT(NVARCHAR,@Length), '-1'), 'MAX') + ')'
78

79
      IF(@ColumnExists = 1)
80
      BEGIN
81
        SELECT @AlterColumnStatement = @AlterColumnStatement + REPLACE(@AlterColumnStub, '{1}', @Part)
82
      END
83
      ELSE
84
      BEGIN
85
        SELECT @Statement = @Statement + @Part + ','
86
      END
87
    END
88
    ELSE IF @DataType IN('decimal','numeric')
89
    BEGIN
90
      SELECT @Part = '[' + @ColumnName + '] ' + @DataType + '(' + CONVERT(NVARCHAR,@Precision) + ',' + CONVERT(NVARCHAR,@Scale) + ')'
91

92
      IF(@ColumnExists = 1)
93
      BEGIN
94
        SELECT @AlterColumnStatement = @AlterColumnStatement + REPLACE(@AlterColumnStub, '{1}', @Part)
95
      END
96
      ELSE
97
      BEGIN
98
        SELECT @Statement = @Statement + @Part + ','
99
      END
100
    END
101
    ELSE
102
    BEGIN
103
      SELECT @Part = '[' + @ColumnName + '] ' + @DataType
104

105
      IF(@ColumnExists = 1)
106
      BEGIN
107
        SELECT @AlterColumnStatement = @AlterColumnStatement + REPLACE(@AlterColumnStub, '{1}', @Part)
108
      END
109
      ELSE
110
      BEGIN
111
        SELECT @Statement = @Statement + @Part + ','
112
      END
113
    END
114
  FETCH NEXT FROM x INTO @ColumnName, @DataType, @Length, @Precision, @Scale
115
  END
116
  CLOSE x
117
  DEALLOCATE x
118

119
  IF (LEN(@Statement) > 1)
120
  BEGIN
121
    SELECT @Statement = SUBSTRING(@Statement, 1, LEN(@Statement) - 1)
122
  END
123

124
  -- determine if is alter or create statement
125
  IF OBJECT_ID(@EntityName) IS NOT NULL
126
  BEGIN
127
    IF (@HasExistingColumns = 1)
128
    BEGIN
129
      SELECT @Sql += @AlterColumnStatement
130
    END
131

132
    IF (@HasNewColumns = 1)
133
    BEGIN
134
      SELECT @Sql += '
135
ALTER TABLE [{2}].[{0}]
136
ADD {1}'
137
    END
138
  END
139

140
  SELECT @Sql = REPLACE(@Sql, '{0}', @TableName)
141
  SELECT @Sql = REPLACE(@Sql, '{1}', @Statement)
142
  SELECT @Sql = REPLACE(@Sql, '{2}', @TableSchema)
143

144
    IF @IsDebug = 1
145
        PRINT (@Sql)
146
    IF @IsDebug = 0
147
        EXEC (@Sql)
148
END

Example — Generate Statement Layouts#

Here we generate layouts that can be used in SELECT, INSERT and UPDATE statements assuming the source table is aliased as [source].

1
IF OBJECT_ID('tempdb..#SHARED_COLUMNS') IS NOT NULL
2
  DROP TABLE #SHARED_COLUMNS
3

4
SELECT
5
    [ColumnName]
6
INTO #SHARED_COLUMNS
7
FROM #Source
8
INTERSECT
9
SELECT
10
    [ColumnName]
11
FROM #Destination
12

13
-- generate layout --
14
DECLARE
15
    @ColumnFormat NVARCHAR(MAX) = '',
16
    @UpdateFormat NVARCHAR(MAX) = '',
17
    @ValuesFormat NVARCHAR(MAX) = ''
18

19
SELECT
20
    @ColumnFormat = STUFF((SELECT ', [' + [ColumnName] + ']' FROM #SHARED_COLUMNS ORDER BY [ColumnName] FOR XML PATH('')), 1, 2, ''),
21
    @UpdateFormat = STUFF((SELECT ', [' + [ColumnName] + '] = [source].[' + [ColumnName] + ']' FROM #SHARED_COLUMNS ORDER BY [ColumnName] FOR XML PATH('')), 1, 2, ''),
22
    @ValuesFormat = STUFF((SELECT ', [source].[' + [ColumnName] + ']' FROM #SHARED_COLUMNS ORDER BY [ColumnName] FOR XML PATH('')), 1, 2, '')
23

24
PRINT @ColumnFormat
25
PRINT @UpdateFormat
26
PRINT @ValuesFormat

~ SK