Tool for Scripting View Generation

/******************************************************************************
Joe Kelly
2013-08-29 13:54:01.987
ScriptGen.sql

Given two databases, source and dest, this script will generate a READ ONLY
view in dest for each table or view in source.

Note:

    - Existing schema.object definitions in dest will be overwritten

    - If one tries to update one of the read only views an error as follows
      will be generated:

        Update or insert of view or function 'abc' failed because it
        contains a derived or constant field.

      this is due to the intentional ambiguity introduced to the view by a
      union on the same object with a predicate of where 1 = 0

    - Columns are not named because on larger objects the buffer can be
      exceeded resulting in syntax errors (yes, it would better to do the
      concatenation in an external program)

    - For easier testing change the rowcount set

To Use:

    Set the Custom variables and execute in text mode or to file, apply output carefully

        @sourceDB SYSNAME = 'test'
    ,    @destDB SYSNAME = 'test2'
    ,    @sourceSchema SYSNAME = 'dbo'
    ,    @destSchema SYSNAME = 'someSchema'

******************************************************************************/


USE test

GO

-- SET ROWCOUNT 30
SET ROWCOUNT 0

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- Custom variables
DECLARE
    @sourceDB SYSNAME = 'foo'
,    @destDB SYSNAME = 'test2'
,    @sourceSchema SYSNAME = 'dbo'
,    @destSchema SYSNAME = 'someSchema'

-- System variables
DECLARE
    @rowCount BIGINT = 0
,    @rowIter BIGINT = 0
,    @columnCount INT = 0
,    @columnIter BIGINT = 0
,    @tableString NVARCHAR (MAX) = ''
,    @tableStringShort NVARCHAR (MAX) = ''
,    @tableName SYSNAME = ''
,    @tableType NVARCHAR (16) = ''
,    @commandString NVARCHAR (MAX) = ''
,    @use NVARCHAR (MAX) = ''
,    @drop1 NVARCHAR (MAX) = 'IF OBJECT_ID('''
,    @drop2 NVARCHAR (MAX) = ''') IS NOT NULL DROP VIEW '

SELECT @use = 'USE ' + @sourceDB

-- Ensure we are in the correct DB
EXECUTE sp_executesql @stmt = @use

IF OBJECT_ID('tempdb..#columns') IS NOT NULL
    DROP TABLE #columns

IF OBJECT_ID('tempdb..#tables') IS NOT NULL
    DROP TABLE #tables

IF OBJECT_ID('tempdb..#output') IS NOT NULL
    DROP TABLE #output

CREATE TABLE #columns (
    ident BIGINT IDENTITY (1, 1)
,    TABLE_CATALOG SYSNAME
,    TABLE_SCHEMA SYSNAME    
,    TABLE_NAME SYSNAME    
,    TABLE_TYPE SYSNAME
,    COLUMN_NAME SYSNAME 
,    ORDINAL_POSITION INT
)

CREATE TABLE #tables (
    ident BIGINT IDENTITY (1, 1) 
,    TABLE_CATALOG SYSNAME
,    TABLE_SCHEMA SYSNAME    
,    TABLE_NAME SYSNAME    
,    TABLE_TYPE SYSNAME
,    columnCount INT
,    rowStart BIGINT
)

CREATE TABLE #output (
    ident BIGINT IDENTITY (1, 1) 
,    command NVARCHAR (MAX)
)

-- Get a list of all the columns in the tables and views 
INSERT #columns (
    TABLE_CATALOG   
,    TABLE_SCHEMA   
,    TABLE_NAME   
,    TABLE_TYPE
,    COLUMN_NAME
,    ORDINAL_POSITION
)    
SELECT
    t.TABLE_CATALOG   
,    t.TABLE_SCHEMA   
,    t.TABLE_NAME   
,    t.TABLE_TYPE
,    c.COLUMN_NAME
,    c.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c
    ON    t.TABLE_CATALOG = c.TABLE_CATALOG   
    AND    t.TABLE_SCHEMA = c.TABLE_SCHEMA   
    AND    t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_TYPE IN ('BASE TABLE', 'VIEW')
ORDER BY
    t.TABLE_CATALOG   
,    t.TABLE_SCHEMA   
,    t.TABLE_NAME   

-- Get a list of all the distinct tables and views 
INSERT #tables (
    TABLE_CATALOG
,    TABLE_SCHEMA
,    TABLE_NAME
,    TABLE_TYPE
,    columnCount
,    rowStart
)
SELECT DISTINCT
    c.TABLE_CATALOG
,    c.TABLE_SCHEMA
,    c.TABLE_NAME
,    c.TABLE_TYPE
,    COUNT (c.TABLE_CATALOG)
,    MIN (c.ident)
FROM #columns c
GROUP BY
    c.TABLE_CATALOG
,    c.TABLE_SCHEMA
,    c.TABLE_NAME
,    c.TABLE_TYPE

SELECT @rowCount = @@ROWCOUNT

-- Set the destination db for the output
INSERT #output ( command )
SELECT 'USE ' + @destDB
+    CHAR (10) -- + CHAR (13)
+    CHAR (10) -- + CHAR (13)
+    'GO '
+    CHAR (10) -- + CHAR (13)

-- Ensure that the schema exists in the destination db
-- Create schema has to be the first command in a batch
-- so this will be a fatal error
INSERT #output ( command )
SELECT 'IF SCHEMA_ID('''
+    @destSchema
+    ''') IS NULL '
+    'SELECT ''Destination Schema ['
+    @destSchema
+    '] does not extist'''
+    CHAR (10) -- + CHAR (13)
+    CHAR (10) -- + CHAR (13)
+    'GO '
+    CHAR (10) -- + CHAR (13)
--+     'CREATE SCHEMA '
--+    '['
--+    @destSchema
--+    '] AUTHORIZATION dbo '
--+    CHAR (10) -- + CHAR (13)
--+    CHAR (10) -- + CHAR (13)
--+    'GO '
--+    CHAR (10) -- + CHAR (13)

