September 6, 2013 at 9:59 am
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
September 6, 2013 at 10:10 am
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.
September 6, 2013 at 10:23 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 6, 2013 at 1:44 pm
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!
September 6, 2013 at 1:57 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 8, 2013 at 12:03 pm
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
Follow me on
Twitter: @sqltwins
September 8, 2013 at 4:09 pm
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply