Technical Article

Generate Create View statements for database tables.

,

In a refactoring project, we determined we needed to put our maintenance and logging tables in a serperate database from the customer data.

I created this statement to create views pointing to the new table locations to maintain backward compatibility for existing objects.

I recommend you run this on the new database location as it will include the reference to the new database in the defintion.  Otherwise, you'll want to update the code to hardcode your new database name.

Hope this saves you some time creating views!

SELECT DISTINCT STATEMENT = 
        'Create View ' + QUOTENAME(c1.TABLE_SCHEMA) + '.' + QUOTENAME(c1.TABLE_NAME) + ' 
AS 
  ( SELECT ' + REPLACE(SUBSTRING(( SELECT ', ' + QUOTENAME(COLUMN_NAME)
                                   FROM   INFORMATION_SCHEMA.COLUMNS AS C2
                                   WHERE  c2.TABLE_NAME = c1.Table_name
                                          AND c2.TABLE_SCHEMA = c1.table_schema
                                   ORDER BY ORDINAL_POSITION
                                 FOR
                                   XML PATH('')
                                 ) , 2 , 8000) + ' 
    FROM    ' + QUOTENAME(c1.TABLE_CATALOG) + '.' + QUOTENAME(c1.TABLE_SCHEMA) + '.' + QUOTENAME(c1.TABLE_NAME) +'
)
GO' , ', ' , CHAR(13) + SPACE(10) + ', ')
FROM    INFORMATION_SCHEMA.COLUMNS AS C1
ORDER BY Statement

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

3.5 (2)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

3.5 (2)

You rated this post out of 5. Change rating