You could roll your own scripting (I've written myself help SQL scripts using sys.objects
sys.columns
etc...
I use a variation of the below script to get me the field definitions, then it's just a matter of string concatenation (with setting SSMS to output to Text):
DECLARE @TableName varchar(200)
SET @TableName = '[MyTable]'
INSERT INTO @WorkingTable (...)
select
CanUpdate, FieldName, PrimaryKey, FieldType, FieldLength, FieldPrecision, FieldScale,
case FieldType
when 'varchar' then '(' + case when FieldLength = -1 then 'max' else convert(varchar, FieldLength) end + ')'
when 'varbinary' then '(' + convert(varchar, FieldLength) + ')'
when 'nvarchar' then '(' + case when FieldLength = -1 then 'max' else convert(varchar, FieldLength / 2) end + ')'
when 'char' then '(' + convert(varchar, FieldLength) + ')'
when 'nchar' then '(' + convert(varchar, FieldLength / 2) + ')'
when 'numeric' then '(' + CONVERT(varchar, FieldPrecision) + ', ' + CONVERT(varchar, FieldScale) + ')'
when 'decimal' then '(' + CONVERT(varchar, FieldPrecision) + ', ' + CONVERT(varchar, FieldScale) + ')'
else ''
end as fieldLengthString
from
(select
MIN(column_id) as column_id,
CanUpdate, FieldName, PrimaryKey, FieldType, FieldLength, FieldPrecision, FieldScale
from
(select
sc.column_id,
MIN(ISNULL(convert(int, ic.is_included_column),case when sc.is_identity = 1 then 0 else 1 end)) as CanUpdate,
sc.name as FieldName,
max(case when ind.is_primary_key = 1 and ic.is_included_column = 0 THEN 1 ELSE 0 END) as PrimaryKey,
typ.name as FieldType,
sc.max_length as FieldLength,
sc.precision as FieldPrecision,
sc.scale as FieldScale
from
sys.columns sc
left join sys.indexes ind on ind.object_id = sc.object_id and (ind.is_unique = 1 or ind.is_unique_constraint = 1 or ind.index_id = 1)
left join sys.index_columns ic on ic.object_id = ind.object_id and ic.index_id = ind.index_id and ic.column_id = sc.column_id
left join sys.types typ on typ.user_type_id = sc.user_type_id
where
OBJECT_NAME(sc.object_id) = @TableName
group by
sc.column_id,
sc.name,
typ.name,
sc.max_length,
sc.precision,
sc.scale
) t
group by
CanUpdate, FieldName, PrimaryKey, FieldType, FieldLength, FieldPrecision, FieldScale) s
order by column_id
so to output the create table script would be:
/* Parse up strings of fields in table, and fields in Primary key */
DECLARE @FieldDefinition varchar(max), @PKFields varchar(max)
SET @FieldDefinition = ''
SELECT @FieldDefinition = @FieldDefinition + CASE @FieldDefinition WHEN '' THEN '' ELSE ', ' END + '
[' + wt.FieldName + '] [' + wt.FieldType + ']' + fieldLengthString
FROM @WorkingTable wt
SELECT @PKFields = @PKFields + case when @PKUpdMatch = '' THEN '' ELSE ',
[' + wt.FieldName + '] ASC'
FROM @WorkingTable wt WHERE wt.PrimaryKey = 1
/* now print out the string for creating the table */
print 'SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[' + @TableName + '](' + @FieldDefinition + ',
CONSTRAINT [PK_' + @TableName + '] PRIMARY KEY NONCLUSTERED
(
' + PKFields + '
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO'