[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
)
)
) 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'
阅读(1046) | 评论(0) | 转发(0) |
