SQL Server 2005 查看列信息

相关Table:
select * from sys.syscolumns
SELECT * from sys.extended_properties
SELECT * from sys.sysobjects


select   so.name As TableName,sc.name As ColumnName,sp.value As Description   from   sys.extended_properties   sp
inner   join   sys.syscolumns   sc
on   sp.major_id   =   sc.id   and   sp.minor_id   =   sc.colid
left   join   sys.sysobjects   so
on   so.id   =   sc.id


SELECT sys.syscolumns.name AS 列名, sys.systypes.name
+(case sys.systypes.name
when 'numeric' then '(' + convert(varchar(10),sys.syscolumns.prec) + ',' + convert(varchar(10),sys.syscolumns.scale) + ')'
when 'int' then ''
when 'datetime' then ''
else '(' + convert(varchar(10),sys.syscolumns.prec) + ')' end) AS 数据类型
FROM sys.syscolumns INNER JOIN
      sys.sysobjects ON sys.syscolumns.id = sys.sysobjects.id INNER JOIN
      sys.systypes ON sys.syscolumns.xtype = sys.systypes.xtype
WHERE (sys.sysobjects.name = N'Table_Name')
AND (sys.systypes.name IN ('int','datetime','decimal','varchar','nvarchar','nchar','char','numeric') )

ORDER BY syscolumns.colid

 

 

 

 

2. 

Declare @TableName Nvarchar(100)
set @TableName = ''

select B.* from (
SELECT sys.syscolumns.name AS 字段代码
FROM sys.syscolumns INNER JOIN
      sys.sysobjects ON sys.syscolumns.id = sys.sysobjects.id

WHERE (sys.sysobjects.name = @TableName)) As A
left join (
SELECT  t1.字段代码,t1.字段名称
,t1.数据类型,(case isnull(t2.主键,'') when '' then '' else 'PK' end) As [KEY], t1.可空,t1.备注 FROM
(SELECT c.name 字段代码,value 字段名称, systypes.name+(case sys.systypes.name
when 'numeric' then '(' + convert(varchar(10),c.prec) + ',' + convert(varchar(10),c.scale) + ')'
when 'decimal' then '(' + convert(varchar(10),c.prec) + ',' + convert(varchar(10),c.scale) + ')'
when 'int' then ''
when 'datetime' then ''
else '(' + convert(varchar(10),c.prec) + ')' end) AS 数据类型,case c.isnullable when 0 then 'No' else 'Yes' end  可空, c.length 长度,value 备注
FROM sys.systypes,sys.sysobjects o,syscolumns c
LEFT JOIN ::fn_listextendedproperty(N'MS_Description', N'user', N'dbo', N'table',@TableName, N'column', default) d
ON objname = c.name COLLATE Chinese_PRC_CI_AS
WHERE  c.xusertype = systypes.xusertype
AND c.id = o.id
AND o.name =@TableName) t1
LEFT JOIN
(SELECT i.name 主键名称,c.name 主键
FROM sys.indexes i,sys.sysindexkeys sd, sys.all_columns c, sys.sysobjects o
WHERE i.index_id = sd.indid
AND i.object_id = o.id
AND i.is_primary_key = 1
AND sd.id = o.id
AND c.object_id = o.id
AND c.column_id = sd.colid
AND o.xtype='u'
AND o.name =@TableName) t2 ON t1.字段代码 = t2.主键) As B on A.字段代码 = b.字段代码