Is XmlSchema in use?

SQL 2005 brings XML as a native data type, and what good is XML if it's not typed? The following function helps identify XML Schemas that exist in the current database. It also helps in identifying XML Schema Collections that are in use in table columns and/or in procedures as typed XML parameters. The XmlSchemaName parameter should contain a qualified XML Schema Collection name (i.e. <schema>.<name>). For unqualified names the default schema of the caller is used.

create function dbo.fnGet_XmlSchema_IsUsed
 (
 @XmlSchemaName sysname
 )
returns bit
/*
Return:
1 XML Schema exists and is in use
0 XML Schema exists and is not in use
null XML Schema does not exist
*/
as
begin
 declare @result  bit
 declare @objectName sysname
 declare @schemaId int

 set @objectName = parsename(@XmlSchemaName, 1)
 
 select @schemaId
   = case
    when (parsename(@XmlSchemaName, 2) is null)
     then schema_id()
    else schema_id(parsename(@XmlSchemaName, 2))
    end

 if (exists (
   select sys.xml_schema_collections.xml_collection_id
    from sys.xml_schema_collections
    where (sys.xml_schema_collections.schema_id = @schemaId)
     and (sys.xml_schema_collections.[name] = @objectName)
   ))
  begin
   if (exists (
     select sys.xml_schema_collections.xml_collection_id
      from sys.xml_schema_collections
       inner join sys.column_xml_schema_collection_usages
         on sys.column_xml_schema_collection_usages.xml_collection_id
         = sys.xml_schema_collections.xml_collection_id
      where (sys.xml_schema_collections.schema_id = @schemaId)
       and (sys.xml_schema_collections.[name] = @objectName)
     union
     select sys.xml_schema_collections.xml_collection_id
      from sys.xml_schema_collections
       inner join sys.parameter_xml_schema_collection_usages
         on sys.parameter_xml_schema_collection_usages.xml_collection_id
         = sys.xml_schema_collections.xml_collection_id
      where (sys.xml_schema_collections.schema_id = @schemaId)
       and (sys.xml_schema_collections.[name] = @objectName)
     ))
    begin
     set @result = 1
    end
   else
    begin
     set @result = 0
    end
  end
 else
  begin
   set @result = null
  end

 return @result
end
go

Return values:

1       XML Schema exists and is in use
0       XML Schema exists and is not in use
null    XML Schema does not exist

ML