4

Say I get a resultset back from the following query:

select * 
from sys.database_files;

For any given resultset, I want to be able to query the column names and types so I can then create tables to store the results.

What is a good way of performing this in T-SQL?

6

How about:

SELECT * 
INTO YourTableName 
FROM sys.database_files;
  • 2
    I like any solution that makes two jobs into one. – RThomas May 16 '11 at 18:03
  • There's no other solution of getting directly from TSQL the schema of a result set. Just 'select into' comes to mind. If you'd rather not use new tables, then you have to query the meta data (information_schema system views). – Marian May 16 '11 at 18:48
  • 3
    If you want to be really clean about it, use a temp table (like #YourTableName) instead of a user table. That avoids changing pages in the user database, thereby keeping logging & differential backups smaller. Kinda anal retentive, but hey, that's me! – Brent Ozar May 16 '11 at 20:31
  • Stop, Brent, you keep making sense :) I have been beating my head trying to remember how this was done, and it was pushed into a temp table, indeed. – Dale May 18 '11 at 4:16
9

Way late to the party, but just thought I'd mention a metadata enhancement in SQL Server Denali that will make it much easier - not only to inspect the output of a query without running it (not quite the same behavior as SET FMTONLY ON, which many apps use today), but also to build target tables dynamically (without all the parsing and case work involved with pulling metadata from sys.columns). Here is a quick example - notice you never have to actually run the query to figure out the shape of its resultset:

DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM sys.database_files';

SELECT name, system_type_name, collation_name
    FROM sys.dm_exec_describe_first_result_set(@sql, NULL, 0)
    ORDER BY column_ordinal;

There are some limitations, of course. If you have a query with multiple statements, the phrase "first_result_set" in the name should give a clue that you will only receive information about the first statement that returns data - it will not stop at a leading SET NOCOUNT ON; for example. There are also going to be issues if you are referencing > 3-part names either directly or by trying to follow a view or synonym. But for the majority of use cases this will be a welcome simplification.

I blogged a lot more details back in December, also describing some of the other metadata enhancements:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.