create table based on existing table schema

create.table.based.on.existing.schema.sql

declare
	@templateSchemaName nvarchar(max)
	, @templateTableName nvarchar(max)
	, @newSchemaName nvarchar(max)
	, @newTableName nvarchar(max)
	
with columninfo as (
select
	t.name table_name
	, c.name column_name
	, c.is_nullable
	, c.precision
	, c.scale
	, ty.name data_type
	, c.max_length
	, c.is_computed
	, cc.is_persisted
	, cc.definition
from
	sys.schemas s
	inner join sys.tables t on t.schema_id = s.schema_id
	inner join sys.columns c on c.object_id = t.object_id
	inner join sys.types ty on ty.user_type_id= c.user_type_id
	left join sys.computed_columns cc on c.column_id = cc.column_id and c.object_id = cc.object_id
where
	s.name = @templateSchemaName
	and t.name = @templateTableName
)
, complete_column as (
	select 
	quotename(column_name) + ' ' +
	case 
		when is_computed = 1 then 
			' as ' + definition
			+ case 
				when is_persisted = 1 then 
					' persisted' 
					+ case when is_nullable = 1 then ' null' else ' not null' end
				else '' end 
		else
			case 
				when data_type like '%char%' then data_type + '(' + cast(max_length as nvarchar(max)) + ')'
				when data_type like 'decimal%' then data_type + '(' + cast(precision as nvarchar(max)) +','+cast(scale as nvarchar(max)) + ')'
				else data_type
			end
			+ case
				when is_nullable = 1 then ' null'
				else ' not null'
			end
		end
	complete_column_expr
	, table_name
	from
		columninfo
)
select
	'create table ' + quotename(@newSchemaName) + '.' + quotename(@newTableName) + ' ('
	+
	stuff(
		(
			select
				',
' 
				+ complete_column_expr
			from 
				complete_column innerc
			where innerc.table_name = outerc.table_name
			for xml path(''), type
			).value('.[1]', 'nvarchar(max)')
		,1,2, '')
	+ ')'
from
	complete_column outerc
group by
	table_name