Find all tables with a field containing xml string

Question

I have an SQL 2005 database and I know that in the database there is a table which has got some xml strings in it. How can I find this table(s)?

Answer 1

If the fields are actually of type XML, then this query will give you what you're looking for:

select * from information_schema.columns
where DATA_TYPE = 'XML'

Marc

Answer 2

Run this:

select 'select distinct ''' || a.name || '.' || b.name 
|| '''  from ' || b.name 
|| 'where ' || b.name || ' like ''%<%/>%'' union ' 
from systable a 
join syscolumns b on (a.id = b.id)
join systypes c on (b.type = c.xtype) 
where a.type ='U' and c.name = ('CHAR', 'CHARN', 'VARCHAR', 'VARCHARN');

The first result set will have one row per character column in the database:

select distinct 'table.column' from table where column like '%<%/>%' union

Take that resultset, snip off the last union, and run the resultset as a SQL statement. It'll bring back the table name and column name for any column that has one or more rows that look XML-ish.

Edit: this is from memory; the join to systypes and the type names may be wrong, so select from systypes and check.