How do you return the column names of a table?

问题:

How would I return the column names of a table using SQL Server 2008? i.e. a table contains these columns- id, name, address, country and I want to return these as data.

回答1:

Not sure if there is an easier way in 2008 version.

USE [Database Name]
SELECT COLUMN_NAME,* 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTableName' AND TABLE_SCHEMA='YourSchemaName'

回答2:

This is the easiest way

exec sp_columns [tablename]

回答3:

Something like this?

sp_columns @table_name=your table name

回答4:

One method is to query syscolumns:

select
   syscolumns.name as [Column],
   syscolumns.xusertype as [Type],
   sysobjects.xtype as [Objtype]
from 
   sysobjects 
inner join 
   syscolumns on sysobjects.id = syscolumns.id
where sysobjects.xtype = 'u'
and   sysobjects.name = 'MyTableName'
order by syscolumns.name

回答5:

This seems a bit easier then the above suggestions because it uses the OBJECT_ID() function to locate the table's id. Any column with that id is part of the table.

SELECT * 
  FROM syscolumns 
 WHERE id=OBJECT_ID('YOUR_TABLE') 

I commonly use a similar query to see if a column I know is part of a newer version is present. It is the same query with the addition of {AND name='YOUR_COLUMN'} to the where clause.

IF EXISTS (
        SELECT * 
          FROM syscolumns 
         WHERE id=OBJECT_ID('YOUR_TABLE') 
           AND name='YOUR_COLUMN'
        )
BEGIN
    PRINT 'Column found'
END

回答6:

try this

select * from <tablename> where 1=2

...............................................

回答7:

I use

SELECT st.NAME, sc.NAME, sc.system_type_id
FROM sys.tables st
INNER JOIN sys.columns sc ON st.object_id = sc.object_id
WHERE st.name LIKE '%Tablename%'

回答8:

The following seems to be like the first suggested query above but sometime you have to specify the database to get it to work. Note that the query should also work without specifying the TABLE_SCHEMA:

SELECT COLUMN_NAME
FROM   YOUR_DB_NAME.INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_NAME = 'YOUR_TABLE_NAME' AND TABLE_SCHEMA = 'YOUR_DB_NAME'

回答9:

USE[Database] SELECT TABLE_NAME,TABLE_SCHEMA,[Column_Name],[Data_type] FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='dbo'

回答10:

Why not just try this:

right click on the table -> Script Table As -> Create To -> New Query Editor Window?

The entire list of columns are given in the script. Copy it and use the fields as necessary.

回答11:

CREATE PROCEDURE [dbo].[Usp_GetColumnName]      
        @TableName varchar(50)
AS
BEGIN   
    BEGIN
        SET NOCOUNT ON
        IF (@TableName IS NOT NULL) 
            select ORDINAL_POSITION OrderPosition,COLUMN_NAME ColumnName from information_schema.columns 
             where table_name =@TableName
             order by ORDINAL_POSITION
    END
END

回答12:

I'm not sure if the syscolumns.colid value is the same as the 'ORDINAL_POSITION' value returned as part of sp_columns, but in what follows I am using it that way - hope I'm not misinforming...

Here's a slight variation on some of the other answers I've found - I use this because the 'position' or order of the column in the table is important in my application - I basically need to know 'What is column (n) called?'

sp_columns returns a whole bunch of extraneous stuff, and I'm handier with a select than T-SQL functions, so I went this route:

select    
  syscolumns.name, 
  syscolumns.colid    
from     
  sysobjects, syscolumns  
where 
  sysobjects.id = syscolumns.id and   
  sysobjects.xtype = 'u' and   
  sysobjects.name = '<YOUR_TABLE>' 
order by syscolumns.colid 

回答13:

You can use the below code to print all column names; You can also modify the code to print other details in whichever format u like

    declare @Result varchar(max)='
            '
            select @Result=@Result+''+ColumnName+'
            '
            from
            (
                select
                    replace(col.name, ' ', '_') ColumnName,
                    column_id ColumnId
                from sys.columns col
                    join sys.types typ on
                        col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
                where object_id = object_id('tblPracticeTestSections')
            ) t
            order by ColumnId
            print @Result

Output

column1
column2
column3
column4

To use the same code to print the table and its column name as C# class use the below code:

    declare @TableName sysname = '<EnterTableName>'
    declare @Result varchar(max) = 'public class ' + @TableName + '
    {'

    select @Result = @Result + '
        public static string ' + ColumnName + ' { get { return "'+ColumnName+'"; } }
    '
    from
    (
        select
            replace(col.name, ' ', '_') ColumnName,
            column_id ColumnId
        from sys.columns col
            join sys.types typ on
                col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
        where object_id = object_id(@TableName)
    ) t
    order by ColumnId

    set @Result = @Result  + '
    }'

    print @Result

Output:

 public class tblPracticeTestSections
 {
   public static string column1 { get { return "column1"; } }

   public static string column2{ get { return "column2"; } }

   public static string column3{ get { return "column3"; } }

   public static string column4{ get { return "column4"; } }

 } 

回答14:

I just use a query like Martin Smith mentioned, just little shorter:

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTableName'

回答15:

While @Gulzar Nazim's answer is great, it is probably easier to include the database name in the query, which could be achieved by the following SQL.

SELECT COLUMN_NAME, *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'you-table-name' AND TABLE_CATALOG='your-database-name'

回答16:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'TableName'

回答17:

Since SysColumns is deprecated, use Sys.All_Columns:

Select  
 ObjectName       = Object_Name(Object_ID)  
,T.Name  
,C.*  
,T.*  
From   
           Sys.All_Columns C  
Inner Join Sys.Types       T  On T.User_Type_Id = C.User_Type_Id  
Where [Object_ID] = Object_ID('Sys.Server_Permissions')  
--Order By Name Asc  

Select * From Sys.Types will yield user_type_id = ID of the type. This is unique within the database. For system data types: user_type_id = system_type_id.

回答18:

DECLARE @col NVARCHAR(MAX);
SELECT @col= COALESCE(@col, '') + ',' + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS WHERE  Table_name = 'MxLocations';
SELECT @col;

回答19:

set fmtonly on
select * from yourTable