MS SQL create tables given a schema

create-tables.sql

-- MS SQL Server 2016
-- Generate "create table" for each table within the schema
-- https://stevesohcot.medium.com/sql-to-generate-create-table-statements-for-all-tables-given-a-schema-in-ms-sql-server-046f7ffde9a6

SELECT
	'CREATE TABLE '  + SCHEMA_NAME(t.schema_id) + '.' + t.name + ' (' +
		STUFF ((
			SELECT ', ' + c2.name + ' ' + type_name(c2.user_type_id) +
				CASE
					WHEN c2.is_nullable = 1 THEN 'NULL'
					ELSE ' NOT NULL'
				END + 
				CASE
					WHEN c2.column_id = 1 AND c2.is_identity = 1 THEN ' IDENTITY (1,1)'
					ELSE ''
				END +
				CASE
					WHEN pk.column_id IS NOT NULL THEN ' PRIMARY KEY'
					ELSE ''
				END
			FROM sys.columns c2
			LEFT JOIN (
				SELECT ic.object_id, ic.column_id, ic.index_column_id
				FROM sys.index_columns ic
				JOIN sys.indexes i ON 
						i.object_id  = ic.object_id
					AND i.index_id = ic.index_id
				WHERE i.is_primary_key = 1
			) pk ON 
					pk.object_id = c2.object_id
				AND pk.column_id = c2.column_id
			WHERE c2.object_id = t.object_id
			ORDER BY c2.column_id
			FOR XML PATH (''), TYPE
			).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + 
		')'
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE s.name = 'dbo'
ORDER BY t.name;