Flattening Nested Views

Question

Flattening Nested Views

jmann84

Old Hand

Points: 342
More actions
July 26, 2016 at 10:33 am

#328662

Hi All,
I am in clean up mode from a previous DBA who loved to nest views when he was building scripting.
I can peel these apart by hand, but some of these are 6 or 7 levels deep.
Can anyone recommend some dynamic sql or perhaps a tool that can flatten these to the point where the only dependencies would be the actual tables the data is coming from?
Thanks in advance!
Views can include a CTE.
Views can be given aliases within another view.
Aliases within a view can duplicate aliases in a parent/sibling view once flattened.
Views may have the same name as other objects if they are in different schemas.
There may be synonyms for some of your views.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

jmann84 (7/26/2016)
Hi All,
I am in clean up mode from a previous DBA who loved to nest views when he was building scripting.
I can peel these apart by hand, but some of these are 6 or 7 levels deep.
Can anyone recommend some dynamic sql or perhaps a tool that can flatten these to the point where the only dependencies would be the actual tables the data is coming from?
Thanks in advance!

care to provide some sample code that needs sorting?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

ScottPletcher SSC Guru Points: 101120 More actions · Answer 2

I can't imagine any tool that would be able to do that, given that each view level could be arbitrarily complex.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

jmann84 Old Hand Points: 342 More actions · Answer 3

So I just created this to illustrate what I'm looking to do.

CREATE TABLE STYLE(

[StyleID] [smallint] NOT NULL,

[StyleName] [varchar](50) NOT NULL,

[Activestatus] [bit] NULL

)

GO

CREATE TABLE REPORTHEADER(

[StyleID] [smallint] NOT NULL,

[ReportHeaderForeColor] [varchar](50) NULL CONSTRAINT [DF_Table_1_ReportHeaderForeColor] DEFAULT ('Black'),

[ReportHeaderBackColor] [varchar](50) NULL CONSTRAINT [DF_SSRS_ReportStyles_ReportHeaderBackColor] DEFAULT ('White'),

[ReportHeaderFont] [varchar](50) NULL CONSTRAINT [DF_SSRS_ReportStyles_ReportHeaderFont] DEFAULT ('Arial'),

[ReportHeaderSize] [int] NULL CONSTRAINT [DF_SSRS_ReportStyles_ReportHeaderSize] DEFAULT ((20)),

[ReportHeaderStyle] [varchar](50) NULL CONSTRAINT [DF_SSRS_ReportStyles_ReportHeaderStyle] DEFAULT ('Normal'),

[ReportHeaderWeight] [varchar](50) NULL CONSTRAINT [DF_SSRS_ReportStyles_ReportHeaderWeight] DEFAULT (N'Bold')

)

GO

CREATE TABLE TABLEHEADER(

[StyleID] [smallint] NOT NULL,

[TableHeaderForeColor] [varchar](50) NULL CONSTRAINT [DF_Table_1_TableHeaderBackColor] DEFAULT ('Black'),

[TableHeaderBackColor] [varchar](50) NULL CONSTRAINT [DF_SSRS_ReportStyles_TableHeaderBackColor] DEFAULT ('White'),

[TableHeaderFont] [varchar](50) NULL CONSTRAINT [DF_SSRS_ReportStyles_TableHeaderFont] DEFAULT ('Arial'),

[TableHeaderSize] [int] NULL CONSTRAINT [DF_SSRS_ReportStyles_TableHeaderSize] DEFAULT ((10)),

[TableHeaderStyle] [varchar](50) NULL CONSTRAINT [DF_SSRS_ReportStyles_TableHeaderStyle] DEFAULT ('Normal'),

[TableHeaderWeight] [varchar](50) NULL CONSTRAINT [DF_SSRS_ReportStyles_TableHeaderWeight] DEFAULT ('Normal')

)

GO

CREATE TABLE MISCINFO(

[StyleID] [smallint] NOT NULL,

[DataForeColor] [varchar](50) NULL CONSTRAINT [DF_SSRS_ReportStyles_DataForeColor] DEFAULT ('Black'),

[DataFont] [varchar](50) NULL CONSTRAINT [DF_SSRS_ReportStyles_DataFont] DEFAULT ('Arial'),

[DataSize] [int] NULL CONSTRAINT [DF_SSRS_ReportStyles_DataSize] DEFAULT ((10)),

[DataStyle] [varchar](50) NULL CONSTRAINT [DF_SSRS_ReportStyles_DataStyle] DEFAULT ('Normal'),

[DataWeight] [varchar](50) NULL CONSTRAINT [DF_SSRS_ReportStyles_DataWeight] DEFAULT ('Normal'),

[ToogleRowBackColor1] [varchar](50) NULL CONSTRAINT [DF_SSRS_ReportStyles_ToogleRowBackColor1] DEFAULT ('White'),

[ToogleRowBackColor2] [varchar](50) NULL CONSTRAINT [DF_SSRS_ReportStyles_ToogleRowBackColor2] DEFAULT ('White'),

)

