Getting Table Columns in SQL Server

Written by belle
Posted March 14, 2009 at 1:03 am

Few ways to get table columns in SQL Server:

Using sp_help

   1: EXEC sp_help 'SalesLT.Customer'

Using INFORMATION_SCHEMA

   1: SELECT

   2:     *

   3: FROM

   4:     INFORMATION_SCHEMA.COLUMNS

   5: WHERE

   6:     TABLE_CATALOG = 'SalesLT'

   7:     AND TABLE_SCHEMA = 'SalesLT'

   8:     AND TABLE_NAME = 'Customer'

Using sys.columns DMV

   1: SELECT [name]

   2: FROM sys.columns

   3: WHERE OBJECT_NAME(object_id) = 'Customer'

Another userful tip:

Sometimes we need to specify column names in our T-SQL statements (INSERT/UPDATE/DELETE). It is very tedious to try and write them ourselves. Good thing in SSMS, you can drag the columns folder onto the code editor and this will list out all the columns in the table.