The Verb I Want: Replace
File this under this how to know what you have fix before you fix an XML Schema Collection
When I’m introducing folks to XML Schema Collections in SQL Server 2005, conversation usually goes something like this:
Blah blah blah blah use to make sure that XML instances are valid according to an XML Schema blah blah blah and you create them using CREATE XML SCHEMA COLLECTION name AS blah blah blah. Naturally, you can delete such a schema collection using DROP XML SCHEMA COLLECTION but you have to make sure there are no dependent objects on that before doing so. Some more talk and then I have to say it. The part of the talk I like the second least.
“So there is an alter for XML schema collections and to nobody’s surprise that starts with ALTER XML SCHEMA COLLECTION followed by the qualified name of the schema collection and a verb. In SQL Server 2005 there is one supported verb: ADD.”
As an mentor of mine says: Ick!
That means that the alter for an XML schema collection is really only good for doing either of things: adding additional elements to default namespace, or adding a schema for a new namespace. And that’s basically it. There’s really not a good way to fix a broken or out of date schema for an existing namespace. And that means – oh joy – that you need to alter all of the objects that use the schema to not do so before you go about fixing the schema. And the first step in that is, of course, getting an inventory of the objects you’ll need to touch.
If you have good documentation of your database design, that won’t be much of an issue. But if your database as been grown somewhat more… organically… it could be. So rather than struggling to do this by trial and error, it would be great if SQL Server had a way just telling us what objects depend on a given schema collection. Turns out that there isn’t a single source that will tell us everything we need to know, but it is not very hard to query a few Distributed Management Views (DMVs) to figure it out. Those views include:
- sys.xml_schema_collections – which provides a basic listing of the schemas in the current database.
- sys.column_xml_schema_collection_usages – tells that given column in table is bound to a given schema collection. You can join this to sys.columns to figure out what table the column belongs to.
- sys.parameter_xml_schema_collection_usages – describes the function and stored procedure parameters that are bound to schema collections. Again, you can join this to other DMVs to determine the objects using these parameters.
- sys.message_type_xml_schema_collection_usages – lists the Message Types that validate their message bodies using schema collections and which collections are used.
The down side of this there are no DMVs that, for example, tell us if the T-SQL code within a Stored Procedure, Trigger or Function internally uses a schema-bound instance.
Armed with that information, I decided to write up a simple Stored Procedure that will give you lists of the objects that have either direct or indirect dependencies statically on an XML schema in the current database. The source for that is shown below. Enjoy!
Maybe in the next version of SQL Server 2005, we'll get the Verb I really want and void this effort. Replace, man, replace.
-- @owningSchema is the security schema that the XML Schema collection belongs to
-- @schemaCollectionName is the name of the XML Schema Collection in question
create procedure dbo.FindObjectsDependentOnSchemaCollection
(@owningSchema nvarchar(255),@schemaCollectionName nvarchar(255))
as
begin
set nocount on
declare @schema_id int
declare @xml_collection_id int
-- Get the schema ID and xml collection id for the
-- the requested object, if it can be found.
select @schema_id = s.schema_id
, @xml_collection_id = xs.xml_collection_id
from sys.xml_schema_collections xs
join sys.schemas s on xs.schema_id = s.schema_id
where s.name = @owningSchema
and xs.name = @schemaCollectionName
if(@schema_id is null) or (@xml_collection_id is null)
RaisError('Could not find the requested schema collection',15,1)
-- Find the columns that are schema bound, and then
-- find the tables that those columns are part of
-- returns the type of object, that object's name,
-- the column's relative position in the table
select o.type_desc,ss.name+'.'+o.name as object
, c.name as columnName
, c.column_id
from sys.objects o
join sys.column_xml_schema_collection_usages s
on o.object_id = s.object_id
join sys.schemas ss
on o.schema_id = ss.schema_id
join sys.columns c
on c.column_id = s.column_Id
and c.object_id = s.object_id
where ss.schema_id = @schema_id
and s.xml_collection_id = @xml_collection_id
-- Returns the name object type (procedure or function) and
-- that object's name, the parameter and name and position
-- (0 for return value)
select o.type_desc,ss.name+'.'+o.name as objectName
, pr.name
, p.parameter_id
from sys.objects o
join sys.parameter_xml_schema_collection_usages p
on o.object_id = p.object_id
join sys.parameters pr
on o.object_id = pr.object_id and pr.parameter_id > 0
join sys.schemas ss
on o.schema_id = ss.schema_id
where ss.schema_id = @schema_id
and p.xml_collection_id = @xml_collection_id
-- and finally, the Message Types by name
select 'message type' as objectType
,mt.name as objectName
from sys.message_type_xml_schema_collection_usages mtc
join sys.service_message_types mt
on mtc.message_type_id = mt.message_type_id
where mtc.xml_collection_id = @xml_collection_id
end
go