GO

CREATE VIEW reportandtableheaders AS

SELECT a.[StyleID]

,[ReportHeaderForeColor]

,[ReportHeaderBackColor]

,[ReportHeaderFont]

,[ReportHeaderSize]

,[ReportHeaderStyle]

,[ReportHeaderWeight]

,[TableHeaderForeColor]

,[TableHeaderBackColor]

,[TableHeaderFont]

,[TableHeaderSize]

,[TableHeaderStyle]

,[TableHeaderWeight]

FROM REPORTHEADER a inner join TABLEHEADER b ON a.StyleID = b.StyleID

GO

CREATE VIEW styleandmisc AS

SELECT a.[StyleID]

,[StyleName]

,[DataForeColor]

,[DataFont]

,[DataSize]

,[DataStyle]

,[DataWeight]

,[ToogleRowBackColor1]

,[ToogleRowBackColor2]

,[Activestatus]

FROM STYLE a inner join MISCINFO b on a.StyleID = b.StyleID

GO

--This is the only view that is actually used

CREATE VIEW COMBINEALL AS

SELECT a.[StyleID]

,[StyleName]

,[ReportHeaderForeColor]

,[ReportHeaderBackColor]

,[ReportHeaderFont]

,[ReportHeaderSize]

,[ReportHeaderStyle]

,[ReportHeaderWeight]

,[TableHeaderForeColor]

,[TableHeaderBackColor]

,[TableHeaderFont]

,[TableHeaderSize]

,[TableHeaderStyle]

,[TableHeaderWeight]

,[DataForeColor]

,[DataFont]

,[DataSize]

,[DataStyle]

,[DataWeight]

,[ToogleRowBackColor1]

,[ToogleRowBackColor2]

,[Activestatus]

FROM reportandtableheaders a inner join styleandmisc b on a.StyleID = b.StyleID

GO

What I'm looking to do using something like the sys.sql_modules table to extract the definition to flatten the COMBINEALL view into something like this (just to get it all in one place to start with):

ALTER VIEW COMBINEALL AS

SELECT a.[StyleID]

,[StyleName]

,[ReportHeaderForeColor]

,[ReportHeaderBackColor]

,[ReportHeaderFont]

,[ReportHeaderSize]

,[ReportHeaderStyle]

,[ReportHeaderWeight]

,[TableHeaderForeColor]

,[TableHeaderBackColor]

,[TableHeaderFont]

,[TableHeaderSize]

,[TableHeaderStyle]

,[TableHeaderWeight]

,[DataForeColor]

,[DataFont]

,[DataSize]

,[DataStyle]

,[DataWeight]

,[ToogleRowBackColor1]

,[ToogleRowBackColor2]

,[Activestatus]

FROM (SELECT a.[StyleID]

,[ReportHeaderForeColor]

,[ReportHeaderBackColor]

,[ReportHeaderFont]

,[ReportHeaderSize]

,[ReportHeaderStyle]

,[ReportHeaderWeight]

,[TableHeaderForeColor]

,[TableHeaderBackColor]

,[TableHeaderFont]

,[TableHeaderSize]

,[TableHeaderStyle]

,[TableHeaderWeight]

FROM REPORTHEADER a inner join TABLEHEADER b ON a.StyleID = b.StyleID) a inner join ( SELECT a.[StyleID]

,[StyleName]

,[DataForeColor]

,[DataFont]

,[DataSize]

,[DataStyle]

,[DataWeight]

,[ToogleRowBackColor1]

,[ToogleRowBackColor2]

,[Activestatus]

FROM STYLE a inner join MISCINFO b on a.StyleID = b.StyleID) b on a.StyleID = b.StyleID

drew.allen SSC Guru Points: 76996 More actions · Answer 4

I think that it's possible, but it's going to be very complex, and it might just be easier to do it manually.

Here are some of the issues that you'll need to be aware of and possibly account for.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 5

drew.allen (7/26/2016)
just be easier to do it manually.
Drew

agree

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day