1: -- this lists the table catalog, table name, 

   2: -- column name, and data type

   3: SELECT 

   4:     TABLE_CATALOG,

   5:     TABLE_NAME,

   6:     COLUMN_NAME, 

   7:     DATA_TYPE 

   8: FROM 

   9:     INFORMATION_SCHEMA.COLUMNS

  10: WHERE 

  11:     DATA_TYPE = 'xml'

  12:  

   1: -- this lists the corresponding schemas

   2: SELECT 

   3:     DISTINCT

   4:     OBJECT_NAME(sys.columns.object_id)        AS 'TableName',

   5:     sys.columns.name                    AS 'ColName',

   6:     sys.xml_schema_collections.name            AS 'Schema' 

   7: FROM 

   8:     sys.columns

   9:     LEFT JOIN     sys.xml_schema_collections 

  10:     ON sys.columns.xml_collection_id = sys.xml_schema_collections.xml_collection_id

  11: ORDER BY 

  12:     OBJECT_NAME(sys.columns.object_id), sys.columns.name    

  13: