Question: How to get column names from a specific table in SQL Server?
Answer: This is a very popular question and there are multiple ways to get column names of a specific table in SQL Server. Let us see various methods. You can choose the method which you prefer to use for your solutions.
Method 1: Using INFORMATION_SCHEMA.COLUMNS
SELECT * FROM INFORMATION_SCHEMA.COLUMNS --WHERE TABLE_NAME = N'YourTableName'
Method 2: Using Sys Schema (My Preferred Way)
SELECT OBJECT_SCHEMA_NAME (c.object_id) SchemaName,
o.Name AS Table_Name,
c.Name AS Field_Name,
t.Name AS Data_Type,
t.max_length AS Length_Size,
t.precision AS Precision
FROM sys.columns c
INNER JOIN sys.objects o ON o.object_id = c.object_id
LEFT JOIN sys.types t on t.user_type_id = c.user_type_id
WHERE o.type = 'U'
-- and o.Name = 'YourTableName'
ORDER BY o.Name, c.NameWhen you run above script, it will give you the name of the schema along with tablename. I prefer this way over any other way at this point of time.
Here is the screenshot of the result set of the method 2. You can see it displays all the necessary information along with schema and tablename.
Let me know if you use any other script by leaving the script in the comments area.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
17 Comments. Leave new
I just select the name of the table and press Alt + F1, which gives all the columns, indexes etc.
I just select the table name and press Alt + F1, which lists out the table name, indexes, etc.
Are you daft? this is for when you need to do it programmatically
How to get columns for temporary table?
Refer this post https://alveenajoyce.wordpress.com/2019/11/01/methods-to-find-table-structure-in-sql/
Is it at all possible to find when a Column was added to an Existing Table (datetime of the new column added and not the Table in whole).
I am using sp_columns as it is shorter to write :)
SELECT *
FROM sys.dm_exec_describe_first_result_set( ‘SELECT * FROM schema.table, NULL, 1 )
If the goal is just to get the column names, a quick way is just to select and return an empty set.
select * from dbo.tablename where 0=1
However, if you want or need additional details, then the examples given would be a better methodology.
select * from sys.columns a inner join sys.tables b on a.object_id=b.object_id where b.name =’UserTableName’
select SC.* from sys.columns SC join sys.tables ST on SC.Object_ID = ST.Object_ID
where ST.Name =
select SC.* from sys.columns SC join sys.tables ST on SC.Object_ID = ST.Object_ID
where ST.Name = ‘table_name’
Here are two other simple methods to do the same
https://madhivanan.wordpress.com/2017/07/03/how-to-get-column-names-from-a-specific-table/
Is there a way to do this for all views?
same script including indexes and keys information, is there any way to add in the same script, Please help
Hey Pinal,
I really enjoy reading through your Tips and Tricks. Really neat.
By the way, I believe the Method 2 has a slight error. The 5th column “Length_Size” is marked as “t.max_length AS Length_Size”. I believe you meant that to be “c.max_length AS Length_Size”.
Refer this post to know different methods to know structure of the table https://alveenajoyce.wordpress.com/2019/11/01/methods-to-find-table-structure-in-sql/