现在只能查询[某个]数据库的数据表 ,想要所有数据库的数据表信息, 能实现吗?
以下是我查询[某个]数据库的数据表的代码
SELECT
表名 = CONVERT (VARCHAR(50), d.name)
,表用途=isnull(h.用途,'')
,字段序号=a.colorder
,字段名 = CONVERT (VARCHAR(100), a.name)
,字段类型 = CONVERT (VARCHAR(50), b.name)
,标识=case when COLUMNPROPERTY(a.id,a.name,'IsIdentity')=1 then '√'else '' end
,主键=(case when (SELECT count(*) FROM sysobjects WHERE (name in
(SELECT name FROM sysindexes WHERE (id = a.id) AND (indid in
(SELECT indid FROM sysindexkeys WHERE (id = a.id) AND (colid in
(SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name))))))) AND (xtype = 'PK'))>0
then '√' else '' end)
,占用字节数=a.length
,长度=COLUMNPROPERTY(a.id,a.name,'PRECISION')
,小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0)
,允许空=case when a.isnullable=1 then '√'else '' end
,字段说明 = CONVERT (VARCHAR (50),isnull(g.value,isnull(i.字段说明,isnull(i.字段定义,''))))
,自定义备注=isnull(j.字段说明,'')
FROM dbo.syscolumns a
LEFT JOIN dbo.systypes b ON a.xusertype = b.xusertype
INNER JOIN dbo.sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.name <> 'dtproperties'
LEFT JOIN dbo.syscomments e ON a.cdefault = e.id
LEFT JOIN sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id
LEFT JOIN sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0
left join other.dbo.表说明 h on h.id=d.id
left join other.dbo.表中各列说明 i on i.id=d.id and i.字段名=a.name
left join other.dbo.报表字段说明 j on j.id=d.id and j.字段名=a.name
order by 表名,字段序号