0

Wondering if anyone has found or knows of a way to programmatically re-write a query to use the view definition as a subquery?

For example, let's say I have a view:

CREATE VIEW View1
AS
select * from table1
go

I want to be able to take the query:

select * from View1

And have it automatically rewritten as

select *
from (
  select * from table1
) as View1

This is a simple example, but I'd like to be able to do this for more complex queries, for example, if a view references another view, or there are multiple views joined together. Rather than manually looking at the definitions of all the views and rewriting it manually, I was hoping to come up with a way to do this programatically some how. I could probably figure something out on my own, but I just wanted to ask first in case someone knows of something that already does this so I don't have to recreate the wheel.

3
  • 1
    Why do you need to do this?
    – mustaccio
    Aug 4, 2020 at 18:01
  • For a pet project I'm working on. Plus, sometimes I think it's easier to figure out what's going on in an execution plan when I am able to see the actual table queries, rather than the query text just being "select * from view"
    – MarkAugust
    Aug 4, 2020 at 18:12
  • If it is just for a few queries, I would download a copy of RedGate SQL Prompt. Then it is easy to see and grab the underlying code. Aug 12, 2020 at 17:58

1 Answer 1

0

I've never heard of or seen anything of this ilk.

You're most likely better served rethinking how you utilize views and refactoring them in a way that isn't an impediment to development, instead of building a tool to do this.

Several layers of nested views are (to me) an anti-pattern and make it harder for developers and SQL Server alike. They're also very likely to hide bad code and obscure issues.

Also see:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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