Created
December 21, 2021 10:35
-
-
Save ax4413/50c73b93382c8878b1fb804354a99caf to your computer and use it in GitHub Desktop.
Generate dynamic DDL table definitions from system views
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DECLARE @TableName varchar(100) = 'SomeTableName' | |
SELECT 'CREATE TABLE ' + QUOTENAME(SCHEMA_NAME()) + '.' + QUOTENAME(t.name) + CHAR(10) + '(' + CHAR(10) | |
+ CHAR(9) + col.List + ') ON [' + ds.name + '];' AS SQLStatement | |
FROM sys.tables t | |
CROSS APPLY(SELECT DISTINCT STUFF( | |
(SELECT CHAR(9) + ', ' + QUOTENAME(c.name) + ' ' + QUOTENAME(ty.name) + | |
CASE ty.name | |
WHEN'char' THEN '(' + cast(c.max_length AS VARCHAR(5)) + ')' | |
WHEN'date' THEN '' | |
WHEN'datetime' THEN '' | |
WHEN'decimal' THEN '(' + cast(c.precision AS VARCHAR(5)) + ',' + cast(c.scale AS VARCHAR(5)) + ')' | |
WHEN'int' THEN '' | |
WHEN'smallint' THEN '' | |
WHEN'varchar' THEN '(' + CASE c.max_length WHEN-1 THEN 'MAX' ELSE cast(c.max_length AS VARCHAR(5)) END + ')' | |
ELSE'ERROR' -- This will cause the table creation script to fail parsing | |
END+ | |
' ' + CASE c.is_nullable WHEN 1 THEN 'NULL' ELSE 'NOT NULL' END + char(10) | |
FROM sys.columns c | |
INNER JOIN sys.types ty ON ty.user_type_id = c.user_type_id | |
WHERE c.object_id = t.object_id | |
ORDER BY c.column_id | |
FOR XML PATH('')),1,2,'') AS List | |
FROM sys.columns oc | |
) col | |
INNER JOIN sys.indexes i on i.object_id = t.object_id AND i.index_id < 2 | |
INNER JOIN sys.data_spaces ds on ds.data_space_id = i.data_space_id | |
WHERE t.type = 'U' | |
AND t.name = @TableName | |
ORDER BY t.name; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment