sql server系统表的查询操作

--列出所有的用户数据表及其字段信息
SELECT TOP 100 PERCENT c.colid AS 序号,o.name AS 表名,c.name AS 列名,
      t.name AS 类型,c.length AS 长度,c.isnullable AS 允许空,
      CAST(m.[value] AS Varchar(100)) AS 说明
FROM  syscolumns c INNER JOIN
      sysobjects o ON o.id = c.id AND objectproperty(o.id, N'IsUserTable') = 1 AND
      o.name <> 'dtproperties' INNER JOIN
      systypes t ON t.xusertype = c.xusertype LEFT OUTER JOIN
      sysproperties m ON m.id = o.id AND m.smallid = c.colorder
ORDER BY o.name,c.colid

--查找存在某些字段的用户表和视图
select name,case when xtype='u' then 'usertable' else 'userview' end as xtype
from sysobjects
where id in (
select id from syscolumns where name in('商品编号','商品名称')
) and (xtype='u' or xtype='v')
order by xtype

--获取指定表或视图中所有的列
Select c.name As ColumnName,t.name As TypeName
From syscolumns c, systypes t, sysobjects o
Where c.xtype = t.xusertype And c.id = o.id And o.name = 'object_name'
Order By c.colorder

--获取指定表或视图中所有列的详细信息
Select ColOrder = col.colorder, --排序号
       ColumnName = col.name,   --列名
       TypeName = type.name,    --数据类型名称
Length =  (Case When type.name='nvarchar' Or type.name='nchar' Then col.length/2 Else col.length End), --长度
[PRECISION] = COLUMNPROPERTY(col.id, col.name, 'PRECISION'),   --精度
Scale = ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0),  --小数
IsIdentity = Case When COLUMNPROPERTY(col.id, col.name, 'IsIdentity')=1 Then '√' Else '' End, --是否为自动编号列
IsPK = Case When Exists(Select 1 From sysobjects Where xtype = 'PK' And name In (
        Select name From sysindexes Where indid In (
            Select indid From sysindexkeys Where id = col.id And colid = col.colid
            ) --// www.jb200.com
        )
    ) Then '√' Else '' End,  --是否为主键
AllowNull = Case When col.isnullable=1 Then '√' Else '' End, --是否允许为空
DefalutValue = isnull(com.text, '') --默认值
From syscolumns col
Left Join systypes type On col.xtype = type.xusertype
Inner Join sysobjects obj On col.id = obj.id And (obj.xtype = 'U' Or obj.xtype = 'V') And obj.name <> 'dtproperties'
Left Join syscomments com On col.cdefault = com.id
Where obj.name = 'object_name'