SQL Server sp_help to pull out just the columns information

Question

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.

Are you just looking for a list of columns and their associated meta data? — Andrew O'Brien, Apr 04 '17 at 23:54
Yes. Primary information that I need are the column_name, data type, length and nullability. Thanks. — rvphx, Apr 04 '17 at 23:56

score 3 · Accepted Answer · answered Apr 05 '17 at 00:02

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.

score 0 · Answer 2 · answered Apr 05 '17 at 01:36

0

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

This should work.

answered Apr 05 '17 at 01:36

Halt_And_Catch_Fire

1
1

SQL Server sp_help to pull out just the columns information

2 Answers2