create table DDL based on existing table.sql

USE dbName
GO

declare @tableName as varchar(255) = 'table name'
set @tableName = @tableName+'%'

;with ident_cols as (
SELECT
	DB_NAME() AS database_name,
	schemas.name AS schema_name,
	tables.name AS table_name,
	columns.name AS column_name,
	types.name AS data_type,
	IDENT_CURRENT(quotename(schemas.name) + '.' + quotename(tables.name)) AS current_identity_value,
	IDENT_INCR(quotename(schemas.name) + '.' + quotename(tables.name)) AS identity_increment,
	CASE
		WHEN types.name = 'TINYINT' THEN CAST(255 AS BIGINT)
		WHEN types.name = 'SMALLINT' THEN CAST(32767 AS BIGINT)
		WHEN types.name = 'INT' THEN CAST(2147483647 AS BIGINT)
		WHEN types.name = 'BIGINT' THEN CAST(9223372036854775807 AS BIGINT)
		WHEN types.name IN ('DECIMAL', 'NUMERIC') THEN CAST(REPLICATE(9, (' + CAST(columns.precision AS VARCHAR(MAX)) +  -  + CAST(columns.scale AS VARCHAR(MAX)) + ')) AS BIGINT)
		ELSE -1
	END AS maximum_identity_value
FROM sys.tables
JOIN sys.columns ON tables.object_id = columns.object_id
JOIN sys.types ON types.user_type_id = columns.user_type_id
JOIN sys.schemas ON schemas.schema_id = tables.schema_id
WHERE columns.is_identity = 1
	and tables.name like @tableName
)

--select * from ident_cols

SELECT 
	c.TABLE_SCHEMA+'.'+c.TABLE_NAME as table_name
	,c.COLUMN_NAME
	,char(9) +  quotename(c.column_name) +' '+ quotename(c.data_type) 
	+ CASE 
		WHEN c.data_type IN ('decimal')
			THEN isnull('(' + convert(varchar, c.numeric_precision) + ', ' + convert(varchar, c.numeric_scale) + ')', '') 
		WHEN c.data_type IN ('varchar', 'nvarchar', 'char', 'nchar')
			THEN isnull('(' + 
			CASE WHEN c.character_maximum_length = -1
				THEN 'max'
				ELSE convert(varchar, c.character_maximum_length) 
		END + ')', '')
		ELSE '' END
	+ CASE WHEN ic.current_identity_value is NOT NULL THEN ' IDENTITY(1,'+convert(varchar,identity_increment)+') ' ELSE '' END
	+ CASE WHEN c.COLUMN_DEFAULT is NULL THEN '' ELSE ' DEFAULT '+convert(varchar,c.COLUMN_DEFAULT) END
	+ CASE WHEN c.IS_NULLABLE = 'YES' THEN ' NULL' ELSE ' NOT NULL' END
	+ ',' as create_table_script
	,current_identity_value, identity_increment, maximum_identity_value
FROM INFORMATION_SCHEMA.COLUMNS c 
left join ident_cols as ic on c.TABLE_NAME=ic.table_name and c.COLUMN_NAME=ic.column_name
WHERE c.TABLE_NAME LIKE @tableName
ORDER BY c.ordinal_position