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

nora devids 0 Reputation points
2023-11-04T06:59:47.2166667+00:00

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

Azure Database for MySQL
Azure Database for MySQL
An Azure managed MySQL database service for app development and deployment.
740 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Boris Von Dahle 3,121 Reputation points
    2023-11-04T08:11:53.0966667+00:00

    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

    0 comments No comments