SQL Server – How to get all Column names of a table

  • by
Post Views: 5,783

To get Column names of a table in SQL server use query below query:

Select COLUMN_NAME,DATA_TYPE from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='Your_Table_Name'

For example: we will consider NorthWind database for querying our SQL column names

I want to select all the columns from Categories table, so the query will be:

Select COLUMN_NAME,DATA_TYPE from INFORMATION_SCHEMA.COLUMNS 
where TABLE_NAME='Categories'

sql-get-all-columns-2

If you want to get all other Column name properties like  IS_NULLABLE, MAX_LENGTH, DATA_TYPE, COLLATION_NAME use below query using INFORMATION_SCHEMA.COLUMNS

Select * from INFORMATION_SCHEMA.COLUMNS 
where TABLE_NAME='Your_Table_Name'

NorthWind SQL Db:

Select * from INFORMATION_SCHEMA.COLUMNS 
where TABLE_NAME='Categories'

sql-get-all-columns-3

To get Column names as per their order in the table you can use Ordinal_Position. Ordinal_Position in this query will give us the column position.

Select * from INFORMATION_SCHEMA.COLUMNS 
where TABLE_NAME='Your_Table_Name' order by ORDINAL_POSITION asc

To use this query on Categories table:

Select * from INFORMATION_SCHEMA.COLUMNS 
where TABLE_NAME='Categories' order by ORDINAL_POSITION asc

 

Using SQL Stored_Procedures to get column names

We also use in-build Stored procedure to get column names of the particular table.

Using Stored procedures:

exec sp_columns 'table_name'

Example:

exec sp_columns 'Categories'

sql_sp_get_columns_table_name

 

 

 


Tags: