How can I get column names from a table in SQL Server?
You can obtain this information and much, much more by querying the Information Schema views.
This sample query:
SELECT *FROM INFORMATION_SCHEMA.COLUMNSWHERE 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
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.NameFROM sys.columns c JOIN sys.objects o ON o.object_id = c.object_id WHERE o.type = 'U' ORDER BY o.Name, c.NameThis gets all columns from all tables, ordered by table name and then on column name.
select *from INFORMATION_SCHEMA.COLUMNSwhere TABLE_NAME='tableName'This is better than getting from sys.columns because it shows DATA_TYPE directly.