Advertisement
Advertisement


How can I get column names from a table in SQL Server?


Question

I want to query the name of all columns of a table. I found how to do this in:

But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

2020/08/12
1
740
8/12/2020 6:38:14 PM

Accepted Answer

You can obtain this information and much, much more by querying the Information Schema views.

This sample query:

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

Can be made over all these DB objects:

2019/06/18
895
6/18/2019 9:23:26 PM

You can use the stored procedure sp_columns which would return information pertaining to all columns for a given table. More info can be found here http://msdn.microsoft.com/en-us/library/ms176077.aspx

You can also do it by a SQL query. Some thing like this should help:

SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('dbo.yourTableName') 

Or a variation would be:

SELECT   o.Name, c.Name
FROM     sys.columns c 
         JOIN sys.objects o ON o.object_id = c.object_id 
WHERE    o.type = 'U' 
ORDER BY o.Name, c.Name

This gets all columns from all tables, ordered by table name and then on column name.

2019/06/15

select *
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='tableName'

This is better than getting from sys.columns because it shows DATA_TYPE directly.

2016/06/17

You can use sp_help in SQL Server 2008.

sp_help <table_name>;

Keyboard shortcut for the above command: select table name (i.e highlight it) and press ALT+F1.

2019/06/15

By using this query you get the answer:

select Column_name 
from Information_schema.columns 
where Table_name like 'table name'
2014/03/26

You can write this query to get column name and all details without using INFORMATION_SCHEMA in MySql :

SHOW COLUMNS FROM database_Name.table_name;
2016/09/19

Source: https://stackoverflow.com/questions/1054984
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]