sp_help for statements

Question

In SQL SERVER the

SP_HELP 'table_name'

returns a description of each column including it's type, length, nullable state etc. I'm using this info to auto-generate DTOs (classes) and mappings.

If I want to do this for an inline SQL statement (which includes aliases, joins etc.) I create a view temporarily with the definition being my SQL statement and then run sp_help for this view - which gives me all I need. After that I just delete the view.

The question:

Is there any other - simpler way of getting this info for inline SQL - preferably without going through the view creation?

Answer 1

Yes. All object definitions are available from within SQL Server as tables or views. These meta data sources are collectively known as "the catalog."

The industry standard way is through the information schema views. However, I've heard reports that SQL Server's implementation can be a bit rough.

There's another set with names like sys.tables, sys.views etc. These are the ones most often used.

A set of program APIs are published called server management objects (SMO). These can give meta data or script whole objects. They are what SSMS Object Explorer uses. The documentation can be terse but if you persevere it is very powerful. SMO can be called from C#, powershell or other languages.

To create the DTO for an arbitrary query I would reference the catalog views for the individual columns returned. Frankly your technique of creating a transient view is as good a way as any. You could also create a temporary table using select .. into #SomeName. Temp tables clean themselves away when they go out of scope. Similarly wrap the query in a temporary stored proc and use sys.dm_exec_describe_first_result_set.

Answer 2

select * 
from sys.dm_exec_describe_first_result_set('select * from table_name',null,0) 

seems to be the best solution. No need for wrapping.