Generate dynamic DDL table definitions from system views

generate-table-definitions.sql

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;