How can I get column names from a table in SQL Server?
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)?
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:
- CHECK_CONSTRAINTS
- COLUMN_DOMAIN_USAGE
- COLUMN_PRIVILEGES
- COLUMNS
- CONSTRAINT_COLUMN_USAGE
- CONSTRAINT_TABLE_USAGE
- DOMAIN_CONSTRAINTS
- DOMAINS
- KEY_COLUMN_USAGE
- PARAMETERS
- REFERENTIAL_CONSTRAINTS
- ROUTINES
- ROUTINE_COLUMNS
- SCHEMATA
- TABLE_CONSTRAINTS
- TABLE_PRIVILEGES
- TABLES
- VIEW_COLUMN_USAGE
- VIEW_TABLE_USAGE
- VIEWS
Read more... Read less...
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.
select *
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='tableName'
This is better than getting from sys.columns because it shows DATA_TYPE directly.
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.
By using this query you get the answer:
select Column_name
from Information_schema.columns
where Table_name like 'table name'
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;