XmlSchema usage

This post is basically a follow-up to the fnGet_XmlSchema_IsUsed function I posted recently. It lists SQL objects and columns (parameters) where a specific XML Schema is used. If null is specified as the parameter, the function returns a list of all XML Schema Collections and their usage.

create function dbo.fnList_XmlSchema_Usage
 (
 @XmlSchemaName sysname  = null
 )
returns @XmlSchemaUsage table
  (
  XmlSchemaName sysname
  ,XmlSchemaSchema sysname
  ,ObjectName sysname
  ,ObjectSchema sysname
  ,ObjectType sysname
  ,ColumnParameterName sysname
  ,ColumnParameterType sysname
  )
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 (@XmlSchemaName is null)
  begin
   insert @XmlSchemaUsage
    (
    XmlSchemaName
    ,XmlSchemaSchema
    ,ObjectName
    ,ObjectSchema
    ,ObjectType
    ,ColumnParameterName
    ,ColumnParameterType
    )
    select XmlSchemas.[name] as XmlSchemaName
     ,schema_name(XmlSchemas.schema_id) as XmlSchemaSchema
     ,sys.all_objects.[name] as ObjectName
     ,schema_name(sys.all_objects.schema_id) as ObjectSchema
     ,sys.all_objects.type_desc as ObjectType
     ,sys.syscolumns.[name] as ColumnParameterName
     ,UsageType as ColumnParameterType
     from (
      select ColUsage.[object_id] as ObjectId
       ,ColUsage.column_id as ColumnParameterId
       ,ColUsage.xml_collection_id as XmlCollectionId
       ,'COLUMN' as UsageType
       from sys.xml_schema_collections SchemaCollections
        inner join sys.column_xml_schema_collection_usages ColUsage
          on ColUsage.xml_collection_id
          = SchemaCollections.xml_collection_id
      union
      select ParUsage.[object_id]
       ,ParUsage.parameter_id
       ,ParUsage.xml_collection_id
       ,'PARAMETER'
       from sys.xml_schema_collections SchemaCollections
        inner join sys.parameter_xml_schema_collection_usages ParUsage
          on ParUsage.xml_collection_id
          = SchemaCollections.xml_collection_id
      ) XmlSchemaUsage
      inner join sys.all_objects
        on sys.all_objects.[object_id] = XmlSchemaUsage.ObjectId
      inner join sys.syscolumns
        on (sys.syscolumns.[id] = XmlSchemaUsage.ObjectId)
         and (sys.syscolumns.colid = XmlSchemaUsage.ColumnParameterId)
      inner join sys.xml_schema_collections XmlSchemas
        on XmlSchemas.xml_collection_id
        = XmlSchemaUsage.XmlCollectionId
  end
 else
  begin
   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
     insert @XmlSchemaUsage
      (
      XmlSchemaName
      ,XmlSchemaSchema
      ,ObjectName
      ,ObjectSchema
      ,ObjectType
      ,ColumnParameterName
      ,ColumnParameterType
      )
      select @objectName as XmlSchemaName
       ,schema_name(@schemaId) as XmlSchemaSchema
       ,sys.all_objects.[name] as ObjectName
       ,schema_name(sys.all_objects.schema_id) as ObjectSchema
       ,sys.all_objects.type_desc as ObjectType
       ,sys.syscolumns.[name] as ColumnParameterName
       ,UsageType as ColumnParameterType
       from (
        select ColUsage.[object_id] as ObjectId
         ,ColUsage.column_id as ColumnParameterId
         ,ColUsage.xml_collection_id as XmlCollectionId
         ,'COLUMN' as UsageType
         from sys.xml_schema_collections SchemaCollections
          inner join sys.column_xml_schema_collection_usages ColUsage
            on ColUsage.xml_collection_id
            = SchemaCollections.xml_collection_id
         where (SchemaCollections.schema_id = @schemaId)
          and (SchemaCollections.[name] = @objectName)
        union
        select ParUsage.[object_id]
         ,ParUsage.parameter_id
         ,ParUsage.xml_collection_id
         ,'PARAMETER'
         from sys.xml_schema_collections SchemaCollections
          inner join sys.parameter_xml_schema_collection_usages ParUsage
            on ParUsage.xml_collection_id
            = SchemaCollections.xml_collection_id
         where (SchemaCollections.schema_id = @schemaId)
          and (SchemaCollections.[name] = @objectName)
        ) XmlSchemaUsage
        inner join sys.all_objects
          on sys.all_objects.[object_id] = XmlSchemaUsage.ObjectId
        inner join sys.syscolumns
          on (sys.syscolumns.[id] = XmlSchemaUsage.ObjectId)
           and (sys.syscolumns.colid = XmlSchemaUsage.ColumnParameterId)
    end
  end

 return
end
go

ML