mssql读取mysql表_获取MSSQL所有数据库名、所有表名、所有字段名

Select

a.Name as Caption, --列名

(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 1 else 0 end) as IsPrimaryKeyMember, --主键

b.name as DataType, --数据类型

a.Length as [Size], --长度

(case when a.isnullable=1 then 1 else 0 end) as AllowDBNull, --允许空

isnull(e.text,'') as DefaultValue, --默认值

isnull(g.[value],'' ) AS Description --说明

FROM syscolumns a

LEFT JOIN systypes b ON a.xtype=b.xusertype

INNER JOIN sysobjects d ON a.id=d.id and d.xtype='U' and d.name<> 'dtproperties'

LEFT JOIN 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

Where d.name='Person'

orDER BY a.id,a.colorder