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)?
+1
A:
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
marc_s
2009-04-16 08:20:28
The field is a varchar. Thats what makes things a bit difficult
Lonzo
2009-04-16 13:54:15
+2
A:
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.
tpdi
2009-04-16 08:20:39
Good sultion. Couple of remarks: you'll need '+' instead of '||' in SQL. Also the join should read 'from sys.tables tjoin sys.columns c on (t.object_id = c.object_id)'
edosoft
2009-04-16 08:40:00
If you need to be SQL-92 compliant, use INFORMATION_SCHEMA instead of the systable or sys.tables catalog views (those are SQL Server specific)
marc_s
2009-04-16 13:15:38