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.

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'

This is the easiest way

exec sp_columns [tablename]

Something like this?

sp_columns @table_name=your table name

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

try this

select * from <tablename> where 1=2

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

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

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'

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%'

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.

USE [Database]

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

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 

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

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'

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"; } }

 } 

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

IF you are working with postgresql there is a possibility that more than one schema may have table with same name in that case apply the below query

SELECT column_name, data_type 
FROM information_schema.columns
WHERE table_name = 'your_table_name' AND table_schema = 'your_schema_name’;

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

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

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

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.

set fmtonly on
select * from yourTable