Search code examples
sql-servert-sqldmv

SQL Server sp_help to pull out just the columns information

The SP_HELP procedures produces multiple subsets of data and I would only like to have the columns information from that. Is there a way to maybe write a query using sp_help to just pull out that information.

I need to do this to build a metadata database and maintain it on a weekly basis. Any help is appreciated.

Thanks, RV.

Solution

  • The information you want can be found with:

    select * from sys.columns

    However, it can be difficult to navigate using just that table. I like to query the schema, tables, and columns views for this.

    select
    schemas.name as [schema]
    ,tables.name as [table]
    ,columns.*
from sys.schemas
join sys.tables on
    schemas.schema_id = tables.schema_id
join sys.columns on
    tables.object_id = columns.object_id

    You can get more information here.