October 29, 2015 at 1:06 am
Hi All,
I am trying to copy the table data from db to another db, because some tables has xml column with a schema collection, I would like to get difinition of the xml schema collection and create it on the target db, otherwise I will get below error:
Xml columns cannot refer to schemata across databases.
My plan is for those tables with xml column with schema collection, I will:
1. Get all tables/columns that requires xml schema
SELECT t.name AS table_name,
SCHEMA_NAME(t.schema_id) AS schema_name,
c.name AS column_nam,
c2.name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
inner join sys.xml_schema_collections c2 on c.xml_collection_id = C2.xml_collection_id
2. Get table definition
DECLARE
@object_name SYSNAME
, @object_id INT
, @sql NVARCHAR(MAX)
SELECT
@object_name = '[' + OBJECT_SCHEMA_NAME(o.[object_id]) + '].[' + OBJECT_NAME([object_id]) + ']'
, @object_id = [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
3. Get XML Schema definition
4. Combine 2 and 3.
5
insert into newtable select .... cast(xmlColumnas xml) from OldDB.dbo.oldtable
I am stuck at step 3 now....
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;
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply