How can I get column names from a table in SQL Server?
SqlSql ServerSql Server-2008TsqlSql Problem Overview
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)?
Sql Solutions
Solution 1 - Sql
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:
Solution 2 - Sql
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.
Solution 3 - Sql
select *
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='tableName'
This is better than getting from sys.columns because it shows DATA_TYPE directly.
Solution 4 - Sql
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.
Solution 5 - Sql
By using this query you get the answer:
select Column_name
from Information_schema.columns
where Table_name like 'table name'
Solution 6 - Sql
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;
Solution 7 - Sql
SELECT name
FROM sys.columns
WHERE object_id = OBJECT_ID('TABLE_NAME')
TABLE_NAME is your table
Solution 8 - Sql
--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
Solution 9 - Sql
SELECT column_name, data_type, character_maximum_length, table_name,ordinal_position, is_nullable
FROM information_schema.COLUMNS WHERE table_name LIKE 'YOUR_TABLE_NAME'
ORDER BY ordinal_position
Solution 10 - Sql
You can try this.This gives all the column names with their respective data types.
desc <TABLE NAME> ;
Solution 11 - Sql
Just run this command
EXEC sp_columns 'Your Table Name'
Solution 12 - Sql
This SO question is missing the following approach :
-- List down all columns of table 'Logging'
select * from sys.all_columns where object_id = OBJECT_ID('Logging')
Solution 13 - Sql
Summarizing the Answers
I can see many different answers and ways to do this but there is the rub in this and that is the objective.
Yes, the objective. If you want to only know the column names you can use
SELECT * FROM my_table WHERE 1=0
or
SELECT TOP 0 * FROM my_table
But if you want to use those columns somewhere or simply say manipulate them then the quick queries above are not going to be of any use. You need to use
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'Customers'
one more way to know some specific columns where we are in need of some similar columns
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME like N'%[ColumnName]%' and TABLE_NAME = N'[TableName]'
Solution 14 - Sql
It will check whether the given the table is Base Table.
SELECT
T.TABLE_NAME AS 'TABLE NAME',
C.COLUMN_NAME AS 'COLUMN NAME'
FROM INFORMATION_SCHEMA.TABLES T
INNER JOIN INFORMATION_SCHEMA.COLUMNS C ON T.TABLE_NAME=C.TABLE_NAME
WHERE T.TABLE_TYPE='BASE TABLE'
AND T.TABLE_NAME LIKE 'Your Table Name'
Solution 15 - Sql
you can use this query
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME like N'%[ColumnName]%' and TABLE_NAME = N'[TableName]'
Solution 16 - Sql
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
Solution 17 - Sql
You can try using :-
USE db_name;
DESCRIBE table_name;
it'll give you column names with the type.
Solution 18 - Sql
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 SELECT *.
Solution 19 - Sql
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'name_of_your_table'
Solution 20 - Sql
SELECT TOP (0) [toID]
,[sourceID]
,[name]
,[address]
FROM [ReportDatabase].[Ticket].[To]
Simple and doesnt require any sys tables
Solution 21 - Sql
Some SQL Generating SQL:
DROP TABLE IF EXISTS test;
CREATE TABLE test (
col001 INTEGER
, col002 INTEGER
, col003 INTEGER
, col004 INTEGER
, col005 INTEGER
, col006 INTEGER
, col007 INTEGER
, col008 INTEGER
, col009 INTEGER
, col010 INTEGER
)
;
INSERT INTO test(col001) VALUES(1);
INSERT INTO test(col002) VALUES(1);
INSERT INTO test(col005) VALUES(1);
INSERT INTO test(col009) VALUES(1);
INSERT INTO test VALUES (NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
SELECT
CASE ROW_NUMBER() OVER(ORDER BY ordinal_position)
WHEN 1 THEN
'SELECT'+CHAR(10)+' *'+CHAR(10)+'FROM test'
+CHAR(10)+'WHERE '
ELSE
' OR '
END
+ column_name +' IS NOT NULL'
+ CASE ROW_NUMBER() OVER(ORDER BY ordinal_position DESC)
WHEN 1 THEN
CHAR(10)+';'
ELSE
''
END
FROM information_schema.columns
WHERE table_schema='dbo'
AND table_name = 'test'
ORDER BY
ordinal_position;
-- the whole scenario. Works for 10 , will work for 100, too:
-- out -----------------------------------------------
-- out SELECT
-- out *
-- out FROM test
-- out WHERE col001 IS NOT NULL
-- out OR col002 IS NOT NULL
-- out OR col003 IS NOT NULL
-- out OR col004 IS NOT NULL
-- out OR col005 IS NOT NULL
-- out OR col006 IS NOT NULL
-- out OR col007 IS NOT NULL
-- out OR col008 IS NOT NULL
-- out OR col009 IS NOT NULL
-- out OR col010 IS NOT NULL
-- out ;
Solution 22 - Sql
Simple and doesn't require sys variables:
SHOW COLUMNS FROM suppliers;
Solution 23 - Sql
try using : "desc Table_name" only thing is it will give other details as well like ,Is Null ,Type and constraints