|M| 求一条SQL查询语句,表的各种信息
查出来信息如下
字段名     数据类型     数据长度     是否允许为空     描述     默认值     精度     小数位     标识     标识种子   标识递增量   是ROWGUID     公式     排序规则

谢谢

------解决方案--------------------
参考如下(字段不全,参考添加,请见谅!):
SELECT
dbo.syscolumns.name as 字段名称,
dbo.systypes.name as 类型,
dbo.syscolumns.length 长度,
case when syscolumns.isnullable=0 then "× " when syscolumns.isnullable=1 then "√ " end as [允许为空？]
FROM dbo.syscolumns inner join dbo.systypes
on dbo.systypes.xtype =dbo.syscolumns.xtype
WHERE (id =
(SELECT [id]
FROM [ERP].[dbo].[sysobjects]
WHERE name = 你的表名)
)
------解决方案--------------------
sp_help 表名
------解决方案--------------------
访问 Microsoft SQL Server 元数据的三种方法 http://www.microsoft.com/china/technet/columns/tipsfortechs/wsqs3.asp
------解决方案--------------------
--查詢表結構

--引用：邹建

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 exists(Select 1 From sysobjects where Xtype= 'PK ' And Name In (
Select Name From sysindexes Where indid In(
Select indid From sysindexkeys Where ID = A.ID AND colid=A.colid
))) Then '√ ' Else ' ' End,
类型=B.Name,
占用字节数=A.length,
长度=COLUMNPROPERTY(A.ID,A.Name, 'PRECISION '),
小数位数=IsNull(COLUMNPROPERTY(A.ID,A.Name, 'Scale '),0),
允许空=Case When A.isnullable=1 Then '√ 'Else ' ' End,
默认值=IsNull(E.text, ' '),
字段说明=IsNull(G.[value], ' ')
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
Left Join sysproperties G On A.ID=G.ID And A.colid=G.smallid
Where D.Name= 'YouTableName ' --更改為你的表名
Order By A.ID,A.ColOrder


------解决方案--------------------
SELECT
dbo.syscolumns.name as 字段名称,
dbo.systypes.name as 类型,
dbo.syscolumns.length 长度,
case when syscolumns.isnullable=0 then '× ' when syscolumns.isnullable=1 then '√ ' end as [允许为空？]
FROM dbo.syscolumns inner join dbo.systypes
on dbo.systypes.xtype =dbo.syscolumns.xtype
WHERE (id =
(SELECT [id]
FROM [dbo].[sysobjects]
WHERE name = 'authors ')
)

------解决方案--------------------
2楼ERPCoder(Most Valuable Player) 说的对啊
------解决方案--------------------
feiyu0805(飞雨) ( ) 同上
------解决方案--------------------
系统表里面都有.慢慢看看吧!
------解决方案--------------------
UP