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.

MarkAugust
  • 66
  • 5
  • 1
    Why do you need to do this? – mustaccio Aug 04 '20 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 04 '20 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. – Henrik Staun Poulsen Aug 12 '20 at 17:58

1 Answers1

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:

LowlyDBA - John M
  • 10,922
  • 11
  • 42
  • 62