Generate Create View statements for database tables.

  • whenriksen

    SSCarpal Tunnel

    Points: 4742

    Comments posted to this topic are about the item Generate Create View statements for database tables.

    Wes
    (A solid design is always preferable to a creative workaround)

  • Hilda Garcia

    SSC Journeyman

    Points: 85

    Can you show us what the output will look like?

  • whenriksen

    SSCarpal Tunnel

    Points: 4742

    Here is the definition of the Employee table from the AdventureWorks db. The output is formatted as displayed.

    Create View [dbo].[Employee]

    AS

    ( SELECT [EmpID]

    , [NAME]

    , [Salary]

    FROM [AdventureWorks2008R2].[dbo].[Employee]

    )

    GO

    As written, it uses the 3 part naming from the source table and uses the table name as is for the view name, which will not be valid while the table has the same name. Change the view name portion to fit your naming convention. For example to prefix the table name with a "v", add the v to the string containing the dot between the schema and table name, as below.

    QUOTENAME(c1.TABLE_SCHEMA) + '.v' + QUOTENAME(c1.TABLE_NAME)

    Wes
    (A solid design is always preferable to a creative workaround)

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

Viewing 4 posts - 1 through 4 (of 4 total)

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