Post reply

Flattening Nested Views

  • 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!

  • July 26, 2016 at 10:38 am

    #1892098

    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

  • July 26, 2016 at 10:55 am

    #1892101

    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) A socialist is someone who will give you the shirt off *someone else's* back.

  • July 26, 2016 at 11:52 am

    #1892115

    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

  • July 26, 2016 at 12:54 pm

    #1892134

    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.

  • 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

  • July 26, 2016 at 12:57 pm

    #1892135

    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

    • Viewing 6 posts - 1 through 5 (of 5 total)

    You must be logged in to reply to this topic. Login to reply