How can I get column names from a table in SQL Server?

Question

I want to query the name of all columns of a table. I found how to do this in:

• Oracle
• MySQL
• PostgreSQL

But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Answer 1

Hello,

To get column names from a table in SQL Server, you have a few options:

Use the INFORMATION_SCHEMA.COLUMNS view:

 SELECT COLUMN_NAME
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_NAME = N'yourTableName'

This query retrieves the column names from the INFORMATION_SCHEMA.COLUMNS view where the TABLE_NAME matches your table name. The N before the table name indicates a Unicode string.

Use the sys.columns system view:

 SELECT name
   FROM sys.columns
   WHERE object_id = OBJECT_ID('dbo.yourTableName')

This query retrieves the column names from the sys.columns view where the object_id matches the object ID of your table.

Use the sp_columns stored procedure:

  EXEC sp_columns 'yourTableName'

This stored procedure returns information about all columns for a given table.

Keep in mind that the first two methods return only the column names, while the third method returns additional information about the columns, such as data types, nullability, and more.

Hope this helps. If it does please mark the question as accepted so otherusers with same question can fin this topic

Regards