/*查询所有数据库中的所有表的所有字段的信息*/
/*
标题:查询所有数据库中的所有表的所有字段的信息(version 0.9)
作者:InternetRoot 网络根结
时间:2008-03-21
地点:江苏南通
说明:通过赋值语句批量生成查找单个数据库中表信息的查询语句,然后用UNION将多查询并接。version 0.9可在sql server 2000和sql server 2005中运行。
*/
declare
@s varchar(8000)
set @s=''
select @s=' '+@s+
( select 'select '''+name+''' as dbname,a.name as tablename,b.name as colname,c.name as coltype,c.length as coltype from ['+
name+']..sysobjects a inner join ['+
name+']..syscolumns b on a.id=b.id inner join ['+
name+']..systypes c on b.type=b.type where a.type=''U''and c.xtype <>''36'''
as sql
from master..sysdatabases as s where s.name=d.name)+' union '
from master..sysdatabases as d
set @s=left(@s,len(@s)-6)
execute(@s)