Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Get resultset from query without running query. Expand / Collapse
Author
Message
Posted Friday, September 6, 2013 9:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 8, 2014 11:28 AM
Points: 5, Visits: 27
Is there some way to get the result set for a query without running the query? For example take the query "select * from msdb.dbo.sysjobs", and get back the result set columns and datatypes (see below). Is there some way to get that for an arbitrary query/script?


[job_id] [uniqueidentifier] NOT NULL,
[originating_server_id] [int] NOT NULL,
[name] [sysname] NOT NULL,
[enabled] [tinyint] NOT NULL,
[description] [nvarchar](512) NULL,
[start_step_id] [int] NOT NULL,
[category_id] [int] NOT NULL,
[owner_sid] [varbinary](85) NOT NULL,
[notify_level_eventlog] [int] NOT NULL,
[notify_level_email] [int] NOT NULL,
[notify_level_netsend] [int] NOT NULL,
[notify_level_page] [int] NOT NULL,
[notify_email_operator_id] [int] NOT NULL,
[notify_netsend_operator_id] [int] NOT NULL,
[notify_page_operator_id] [int] NOT NULL,
[delete_level] [int] NOT NULL,
[date_created] [datetime] NOT NULL,
[date_modified] [datetime] NOT NULL,
[version_number] [int] NOT NULL
Post #1492315
Posted Friday, September 6, 2013 10:10 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 1:54 PM
Points: 1,430, Visits: 3,229
you could either use:

select top(0) * from TABLE

or you could use a where clause that would never return any rows:

select * from TABLE where 0 = 1




The probability of survival is inversely proportional to the angle of arrival.
Post #1492320
Posted Friday, September 6, 2013 10:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:12 PM
Points: 12,928, Visits: 12,345
If you want to capture the datatypes here are a couple of different ways.

select * 
into MyNewTable
from msdb.dbo.sysjobs
where 1 = 0

select c.name as ColumnName, t.name as DataType, c.is_nullable
from msdb.sys.columns c
join sys.types t on c.user_type_id = t.user_type_id
where object_id = object_id('msdb..sysjobs')
order by c.column_id



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1492332
Posted Friday, September 6, 2013 1:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 8, 2014 11:28 AM
Points: 5, Visits: 27
I should clarify the original problem a bit here: The query i had as an example just pulled from a table. What I am looking for is something I could apply to an arbitrary query. I think there has to be something like this in SQL Server because SSIS has to figure out datatypes and column names for the dataflow task.

Thanks in advance!
Post #1492402
Posted Friday, September 6, 2013 1:57 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:12 PM
Points: 12,928, Visits: 12,345
jobrien9796 (9/6/2013)
I should clarify the original problem a bit here: The query i had as an example just pulled from a table. What I am looking for is something I could apply to an arbitrary query. I think there has to be something like this in SQL Server because SSIS has to figure out datatypes and column names for the dataflow task.

Thanks in advance!


Yes this type of metadata is stored in sys.columns. Take a look at the second example I posted.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1492406
Posted Sunday, September 8, 2013 12:03 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:30 AM
Points: 1,422, Visits: 1,838
If you are using SQL Server 2008 R2 and below, you can use FMTONLY. For SQL Server 2012 and above, you can use sp_describe_first_result_set.

Read more about it in my post: SET options-NOEXEC v/s FMTONLY-Does FMTONLY really execute the query/workload?


Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Post #1492611
Posted Sunday, September 8, 2013 4:09 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 7:07 AM
Points: 803, Visits: 720
On SQL 2008, you can use the command SET FMTONLY ON:

SET FMTONLY ON
go
--you query goes here
go
SET FMTONLY OFF

although, this is quite an ugly hack and it will not work in all cases, particularly not with stored procedures that uses temp tables.

In SQL 2012, there is a better option, the stored procedure sp_describe_first_result_set. This procedures too has limitations with stored procedures that creates temp tables, but it does not have any of the nasty side effects of FMTONLY ON.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1492624
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse