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