If you have ever tried to generate dynamic DDL table definition scripts from the system metadata (system catalog views) you will realize that is not quite as easy as you first imagined. If you have profiled the action of asking SSMS to generate the DDL for you, you will realize that you do not capture any trace data. Here is how to generate the a table creation script from the system meta data.
What is the script doing then? Not a lot really, its not that complicated…
We are using CROSS APPLY to left outer join to the correlated sub query. We need to use cross apply to get access to the outer variables from the inner correlated sub query, this would not be possible with a left outer join as we would not have access to variables out side of the scope of the join.
I am using the STUFF() function to insert strings into another string, this allows us to stuff each column name and sql type into a string which we can return to the outer query. If you haven’t used the stuff() function before www.sqlusa.com has some interesting examples.
Finally we are joining the sys views of indexes and data_spaces to get the file group which the table is stored on. To get the file group where the table is located we must understand how the storage of a table works. There are two types of table a HEAP table and a Clustered table. A heap table does not have a clustered index on it and as such its contents are not sorted. It can be identified in the sys.indexes table by having a index_id of zero and a type_desc of ‘HEAP’. A clustered table by contrast has a clustered index and its contents are ordered based on that index. It can be identified by its index_id of one. Therefore to get the file group which this table is stored on we need to get the index with an index_id less than two.
I don’t have a link to a definitive source, but it appears as though the index_id on the sys.indexes view when less than zero indicates that the heap table id zero of Clustered index id one.
There may be issues with this sql it is far from production tested, but it seems to work, i would appreciate any feedback on the sql if you identify issues or improvements.
I will hopefully be following up this post with the required DDL to generate primary keys, foreign keys, unique keys, indexes and constraints from sys views.
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) +
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
' ' + 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
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;
Thanks and keep coding