What a View

  • I would take a different approach. Not necessarily better, but different.

    I would avoid the cursor and the immediate EXEC(), instead just use a query to produce the desired script for all tables (Output to Text). It's easy to copy the results to a new window and possibly make some manual corrections.

    Instead of building the script with a lot of concatenation, I like to wrap a readable script template in as many REPLACE() functions as necessary.

    I don't like the SELECT * definitions, it's not hard to get the column list.

    If I had to exclude columns like SSN that should not be exposed, I would add an extended property to those columns to keep them out of the column list. These exclusions will then be repeated if the script has to be run again in future.

    SELECTREPLACE(REPLACE(REPLACE(

    'CREATE VIEW [<s>].vw_[<t>] AS

    SELECT <list>

    FROM [<s>].[<t>]

    GO',

    '<t>', tbl.name),

    '<s>', sch.name),

    '<list>', STUFF(cl.ColumnList, 1, 2, ''))

    FROM sys.tables tbl

    INNER JOIN sys.schemas sch ON tbl.schema_id = sch.schema_id

    CROSS APPLY (

    SELECT', ' + QUOTENAME(col.name)

    FROM sys.columns col

    LEFT JOIN sys.extended_properties xp ON xp.major_id = col.object_id AND xp.minor_id = col.column_id

    AND xp.class = 1 AND xp.name = 'HiddenColumn'

    WHERE col.object_id = tbl.object_id AND xp.major_id IS NULL

    ORDER BY column_id

    FOR XML PATH('')

    ) cl (ColumnList)

    WHERE tbl.is_ms_shipped = 0 AND cl.ColumnList IS NOT NULL

    AND NOT EXISTS(SELECT NULL FROM sys.objects WHERE name = 'vw_' + tbl.name AND schema_id = tbl.schema_id)

  • Scott Coleman (10/2/2012)


    I would take a different approach. Not necessarily better, but different.

    I would avoid the cursor and the immediate EXEC(), instead just use a query to produce the desired script for all tables (Output to Text). It's easy to copy the results to a new window and possibly make some manual corrections.

    Instead of building the script with a lot of concatenation, I like to wrap a readable script template in as many REPLACE() functions as necessary.

    I don't like the SELECT * definitions, it's not hard to get the column list.

    If I had to exclude columns like SSN that should not be exposed, I would add an extended property to those columns to keep them out of the column list. These exclusions will then be repeated if the script has to be run again in future.

    SELECTREPLACE(REPLACE(REPLACE(

    'CREATE VIEW [<s>].vw_[<t>] AS

    SELECT <list>

    FROM [<s>].[<t>]

    GO',

    '<t>', tbl.name),

    '<s>', sch.name),

    '<list>', STUFF(cl.ColumnList, 1, 2, ''))

    FROM sys.tables tbl

    INNER JOIN sys.schemas sch ON tbl.schema_id = sch.schema_id

    CROSS APPLY (

    SELECT', ' + QUOTENAME(col.name)

    FROM sys.columns col

    LEFT JOIN sys.extended_properties xp ON xp.major_id = col.object_id AND xp.minor_id = col.column_id

    AND xp.class = 1 AND xp.name = 'HiddenColumn'

    WHERE col.object_id = tbl.object_id AND xp.major_id IS NULL

    ORDER BY column_id

    FOR XML PATH('')

    ) cl (ColumnList)

    WHERE tbl.is_ms_shipped = 0 AND cl.ColumnList IS NOT NULL

    AND NOT EXISTS(SELECT NULL FROM sys.objects WHERE name = 'vw_' + tbl.name AND schema_id = tbl.schema_id)

    Nice! I like variations. This may seem a bit advanced for the beginners, but, they will be alright with multiple options.

    thx for the post Scott

    Cheers,
    John Esraelo

Viewing 2 posts - 31 through 32 (of 32 total)

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