查找包含xml字符串值的字段的所有表

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)?

我有一个SQL 2005数据库,我知道在数据库中有一个表中有一些xml字符串。我怎样才能找到这张桌子?

2 个解决方案

#1

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

如果字段实际上是XML类型,那么此查询将为您提供所需内容:

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

Marc

#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

从表中选择不同的'table.column',其中列为'%<%/>%'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.

获取结果集,剪掉最后一个联合,并将结果集作为SQL语句运行。它将为具有一行或多行看起来像XML的列的任何列带回表名和列名。

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

编辑:这是来自记忆;对systypes和类型名称的连接可能是错误的,因此从systypes中选择并检查。

#1