SQL Server – How to Get Column Names From a Specific Table?

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.Name

When 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)