October 29, 2015 at 1:28 am
And here is part 2 again, for those people who prefer their SQL formatted 🙂
declare @object_name sysname
,@object_id int
,@SQL nvarchar(max);
select @object_name = '[' + object_schema_name(o.object_id) + '].[' + object_name(o.object_id) + ']'
,@object_id = o.object_id
from (select object_id = object_id('TableName', 'U')
) o;
select @SQL = 'CREATE TABLE ' + @object_name + char(13) + '(' + char(13)
+ stuff((select char(13) + ' , [' + c.name + '] ' + case when c.is_computed = 1 then 'AS ' + object_definition(c.object_id, c.column_id)
else case when c.system_type_id != c.user_type_id
then '[' + schema_name(tp.schema_id) + '].[' + tp.name + ']'
else '[' + upper(tp.name) + ']'
end + case when tp.name in ('varchar', 'char', 'varbinary', 'binary')
then '(' + case when c.max_length = -1 then 'MAX'
else cast(c.max_length as varchar(5))
end + ')'
when tp.name in ('nvarchar', 'nchar')
then '(' + case when c.max_length = -1 then 'MAX'
else cast(c.max_length / 2 as varchar(5))
end + ')'
when tp.name in ('datetime2', 'time2', 'datetimeoffset')
then '(' + cast(c.scale as varchar(5)) + ')'
when tp.name = 'decimal'
then '(' + cast(c.precision as varchar(5)) + ','
+ cast(c.scale as varchar(5)) + ')'
else ''
end + case when c.collation_name is not null
and c.system_type_id = c.user_type_id
then ' COLLATE ' + c.collation_name
else ''
end + case when c.is_nullable = 1 then ' NULL'
else ' NOT NULL'
end + case when c.default_object_id != 0
then ' CONSTRAINT [' + object_name(c.default_object_id) + ']'
+ ' DEFAULT ' + object_definition(c.default_object_id)
else ''
end + case when cc.object_id is not null
then ' CONSTRAINT [' + cc.name + '] CHECK '
+ cc.definition
else ''
end + case when c.is_identity = 1
then ' IDENTITY('
+ cast(IDENTITYPROPERTY(c.object_id,
'SeedValue') as VARCHAR(5))
+ ','
+ cast(IDENTITYPROPERTY(c.object_id,
'IncrementValue') as VARCHAR(5))
+ ')'
else ''
end
end
from sys.columns c with (nolock)
join sys.types tp with (nolock) on c.user_type_id = tp.user_type_id
left join sys.check_constraints cc with (nolock) on c.[object_id] = cc.parent_object_id
and cc.parent_column_id = c.column_id
where c.[object_id] = @object_id
order by c.column_id
for xml path('')
,type).value('.', 'NVARCHAR(MAX)'), 1, 7, ' ')
+ isnull((select '
, CONSTRAINT [' + i.name + '] PRIMARY KEY ' + case when i.index_id = 1 then 'CLUSTERED'
else 'NONCLUSTERED'
end + ' ('
+ (select stuff(cast((select ', [' + col_name(ic.object_id, ic.column_id) + ']' + case when ic.is_descending_key = 1 then ' DESC'
else ''
end
from sys.index_columns ic with (nolock)
where i.object_id = ic.object_id
and i.index_id = ic.index_id
for
xml path(N'')
,type
) as nvarchar(max)), 1, 2, '')
) + ')'
from sys.indexes i with (nolock)
where i.object_id = @object_id
and i.is_primary_key = 1
), '') + char(13) + ');';
print @SQL;
October 29, 2015 at 2:02 am
To get the information on the XML Schema collections use the sys.xml_schema_collections view and the xml_schema_namespace function
😎
SELECT
SXSC.name
FROM sys.xml_schema_collections SXSC;
SELECT xml_schema_namespace(N'[RELATIONAL SCHEMA]',N'[XML SCHEMA COLLECTION NAME]');
There are also two undocumented stored procedures, sys.sp_xml_schema_rowset and sys.sp_xml_schema_rowset2, the latter will list all schema collections if executed without parameters.
Edit: additional info
October 29, 2015 at 4:33 pm
Thanks Buddy, I will give it a try!
Viewing 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply