0

In SQL Server and/or its C# API, is there a mechanism by which I can get a description (i.e. column names and data types) of the result of executing arbitrary SQL/prepared statement/stored procedure without actually executing it?

Example...

select * from my my_table

Desired result...

col_1        col_2        col_3        ...        col_n
integer      float        varchar(32)             datetime2

Or some equivalent information?

rene
  • 41,474
  • 78
  • 114
  • 152
Dan Forbes
  • 2,734
  • 3
  • 30
  • 60
  • 2
    In SQL Server, check the views in `INFORMATION_SCHEMA`, as in `select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'some table name'` – MilkyWayJoe May 08 '14 at 14:03
  • Does this work arbitrary select statements, prepared statements, stored procedures, etc? – Dan Forbes May 08 '14 at 14:19
  • There's a view for procedures/triggers/functions called `ROUTINES` under this schema, but i don't think it takes care of arbitrary statements and stuff like that, but it helps a little – MilkyWayJoe May 08 '14 at 15:16

2 Answers2

2

SET FMTONLY is what you're looking for:

Returns only metadata to the client. Can be used to test the format of the response without actually running the query.

E.g.:

SET FMTONLY ON
GO
select * from my my_table
GO
SET FMTONLY OFF
GO

Will produce an empty result set.

In C#, with an SqlCommand object, you can specify the SchemaOnly CommandBehaviour and you'll similarly get an empty result set that you can then examine.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
1

I think you might get use out of this:

EXEC sp_help my_table

Or see my question/answer here: How do I get a list of columns in a table or view?

Community
  • 1
  • 1
rory.ap
  • 34,009
  • 10
  • 83
  • 174