--todo: keys, working linebreaks

SELECT CONCAT ('CREATE TABLE ', tbl.name, '(', col.colstring, ');')
FROM sys.tables tbl
INNER JOIN (
	SELECT DISTINCT col2.object_id, SUBSTRING((
				SELECT CONCAT (',', col1.Name, ' ', datatype.name, CASE WHEN col1.max_length > 0 THEN CONCAT ('(', col1.max_length, ')') WHEN col1.max_length < 0 AND datatype.name IN ('nvarchar', 'varchar', 'varbinary', 'binary') THEN '(max)' WHEN col1.precision > 0 AND col1.scale > 0 THEN CONCAT ('(', col1.precision, col1.scale, ')') WHEN col1.precision > 0 THEN CONCAT ('(', col1.precision, ')') ELSE '' END, ' ', CASE col1.is_nullable WHEN 1 THEN 'NULL' ELSE 'NOT NULL' END) AS [text()]
				FROM sys.columns col1
				INNER JOIN sys.types datatype
					ON datatype.user_type_id = col1.user_type_id
				WHERE col1.object_id = col2.object_id
				ORDER BY col1.object_id
				FOR XML PATH('')
				), 2, 1000) [colstring]
	FROM sys.columns col2
	) col
	ON tbl.object_id = col.object_id