参考如下(字段不全,参考添加,请见谅!): 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 = 你的表名) )
访问 Microsoft SQL Server 元数据的三种方法 http://www.microsoft.com/china/technet/columns/tipsfortechs/wsqs3.asp
SQL ?请使用系统表以及系统存储过程, 具体看联机文档
--查詢表結構--引用:邹建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') )
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 = 你的表名)
)
具体看联机文档
表名=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
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')
)