-- Iterate through the list of tables (order is important)
WHILE @rowIter < @rowCount
BEGIN

    SELECT @rowIter += 1
    ,    @columnIter = 0
    ,    @commandString = ''

    SELECT    @columnCount = t.columnCount
    ,    @tableName = t.TABLE_NAME
    ,    @tableType = t.TABLE_TYPE
    ,    @tableString = '[' + @destDB + '].[' + @destSchema + '].[' + t.TABLE_NAME + ']'
    ,    @tableStringShort = '[' + @destSchema + '].[' + t.TABLE_NAME + ']'
    FROM    #tables t
    WHERE    t.ident = @rowIter

    SELECT @commandString += 'SELECT * '

    -- This can produce text strings that are just too long fo the
    -- SSMS editor so just use the wildcard ...

    ---- Iterate through the list of columns (order is important)
    --WHILE @columnIter < @columnCount -1
    --BEGIN

    --    SELECT @columnIter += 1

    --    -- Get all but the last column
    --    SELECT    @commandString += '['
    --    +    c.COLUMN_NAME
    --    +    '] /*'
    --    +    CAST (c.ORDINAL_POSITION AS VARCHAR (8))
    --    +    '*/ , '
    --    FROM    #columns c
    --    WHERE    c.TABLE_NAME = @tableName
    --        AND    c.ORDINAL_POSITION = @columnIter
       
    --END 

    --SELECT @columnIter += 1

    ---- Now get the last column
    --SELECT    @commandString += '['
    --+    c.COLUMN_NAME
    --+    '] /*'
    --+    CAST (c.ORDINAL_POSITION AS VARCHAR (8))
    --+    '*/ '
    --FROM    #columns c
    --WHERE    c.TABLE_NAME = @tableName
    --    AND    c.ORDINAL_POSITION = @columnIter

    -- Now build up the table name and attach
    SELECT    @commandString += ' FROM ['
    +    @sourceDB    
    +    '].['
    +    @sourceSchema       
    +    '].['
    +    @tableName
    +    ']'
   
    -- Drop statements
    INSERT #output ( command )
    SELECT    @drop1
    +    @tableString   
    +    @drop2
    +    @tableStringShort
    +    ';'
    +    CHAR (10) -- + CHAR (13)
    +    CHAR (10) -- + CHAR (13)
    +    'GO '
    +    CHAR (10) -- + CHAR (13)

    INSERT #output ( command )
    SELECT 'CREATE VIEW '
    +    @tableStringShort
    +    CHAR (10) -- + CHAR (13)
    +    'AS '
    +    CHAR (10) -- + CHAR (13)
    +     @commandString
    +    CHAR (10) -- + CHAR (13)
    +    'UNION '
    +    CHAR (10) -- + CHAR (13)
    +    @commandString
    +    ' WHERE 1 = 0; '
    +    CHAR (10) -- + CHAR (13)
    +    CHAR (10) -- + CHAR (13)
    +    'GO '
    +    CHAR (10) -- + CHAR (13)
   
END 

SELECT command '-- BE REALLY CAREFUL WHERE YOU RUN THIS ... '
FROM #output