tables - sql query to get column names from table
How can I get column names from a table in SQL Server? (12)
I would like to query the name of all columns of a table. I found how to do this in:
But I need to know: how can this be done in Microsoft SQL Server (2008 in my case)?
--This is another variation used to document a large database for conversion (Edited to --remove static columns)
SELECT o.Name as Table_Name , c.Name as Field_Name , t.Name as Data_Type , t.length as Length_Size , t.prec as Precision_ FROM syscolumns c INNER JOIN sysobjects o ON o.id = c.id LEFT JOIN systypes t on t.xtype = c.xtype WHERE o.type = 'U' ORDER BY o.Name, c.Name
--In the left join, c.type is replaced by c.xtype to get varchar types
By using this query you get the answer:
select Column_name from Information_schema.columns where Table_name like 'table name'
Just run this command
EXEC sp_columns 'Your Table Name'
One other option which is arguably more intuitive is:
SELECT [name] FROM sys.columns WHERE object_id = OBJECT_ID('[yourSchemaType].[yourTableName]')
This gives you all your column names in a single column.
If you care about other metadata, you can change edit the SELECT STATEMENT TO
You can obtain this information and much, much more by querying the Information Schema views.
This sample query:
SELECT * FROM Northwind.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'Customers'
Can be made over all these DB objects:
You can try this.This gives all the column names with their respective data types.
desc <TABLE NAME> ;
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.
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;
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'name_of_your_table'
SELECT c.Name FROM sys.columns c JOIN sys.objects o ON o.object_id = c.object_id WHERE o.object_id = OBJECT_ID('TABLE_NAME') ORDER BY c.Name
SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('TABLE_NAME')
// TABLE_NAME is your table
select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='tableName'
This is better than getting from
sys.columns because it shows