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 06, 2013 9:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 08, 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 06, 2013 10:10 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, March 04, 2014 7:44 AM
Points: 1,421, Visits: 3,220
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 06, 2013 10:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:44 PM
Points: 11,970, Visits: 10,995
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 06, 2013 1:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 08, 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 06, 2013 1:57 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:44 PM
Points: 11,970, Visits: 10,995
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 08, 2013 12:03 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 12:36 AM
Points: 1,379, Visits: 1,772
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://beyondrelational.com/modules/2/blogs/77/nakuls-blog.aspx
Be courteous. Drive responsibly.

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


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 4:43 AM
Points: 756, Visits: 631
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