数据库，表，字段，类型等查询

--获取所有数据库名
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