--获取所有数据库名
SELECT Name FROM Master..SysDatabases ORDER BY Name
--获取所有表名 XType='U':表示所有用户表; XType='S':表示所有系统表;
SELECT Name FROM [GWI-Pay]..SysObjects Where XType='U' ORDER BY Name
--获取表中的字段名
SELECT Name FROM SysColumns WHERE id=Object_Id('Order')
--获取数库所有字段类型
select name from systypes
--获取主键字段
SELECT name FROM SysColumns WHERE id=Object_Id('Order') and colid=(select top 1 keyno from sysindexkeys where id=Object_Id('Order'))
--获取字段类型
select a.name as [column],b.name as type from syscolumns a,systypes b where a.id=object_id('order') and a.xtype=b.xtype
--获取表结构 字段名 类型 长度
select column_name,data_type,character_maximum_length from information_schema.columns where table_name = 'order'
--查询表的整体架构
SELECT
d.name as TableName,--表名
a.colorder AS FieldOrder,-- 字段序号
a.name as FieldName,
(case when COLUMNPROPERTY( a.id,a.name, 'IsIdentity' )=1 then 'Y' else 'N' end) AS IsIdentity,-- 标识
(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 'Y' else 'N' end) as IsPrimaryKey,--查询主键END
b.name as FieldType, --类型
a.length as ByteLength,--字节数
COLUMNPROPERTY(a.id,a.name,'PRECISION' ) as FieldLength,
isnull(COLUMNPROPERTY(a.id,a.name,'Scale' ),0) as RadixPoint,--小数位数,
(case when a.isnullable=1 then 'Y' else 'N' end) AS IsNullable,-- 是否允许空,
isnull(e.text,'' ) as DefaultValue,--默认值
isnull(g.[value],'' ) AS FieldDesc -- 字段说明
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='Code' --所要查询的表
order by a.id,a.colorder
