CODEWITHSUNDEEP
sql-servert-sqldmv
rvphx
rvphx

Reputation: 2402

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.

Upvotes: 0

Views: 3890

Answers (2)

Halt_And_Catch_Fire
Halt_And_Catch_Fire

Reputation: 1

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'SchemaName'
      AND TABLE_NAME = 'TableName'

This should work.

Upvotes: 0

Andrew O'Brien
Andrew O'Brien

Reputation: 1813

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.

Upvotes: 3

Related Questions