Programmatic way to rewrite a query with the view definitions as sub query?

Question

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.

Answer 1

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:

• The Seven Sins Against TSQL: Indulging in Nested Views