The following query can be run (substitute the ‘TableName’ in the query with the table name you want column information on) to get the column names for any table in T-SQL.
|
1 2 3 |
select column_name from information_schema.columns where table_name = 'TableName' order by ordinal_position |
|
1 |
To get information on all the columns for all tables in a database use the following query:
|
1 2 |
SELECT Table_Schema, Table_Name, Column_Name, Data_Type FROM information_schema.columns |
SQL Server also provides a stored procedure (‘sp_help’) which is able to display information on any object listed in sysobjects. To use this to get information on a table execute the following:
|
1 |
EXEC sp_help 'tablename' |
About Carlos Ferreira
This is so helpful!!!
Thanks!
I’m trying to get all the columns from a table which don’t have any record. However unable to display column names.
I’m doing this in w3school website.
Hi – It won’t work on w3schools site as it is not using SQL Server. The information tables are specific to SQL Server.
To test I would suggest using sqlfiddle.com. Change the database engine to MS SQL Server 2014.
Paste the following in the Schema side of the interface:
CREATE TABLE Employee(StartDate DATE,
Name VARCHAR(20)
);
Click onto Build Schema button.
Then type the following in the SQL side of the page:
select column_name from information_schema.columnswhere table_name = 'Employee'
order by ordinal_position
And then press the Run SQL button.
You will see the results underneath.
Hope this helps.