Check for usage of an XML Schema Collection?

Question

As part of a script, in which execution is halted and changes are rolled back if there are any errors, I use the following statements to drop an xml schema collection if it exists

IF EXISTS (SELECT * FROM sys.xml_schema_collections WHERE name = 'MyXMLSchemaCollection')
BEGIN
    PRINT 'Drop MyXMLSchemaCollection';
    DROP XML SCHEMA COLLECTION MyXMLSchemaCollection;
END
GO

This works nicely except for when the schema collection is in use, the result of which is an error like the following:

Specified collection 'MyXMLSchemaCollection' cannot be dropped because it is used by object 'MyObject'.

How would I go about only dropping the schema collection if it is not used by any objects in the database? The xml schema collection does not need to be dropped if it's being used.

You already know how to drop it only if its unreferenced - your current code already does so. What are you actually seeking to do - avoid the error message? (Bear in mind that any further checks we perform will be redundant anyway since, as you've already discovered, SQL Server performs a check before allowing the drop to occur) — Damien_The_Unbeliever, Mar 29 '17 at 13:16
@Damien_The_Unbeliever I don't know how to determine if it is referenced, the if statement checks whether it exists, I want to alter the if statement so that in addition to checking for existence, it checks whether it is used by any objects in the database. — , Mar 29 '17 at 13:18
My point is, SQL Server performs this exact check already and, if it's being used it refuses to drop it and instead reports an error message. What do you want to do that is different from *what SQL Server is already doing*? — Damien_The_Unbeliever, Mar 29 '17 at 13:19
@Damien_The_Unbeliever There are other statements before and after the drop schema statement. I want to halt execution of the script and rollback if there are any errors in the script. The xml schema does not need to be dropped if it's being used. — , Mar 29 '17 at 13:24

score 0 · Accepted Answer · edited May 23 '17 at 12:32

Using the example XML Schema Collection from MSDN documentation ALTER XML SCHEMA COLLECTION (Transact-SQL) I found that you can determine if an XML Schema Collection is being referenced by a table using:

select distinct t.name from sys.tables t
left join sys.columns c ON c.object_id = t.object_id
left join sys.xml_schema_collections x ON x.xml_collection_id = c.xml_collection_id
where x.name = 'MyXMLSchemaCollection'

Also for stored procedures and I imagine other object types sys.sql_expression_dependencies table will be of use to you.

select 
    *
from sys.sql_expression_dependencies sed
left join sys.objects o ON o.object_id = sed.referencing_id
where referenced_entity_name = 'MyXMLSchemaCollection'

Have a look at these questions for more information on exploring object catalogs:

Check for usage of an XML Schema Collection?

1 Answers1