use xin20170628  --指定数据库
go
--只能查询出类型，主键和顺序
select SO.name as TableName,SC.name as TableCloumn,ST.name as DataType,
(
SELECT COUNT(1) AS Is_PK 
 FROM  syscolumns  
 JOIN  sysindexkeys  ON   syscolumns.id=sysindexkeys.id  AND  syscolumns.colid=sysindexkeys.colid   
 JOIN  sysindexes   ON   syscolumns.id=sysindexes.id  AND  sysindexkeys.indid=sysindexes.indid  
 JOIN  sysobjects   ON   sysindexes.name=sysobjects.name  AND  sysobjects.xtype=‘PK‘
 WHERE syscolumns.name=SC.name AND syscolumns.id=object_id(SO.name)
) as IsPrimaryKey
,SC.colid as CloumnIndex 
from sysobjects SO 
inner join syscolumns SC on SO.id = SC.id  and  SO.xtype = ‘U‘ and  SO.status >= 0 and SO.name= ‘表名‘
inner join systypes ST on SC.xtype = ST.xusertype 
order by SO.name asc, SC.colorder asc


------------------------------------------------------------------------
go
--查询出字段类型和长度
select o.name as 表名, c.name as 字段名称, t.name as 字段类型, c.length as 字段长度 
from 
syscolumns c inner join 
sysobjects o on c.id = o.id and o.xtype = ‘u‘ inner join 
systypes t on c.xtype = t.xtype
where o.name = ‘表名‘   --查询的表名
---------------------------------------------------------------------------------------
--查询表的字段类型，没有长度
select column_name,data_type from information_schema.columns 
where table_name = ‘表名‘
----------------------------------------------------------------------------------------
--列出所有表： 

SELECT (CASE WHEN a.colorder=1 THEN d.name ELSE ‘‘ END)表名
,a.colorder 字段序号
,a.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) 主键,b.name 类型,a.length 占用字节数
,COLUMNPROPERTY(a.id ,a.name ,‘PRECISION‘) AS 长度,ISNULL(COLUMNPROPERTY(a.id ,a.name ,‘Scale‘) ,0) AS 小数位数
,(CASE WHEN a.isnullable=1 THEN ‘√‘ ELSE ‘‘ END) 允许空,ISNULL(e.text ,‘‘) 默认值 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
ORDER BY a.id ,a.colorder