SQL Script To List All Columns of a Table in SQL Server

Friends,

In my last post, I explained how can you list all tables from a specific table in a database. In this post, we will see different ways of retrieving all columns from a specific table in a database. It is a very simple script to get all columns for a table from a database. We can do it using following 3 ways –

• Using INFORMATION_SCHEMA.COLUMNS table
  
• Using sp_help Stored Procedure
• Using sys.all_columns table

Let us see how can we get the column names using each of them. Lets assume our table name is “Student”.

USE MyDB
GO
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'Student'
GO
EXEC sp_help 'Student'
GO
select * from sys.all_columns where object_id = OBJECT_ID('Student')
GO

Hope you like this post! Keep Learning and sharing! Cheers!