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

  1. USE MyDB  
  2. GO  
  3. SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'Student'  
  4. GO  
  5. EXEC sp_help 'Student'  
  6. GO  
  7. select * from sys.all_columns where object_id = OBJECT_ID('Student')  
  8. GO 

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

Ebook Download
View all
Learn
View all
Think. Innovate. Grow.