SQL Snippets - Copy-pasteable CREATE TABLE() snippet
SQL Snippets 🕊️🧙🏼♂️✨
SQL Snippets - Copy-pasteable CREATE TABLE() snippet
Intro
I come from a PowerBI / PowerQuery background, so I’m not your traditional SQL DBA, I’m a completely self-taught n00b - so take any of my SQL-related content with a heavy pinch of salt 🧂 🕊️
As I meander through the many SQL challenges that I encounter, I hope to share some helpful tips, techniques and discoveries along the way that will make development that much more satisfying.
Data
This example uses the AdventureWorks 2022 sample database:
AdventureWorks Sample DB
Build
When creating a data pipeline of sorts, we may wish create tables: Landing (LND), Staging (STG), or feature/enterprise (ENT). What I found quite tedious and time consuming was having to physically type the CREATE TABLE () script, declaring each column and then declare the data types for each of those columns as well as the character limit for text columns, one-by-one. This is time-consuming and onerous, particularly for wider tables.
Originally, I would copy and pate the last of columns into Excel, then build a formula to add leading-commas and it just felt like a rather clunky process.
So I devised a SQL script that will obtain the column names and the current data types and produce a table output with leading commas which can then be copy-and-pasted directly into SQL and boom 🔥! One-click and go. This solution harnesses SQL’s system INFORMATION_SHEMA.COLUMNS table
Step 1: View Column Information
The script to view your existing Table/Column schema looks like this:
1
2
3
4
5
SELECT * FROM INFORMATION_SCHEMA.COLUMNS -- look at all tables in current server/db
WHERE -- filter for
TABLE_SCHEMA = 'Sales' -- table schema = 'Sales' (normally 'dbo')
AND TABLE_NAME = 'Customer' -- table name = 'Customer'
This produces the following result:
As you can see, there is some useful information. We have some juicy columns such as:
- COLUMN_NAME
- DATA_TYPE
- CHARACTER_MAXIMUM_LENGTH
- NUMERIC_PRECISION
These are all very useful, but we can’t extract and utilise this info in any automated fashion, without manually piecing the puzzle together in some fashion. In the next step, we’ll reshape this info
Step 2: Reshape INFORMATION_SCHEMA output
Let’s run a query that transforms and combines these columns:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
WITH info_schema_columns_data AS (
SELECT
COLUMN_NAME AS column_name,
DATA_TYPE AS old_data_type,
CASE
WHEN DATA_TYPE IN ('varchar', 'text') THEN 'nvarchar'
WHEN DATA_TYPE = 'char' THEN 'nchar'
WHEN DATA_TYPE IN ('bit', 'int', 'bigint', 'smallint', 'tinyint') THEN 'int'
WHEN DATA_TYPE IN ('decimal', 'numeric', 'float', 'real', 'money', 'smallmoney') THEN 'decimal'
ELSE DATA_TYPE
END AS simplified_data_type,
NUMERIC_PRECISION AS numeric_precision,
NUMERIC_SCALE AS numeric_scale,
CHARACTER_MAXIMUM_LENGTH AS text_character_length,
CASE
WHEN DATA_TYPE IN ('bit', 'int', 'bigint', 'smallint', 'tinyint') THEN NULL -- No precision/scale for whole numbers
WHEN DATA_TYPE IN ('decimal', 'numeric', 'float', 'real', 'money', 'smallmoney') -- decimal precision maintained
THEN CAST(CONCAT(NUMERIC_PRECISION, ',', NUMERIC_SCALE) AS NVARCHAR)
ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS NVARCHAR)
END AS character_length,
IS_NULLABLE AS is_nullable,
ROW_NUMBER() OVER (ORDER BY ORDINAL_POSITION) AS rn
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'Sales'
AND TABLE_NAME = 'vStoreWithDemographics'
),
cte_prepared_output AS (
SELECT
column_name,
old_data_type,
simplified_data_type,
text_character_length,
character_length,
-- Generate a final character length representation
CASE
WHEN character_length IS NULL THEN NULL
WHEN text_character_length = -1 THEN '(MAX)'
WHEN character_length IS NOT NULL THEN CONCAT('(', character_length, ')')
ELSE CONCAT( '(',
CASE
WHEN text_character_length BETWEEN 9 AND 10 THEN 10 -- round to 10
WHEN text_character_length BETWEEN 11 AND 15 THEN 15 -- round 11-15
WHEN text_character_length BETWEEN 16 AND 20 THEN 20 -- round 16-20
WHEN text_character_length BETWEEN 90 AND 100 THEN 100 -- round 90-100
WHEN text_character_length BETWEEN 101 AND 200 THEN 200 -- round 101-200
WHEN text_character_length BETWEEN 201 AND 255 THEN 255 -- round 201-255
ELSE text_character_length
END
, ')' )
END AS final_character_length,
is_nullable,
rn
FROM info_schema_columns_data
)
SELECT
column_name,
old_data_type,
simplified_data_type,
text_character_length,
character_length,
final_character_length,
is_nullable,
-- Generating column names for INSERT INTO statement
CASE
WHEN rn = 1 THEN column_name
ELSE ', ' + column_name
END AS insert_table_script,
-- Same as above, but wrapped in square brackets for SQL Server compatibility
CASE
WHEN rn = 1 THEN CONCAT('[', column_name, ']')
ELSE ', ' + CONCAT('[', column_name, ']')
END AS insert_table_script_v2,
-- Generating CREATE TABLE script
CASE
WHEN rn = 1 THEN CONCAT_WS(' ', column_name , UPPER(simplified_data_type) , COALESCE(final_character_length, ''))
ELSE ', ' + CONCAT_WS(' ', column_name , UPPER(simplified_data_type) , COALESCE(final_character_length, ''))
END AS create_table_script,
-- Same as above but with square brackets for SQL Server compatibility
CASE
WHEN rn = 1 THEN CONCAT_WS(' ', CONCAT('[', column_name, ']') , UPPER(simplified_data_type) , COALESCE(final_character_length, ''))
ELSE ', ' + CONCAT_WS(' ', CONCAT('[', column_name, ']') , UPPER(simplified_data_type) , COALESCE(final_character_length, ''))
END AS create_table_script_v2
FROM cte_prepared_output;
You can see in these highlight sections, we now have ready-made columns which allow us to copy-paste into a new script, saving valuable time.
Step 3: Run your script 🤓
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
-- paste
CREATE TABLE dbo.NewTable (
BusinessEntityID INT
, Name NVARCHAR (50)
, AnnualSales DECIMAL (19,4)
, AnnualRevenue DECIMAL (19,4)
, BankName NVARCHAR (50)
, BusinessType NVARCHAR (5)
, YearOpened INT
, Specialty NVARCHAR (50)
, SquareFeet INT
, Brands NVARCHAR (30)
, Internet NVARCHAR (30)
, NumberEmployees INT
)
INSERT INTO dbo.NewTable
(
[BusinessEntityID]
, [Name]
, [AnnualSales]
, [AnnualRevenue]
, [BankName]
, [BusinessType]
, [YearOpened]
, [Specialty]
, [SquareFeet]
, [Brands]
, [Internet]
, [NumberEmployees]
)
(
SELECT
[BusinessEntityID]
, [Name]
, [AnnualSales]
, [AnnualRevenue]
, [BankName]
, [BusinessType]
, [YearOpened]
, [Specialty]
, [SquareFeet]
, [Brands]
, [Internet]
, [NumberEmployees]
FROM [AW_2022].[Sales].[vStoreWithDemographics]
)
;
Step 4: Stored Procedure for easy re-use
This is all very good, but we will want to reuse this many times in future, so instead of using the entire script, we can save it as a stored procedure, and simply run the following script
1
2
3
EXEC dbo.SP_get_data_types @table_schema = 'Sales', @table_name = 'vStoreWithDemographics';
But first,run this script to save your stored procedure - then run the above script:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
CREATE OR ALTER PROCEDURE dbo.SP_get_data_types
@table_schema NVARCHAR(128),
@table_name NVARCHAR(128)
AS
--EXEC dbo.SP_get_data_types @table_schema = 'Data', @table_name = 'Orders';
-- EXEC dbo.SP_get_data_types @table_schema = 'Data', @table_name = 'Customer';
/***
------------------------------------------------------------------------------
______________ ___ _______ ___ ___ ____ ____________ __ _____ ____
/ __/_ __/ __ \/ _ \/ __/ _ \ / _ \/ _ \/ __ \/ ___/ __/ _ \/ / / / _ \/ __/
_\ \ / / / /_/ / , _/ _// // / / ___/ , _/ /_/ / /__/ _// // / /_/ / , _/ _/
/___/ /_/ \____/_/|_/___/____/ /_/ /_/|_|\____/\___/___/____/\____/_/|_/___/
-----------------------------------------------------------------------------
____ __ ___________ __
/ __ \/ / / / __/ _ \ \/ /
/ /_/ / /_/ / _// , _/\ /
\___\_\____/___/_/|_| /_/
-----------------------------------------------------------------------------
***/
BEGIN
SET NOCOUNT ON;
WITH info_schema_columns_data AS (
SELECT
COLUMN_NAME AS column_name,
DATA_TYPE AS old_data_type,
CASE
WHEN DATA_TYPE IN ('varchar', 'text') THEN 'nvarchar'
WHEN DATA_TYPE = 'char' THEN 'nchar'
WHEN DATA_TYPE IN ('bit', 'int', 'bigint', 'smallint', 'tinyint') THEN 'int'
WHEN DATA_TYPE IN ('decimal', 'numeric', 'float', 'real', 'money', 'smallmoney') THEN 'decimal'
ELSE DATA_TYPE
END AS simplified_data_type,
NUMERIC_PRECISION AS numeric_precision,
NUMERIC_SCALE AS numeric_scale,
CHARACTER_MAXIMUM_LENGTH AS text_character_length,
CASE
WHEN DATA_TYPE IN ('bit', 'int', 'bigint', 'smallint', 'tinyint') THEN NULL -- No precision/scale for whole numbers
WHEN DATA_TYPE IN ('decimal', 'numeric', 'float', 'real', 'money', 'smallmoney') -- decimal precision maintained
THEN CAST(CONCAT(NUMERIC_PRECISION, ',', NUMERIC_SCALE) AS NVARCHAR)
ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS NVARCHAR)
END AS character_length,
IS_NULLABLE AS is_nullable,
ROW_NUMBER() OVER (ORDER BY ORDINAL_POSITION) AS ix
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'Sales'
AND TABLE_NAME = 'vStoreWithDemographics'
),
cte_prepared_output AS (
SELECT
column_name,
old_data_type,
simplified_data_type,
text_character_length,
character_length,
-- Generate a final character length representation
CASE
WHEN character_length IS NULL THEN NULL
WHEN text_character_length = -1 THEN '(MAX)'
WHEN character_length IS NOT NULL THEN CONCAT('(', character_length, ')')
ELSE CONCAT( '(',
CASE
WHEN text_character_length BETWEEN 9 AND 10 THEN 10 -- round to 10
WHEN text_character_length BETWEEN 11 AND 15 THEN 15 -- round 11-15
WHEN text_character_length BETWEEN 16 AND 20 THEN 20 -- round 16-20
WHEN text_character_length BETWEEN 90 AND 100 THEN 100 -- round 90-100
WHEN text_character_length BETWEEN 101 AND 200 THEN 200 -- round 101-200
WHEN text_character_length BETWEEN 201 AND 255 THEN 255 -- round 201-255
ELSE text_character_length
END
, ')' )
END AS final_character_length,
is_nullable,
ix
FROM info_schema_columns_data
)
SELECT
column_name,
old_data_type,
simplified_data_type,
text_character_length,
character_length,
final_character_length,
is_nullable,
-- Generating column names for INSERT INTO statement
CASE
WHEN ix = 1 THEN column_name
ELSE ', ' + column_name
END AS insert_table_script,
-- Same as above, but wrapped in square brackets for SQL Server compatibility
CASE
WHEN ix = 1 THEN CONCAT('[', column_name, ']')
ELSE ', ' + CONCAT('[', column_name, ']')
END AS insert_table_script_v2,
-- Generating CREATE TABLE script
CASE
WHEN ix = 1 THEN CONCAT_WS(' ', column_name , UPPER(simplified_data_type) , COALESCE(final_character_length, ''))
ELSE ', ' + CONCAT_WS(' ', column_name , UPPER(simplified_data_type) , COALESCE(final_character_length, ''))
END AS create_table_script,
-- Same as above but with square brackets for SQL Server compatibility
CASE
WHEN ix = 1 THEN CONCAT_WS(' ', CONCAT('[', column_name, ']') , UPPER(simplified_data_type) , COALESCE(final_character_length, ''))
ELSE ', ' + CONCAT_WS(' ', CONCAT('[', column_name, ']') , UPPER(simplified_data_type) , COALESCE(final_character_length, ''))
END AS create_table_script_v2
FROM cte_prepared_output;
END;
/*
__________________________________________________
| insert_table_script | create_table_script |
| ------------------- | ------------------------ |
| column1 | column1 NVARCHAR (10) |
| , column2 | , column2 INT |
| , column3 | , column3 DECIMAL (18,5) |
*/
. . .
En Fin, Serafin
Thank you for staying to the end of the article… I hope you find it useful 😊. See you soon, and remember… #StayQueryous!🧙♂️🪄
This post is licensed under CC BY 4.0 by the author.