SQL--如何获得一个数据表的所有列名

wnYVhPnN

方法如下：先从SYSTEMOBJECT系统表中取得数据表的SYSTEMID,然后再SYSCOLUMN表中取得该数据表的所有列名。

SQL语句如下：
declare @objid int,@objname char(40)
set @objname = 'tablename'
select @objid = id from sysobjects where id = object_id(@objname)
select 'Column_name' = name from syscolumns where id = @objid order by colid

自己修改如下，可以直接放到JSP中执行
SELECT name
FROM syscolumns
WHERE (id =
(SELECT id
FROM sysobjects
WHERE (id = OBJECT_ID('YOURTABLENAME'))))
ORDER BY colid

twZiJevN

是原創么﹖我仰望著天﹐只因為低頭看不見路




好書是頂起來的﹐希望大家多多頂我﹐:)

tbjrilh

也可以这么写
select *
from information_schema.columns

ImJKWDIu

好方法，谢谢

hipoking

不错！！！！！

wnYVhPnN

给你帖个详细的，表名自己过滤

SELECT TOP 100 PERCENT dbo.sysobjects.name AS TableName,
      dbo.syscolumns.colid AS FieldIndex, dbo.syscolumns.name AS FieldName,
      dbo.sysproperties.[value] AS Describe, dbo.systypes.name AS FieldType,
      dbo.syscolumns.length AS FieldSize, dbo.syscolumns.isnullable AS AllowNull
FROM dbo.syscolumns INNER JOIN
      dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype INNER JOIN
      dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id LEFT OUTER JOIN
      dbo.sysproperties ON dbo.syscolumns.id = dbo.sysproperties.id AND
      dbo.syscolumns.colid = dbo.sysproperties.smallid
WHERE (dbo.sysobjects.xtype = 'U') AND (dbo.sysobjects.name <> 'dtproperties')
ORDER BY dbo.sysobjects.name, dbo.syscolumns.colid

auxeie

超级感谢 正在需要呢