This may be a basic question, but I'm not sure how to phrase it, and I'm having trouble finding help as a result.

I have a relatively simple query for SQL Server that makes use of a view, and that view itself is built on other views. Rather than having to inspect each view definition and work backwards to implement the query without the view, is there any way to sort of "decompile" the query, like maybe some tool that can piece the query back together from the query plan?

9/17/2014 2:45:34 PM

If you run the query in SQL Management Studio, you can use the execution plan that should give you the "decompiled" query visually.

http://msdn.microsoft.com/en-us/library/ms191194.aspx

Outside SMS, http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan seems like a pretty good reference for your options.

But actually reconstructing a sql query from the plan is going to be manual from everything I can tell. Maybe if you used the SHOWPLAN_XML OFF flag, it'll spit out a parseable SQL query, but I doubt it.

9/17/2014 7:57:05 PM

Explain that shit.

9/17/2014 8:07:38 PM

Noen: Yeah, I'm familiar with query plans, but I'm wanting something that will bridge the gap and "recompile" the query plan for me sans views.

neodata686: I'll give you a trivial example.

Suppose I have a table called friends with columns for my friends' names and how much I think they're willing to spend on birthday presents for me.

Suppose I also have a table called gifts with columns for descriptions of gifts, their prices, and how badly on a five-point scale that I want them.

In order to raise the quality of the gifts I receive, I want gently suggest gift ideas to my cheapskate friends in a way that minimizes their expenditures and maximizes my satisfaction. To help me with this, I create a couple of views: good_cheap_gifts and cheapskate_friends.

create view good_cheap_gifts as
select description, price from gifts where want > 2 and price < 10.00

create view cheapskate_friends as
select name, budget from friends where budget < 20.00

create view cheap_gifts_from_cheap_people as
select f.name, g.description from cheapskate_friends f join good_cheap_gifts g on g.price <= f.budget

select * from cheap_gifts_from_cheap_people

select f.name, g.description from friends f join gifts g on g.price <= f.budget where f.budget < 20.00 and g.price < 10.00 and g.want > 2

9/18/2014 8:15:42 AM

if you find something that does, it post it

9/18/2014 8:47:42 AM

Quote :

"neodata686: I'll give you a trivial example."

Sorry it was a joke. 'Explain' is the command you run to see a query plan in SQL. So I wasn't actually saying explain it in the sense you thought.

9/18/2014 9:29:45 AM

Oh. Well, as you can see, I'm not talking about getting the query plan. I bascially want a query with an equivalent query plan that doesn't use the views.

9/18/2014 9:46:32 AM

Yeah I just thought it was funny. I haven't used SQL Server in a while nor do I use a lot of views in Postgres.

9/18/2014 10:01:32 AM

My DBA pretty much forbids us from creating views

9/18/2014 12:20:24 PM

Yeah, well, this guy's DBA should've done.

9/18/2014 12:54:39 PM

I don't have access to the tool anymore but I think you can do what you are after in Rapid SQL. When I was using it I wanted to know what tables a view was using and the where criteria. I selected the view and was able to see the code behind it. I think that is what you are looking for at least.

9/19/2014 4:06:39 PM

No, it isn't. I don't have any trouble getting a view's definition. What I want is to replace a view currently in use with a query with the equivalent query logic based on the view's definition.

Maybe this analogy will make it more clear: I want to inline a function from a linked library.

9/19/2014 11:25:23 PM

You don't communicate very well for an English major

BTW, congrats for being the first person to ever type that phrase

[link]https://www.google.com/search?q="inline+a+function+from+a+linked+library"[/link]

9/20/2014 12:52:00 AM

Yeah, that's what it is. I don't communicate well.

9/20/2014 2:12:19 AM

How many views are we talking here?

Can't you just look at all the view definitions and construct a single query out of them or are there 100 views or something?

9/20/2014 10:27:38 AM

I don't know, there are at least eight. I can certainly do it manually--I just don't want to. There may as well be a hundred. It wouldn't matter.

9/20/2014 12:00:02 PM