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

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