ViewDuplication

sql.sql

USE CommonDB
GO
/*
USER DEFINED FUNCTION: getColumnNames
USE: PRIVATE/INTERNAL
AUTHOR: BDC 10/9/23
DESCRIPTION: This function returns a comma-separated list of column names for a given table and schema.
PARAMETERS:
- @schemaName: the name of the schema where the table is located.
- @tableName: the name of the table to retrieve column names from.
RETURN VALUE/TYPE: The function returns the column names as a NVARCHAR(MAX) value.

*/
CREATE OR ALTER FUNCTION dbo.getColumnNames(@schemaName NVARCHAR(50), @tableName NVARCHAR(250))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @columns NVARCHAR(MAX)
    SET @columns = ''

    SELECT @columns = @columns + QUOTENAME(COLUMN_NAME) + ', '
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = @schemaName AND TABLE_NAME = @tableName

    SET @columns = LEFT(@columns, LEN(@columns) - 2)
    RETURN @columns
END

GO

/*
USER DEFINED FUNCTION: getTableDescription
USE: PRIVATE/INTERNAL
AUTHOR: BDC 10/9/23
DESCRIPTION: This function retrieves the description of a table in a database. It takes in two parameters:
PARAMETERS:
- @schemaName: the name of the schema where the table is located
- @tableName: the name of the table whose description is to be retrieved
RETURN VALUE/TYPE: The function returns the description of the table as a NVARCHAR(MAX) value.
*/

CREATE OR ALTER FUNCTION dbo.getTableDescription(@schemaName NVARCHAR(50), @tableName NVARCHAR(250))
RETURNS NVARCHAR(MAX)
AS
BEGIN

    DECLARE @tableDescription NVARCHAR(MAX)
    SELECT @tableDescription = CAST(value AS NVARCHAR(MAX))
    FROM sys.extended_properties
    WHERE major_id = OBJECT_ID(@schemaName + '.' + @tableName)
    AND minor_id = 0
    AND class = 1
    AND name = 'MS_Description'
    RETURN @tableDescription
END

GO

/*
USER DEFINED FUNCTION: getColumnDescription
USE: PRIVATE/INTERNAL
AUTHOR: BC 10/9/23
DESCRIPTION: This function retrieves the description of a table in a database. It takes in two parameters:
PARAMETERS:
- @schemaName: the name of the schema where the table is located
- @tableName: the name of the table whose description is to be retrieved
RETURN VALUE/TYPE: The function returns the description of the table as a NVARCHAR(MAX) value.
*/
CREATE FUNCTION dbo.getColumnDescription(@schemaName NVARCHAR(50), @tableName NVARCHAR(250), @columnName NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @columnDescription NVARCHAR(MAX)
    SELECT @columnDescription = CAST(value AS NVARCHAR(MAX))
    FROM sys.extended_properties
    WHERE major_id = OBJECT_ID(@schemaName + '.' + @tableName)
    AND minor_id = COLUMNPROPERTY(OBJECT_ID(@schemaName + '.' + @tableName), @columnName, 'ColumnId')
    AND class = 1
    AND name = 'MS_Description'
    RETURN @columnDescription
END
GO


/*
USER DEFINED STORED PROCEDURE: addObjectDescription
USE: PRIVATE/INTERNAL
DESCRIPTION: Adds a description to a schema, table, view, or column using the MS_Description extended property.
PARAMETERS:
- @description - The description to be added.
- @schemaName - The name of the schema.
- @tableOrViewType - The type of object to add the description to. Must be either 'VIEW' or 'TABLE'. Default is 'VIEW'.
- @tableOrViewName - The name of the table or view.
- @columnName - The name of the column. Optional.
RETURN VALUE/TYPE: None
*/


CREATE PROCEDURE dbo.addObjectDescription(
    @description NVARCHAR(MAX),
    @schemaName NVARCHAR(50), 
    @tableOrViewType NVARCHAR(50) = 'VIEW', 
    @tableOrViewName NVARCHAR(250), 
    @columnName NVARCHAR(250) = NULL
    )
AS
BEGIN
    IF @tableOrViewType NOT IN ('VIEW','TABLE')
    BEGIN
        RAISERROR('Invalid value for @tableOrViewType. Must be either ''VIEW'' or ''TABLE''.', 16, 1)
        RETURN
    END
    DECLARE @optionalLevel2Type NVARCHAR(50) = NULL
    DECLARE @optionalLevel2Name NVARCHAR(50) = NULL
    
    IF @columnName IS NOT NULL
    BEGIN
        SET @optionalLevel2Name = @columnName
        SET @optionalLevel2Type = 'COLUMN'
    END

    EXEC sys.sp_addextendedproperty
    @name = N'MS_Description',
    @value = @description,
    @level0type = N'SCHEMA',
    @level0name = @schemaName,
    @level1type = @tableOrViewType,
    @level1name = @tableOrViewName,
    @level2type = @optionalLevel2Type,
    @level2name = @optionalLevel2Name;
    
END

/*
USER DEFINED STORE PROCEDURE: createViewFromTable
AUTHOR: BDC 10/9/23
DESCRIPTION: Creates a view based on a specified table, with the option to specify a different schema and view name.
PARAMETERS:
- @tableName - the name of the table to create the view from
- @schemaName - the schema of the table (default is 'dbo')
- @destinationSchemaName - the schema of the view (default is 'vw')
- @destinationViewName - the name of the view (default is the name of the table)
- @columns - the columns to include in the view (default is all columns)
RETURN VALUE/TYPE: None
NOTES: 
    - If @destinationViewName is not specified, the view will have the same name as the table.
    - If @columns is not specified, all columns will be included in the view.
    - The view will have the same column descriptions as the original table.
    - The view will have the same view/table description as the original table.
*/
CREATE PROCEDURE createViewFromTable
    @tableName NVARCHAR(250),
    @schemaName NVARCHAR(50) = 'dbo',
    @destinationSchemaName NVARCHAR(50) = 'vw',
    @destinationViewName NVARCHAR(255) = NULL,
    @columns NVARCHAR(MAX) = NULL
AS
BEGIN
    SET @destinationViewName = ISNULL(@destinationViewName, @tableName)
    DECLARE @qualifiedTableName NVARCHAR(300) = QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName)
    DECLARE @qualifiedViewName NVARCHAR(300) = QUOTENAME(@destinationSchemaName) + '.' + QUOTENAME(@destinationViewName)

    DECLARE @sql NVARCHAR(MAX) = ''
    DECLARE @columnName NVARCHAR(128) = ''
    DECLARE @tableDescription NVARCHAR(MAX) = ''
    DECLARE @columnDescription NVARCHAR(MAX) = ''


    IF @tableName IS NOT NULL
    BEGIN

        -- Get column names for the current table
        SELECT @columns = dbo.getColumnNames(@schemaName, @tableName)

        -- Create the view with explicit column names
        SET @sql = 'CREATE VIEW ' + @qualifiedViewName + ' AS SELECT '
        SET @sql = @sql + @columns + ' FROM ' + @qualifiedTableName + ';'
        EXEC sp_executesql @sql

        -- Duplicate table description to view description
        SELECT @tableDescription = dbo.getTableDescription(@schemaName,@tableName)
        EXEC dbo.addObjectDescription @tableDescription, @destinationSchemaName, 'VIEW', @destinationViewName

        -- Duplicate column descriptions to view column descriptions
        DECLARE columnCursor CURSOR FOR
        SELECT COLUMN_NAME
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = @schemaName AND TABLE_NAME = @tableName

        OPEN columnCursor

        FETCH NEXT FROM columnCursor INTO @columnName

        WHILE @@FETCH_STATUS = 0
        BEGIN
            -- Get column description
            SELECT @columnDescription = dbo.getColumnDescription(@schemaName, @tableName, @columnName)

            IF @columnDescription IS NOT NULL
            BEGIN
                -- Add column description to view
                EXEC dbo.addColumnDescription @columnDescription, @destinationSchemaName, 'VIEW', @destinationViewName, @columnName
            END

            FETCH NEXT FROM columnCursor INTO @columnName
        END

        CLOSE columnCursor
        DEALLOCATE columnCursor
    END
    ELSE
    BEGIN
        RAISERROR('Table name must be specified.', 16, 1)
    END

END

/* 
USER DEFINED STORED PROCEDURE: generateVwListings 
AUTHOR: BDC 10/9/23
DESCRIPTION: This stored procedure generates views for all non-deleted tables in the current database. 
It loops through each table in the LogDB.dbo.tabListing table and generates a view for it if it doesn't already exist.
If the destination view name is not specified, it uses the table name as the view name. 

PARAMETERS:
None 

RETURN VALUE/TYPE: None 

NOTES: 
- This stored procedure requires the createViewFromTable stored procedure to be present in the database.
- The LogDB.dbo.tabListing table must exist and have the following columns: ID (int), schemaName (nvarchar(50)), tableName (nvarchar(250)), 
  destinationViewName (nvarchar(255)), createView (bit), forceReprocess (bit), isDeleted (bit), databaseName (nvarchar(128)).
- If forceReprocess is set to 1, the view will be dropped and recreated even if it already exists.
*/
CREATE PROCEDURE generateVwListings
AS
BEGIN
    DECLARE @ID INT
    DECLARE @tableName NVARCHAR(250)
    DECLARE @schemaName NVARCHAR(50) = 'dbo'
    DECLARE @destinationSchemaName NVARCHAR(50) = 'vw'
    DECLARE @destinationViewName NVARCHAR(255) = NULL
    DECLARE @columns NVARCHAR(MAX) = NULL
    DECLARE @createView BIT
    DECLARE @forceReprocess BIT

    DECLARE tableCursor CURSOR FOR
    SELECT ID, schemaName, tableName, destinationViewName, createView, forceReprocess
    FROM LogDB.dbo.tabListing
    WHERE isDeleted = 0
    and databaseName = DB_NAME()
    ORDER BY ID

    OPEN tableCursor

    FETCH NEXT FROM tableCursor INTO @ID, @schemaName, @tableName, @destinationViewName, @createView, @forceReprocess

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @destinationViewName IS NULL OR @destinationViewName = ''
        BEGIN
            SET @destinationViewName = @tableName
            UPDATE LogDB.dbo.tabListing
            SET view_name = @destinationViewName
            WHERE ID = @ID
        END
        BEGIN TRANSACTION 
        DECLARE @qualifiedViewName NVARCHAR(300) = QUOTENAME(@destinationSchemaName) + '.' + QUOTENAME(@destinationViewName)
        IF @force_reprocess = 1
        BEGIN
            DROP VIEW IF EXISTS @qualifiedViewName
        END

        IF @createView = 1 
        BEGIN
            IF NOT EXISTS (SELECT * FROM sys.views WHERE name = @destinationViewName AND SCHEMA_NAME(schema_id) = @destinationSchemaName)
            BEGIN
                EXEC createViewFromTable @tableName, @schemaName, @destinationSchemaName, @destinationViewName, @columns
            END
        END
        IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRANSACTION
            RETURN
        END
        COMMIT TRANSACTION
        FETCH NEXT FROM tableCursor INTO @ID, @schemaName, @tableName, @destinationViewName, @createView, @forceReprocess
    END

    CLOSE tableCursor
    DEALLOCATE tableCursor
END


/* 
USER DEFINED TRIGGER: trg_TableCreated
AUTHOR: BDC 10/9/23
DESCRIPTION: This trigger is used to log the creation of a new table in a database. It inserts a new row into the 
    LogDB.dbo.tabListing table with information about the new table, including the schema name, table name, and database name. 
    If the table has been previously deleted, the trigger updates the corresponding row in the LogDB.dbo.tabListing table to indicate
    that the table is no longer deleted.

NOTES: 
- The LogDB.dbo.tabListing table must exist and have the following columns: ID (int), schemaName (nvarchar(50)), tableName (nvarchar(250)), 
  destinationViewName (nvarchar(255)), createView (bit), forceReprocess (bit), isDeleted (bit), databaseName (nvarchar(128)).
*/
CREATE OR ALTER TRIGGER [trg_TableCreated]
ON ALL SERVER
FOR CREATE_TABLE
AS
BEGIN
    DECLARE @eventData XML
    SET @eventData = EVENTDATA()

    DECLARE @tableName NVARCHAR(255)
    SET @tableName = @eventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)')

    DECLARE @schemaName NVARCHAR(255)
    SET @schemaName = @eventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(255)')

    DECLARE @databaseName NVARCHAR(255)
    SET @databaseName = @eventData.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(255)')

    IF NOT EXISTS (SELECT 1 FROM LogDB.dbo.tabListing WHERE schemaName = @schemaName AND tableName = @tableName AND databaseName = @databaseName)
    BEGIN
        INSERT INTO LogDB.dbo.tabListing (schemaName, tableName, createView, forceReprocess, destinationViewName, databaseName)
        VALUES (@schemaName, @tableName, 0, 0, @tableName, @databaseName)
    END

    IF EXISTS (SELECT 1 FROM LogDB.dbo.tabListing WHERE schemaName = @schemaName AND tableName = @tableName AND databaseName = @databaseName AND isDeleted = 1)
    BEGIN
        UPDATE LogDB.dbo.tabListing
        SET isDeleted = 0, deletedOn = NULL, deletedBy = NULL
        WHERE schemaName = @schemaName AND tableName = @tableName AND databaseName = @databaseName
    END
END

/* 
USER DEFINED TRIGGER: trg_TableDeleted
AUTHOR: BDC 10/9/23
DESCRIPTION: This trigger is used to log the deletion of a table in a database. It updates the LogDB.dbo.tabListing table 
             with the details of the deleted table, including the schema name, table name, database name, deletion timestamp, 
             and the user who deleted the table.

NOTES: 
- The LogDB.dbo.tabListing table must exist.
*/
CREATE OR ALTER TRIGGER [trg_TableDeleted]
ON ALL SERVER
FOR DROP_TABLE
AS
BEGIN
    DECLARE @eventData XML
    SET @eventData = EVENTDATA()

    DECLARE @tableName NVARCHAR(255)
    SET @tableName = @eventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)')

    DECLARE @schemaName NVARCHAR(255)
    SET @schemaName = @eventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(255)')

    DECLARE @databaseName NVARCHAR(255)
    SET @databaseName = @eventData.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(255)')

    IF EXISTS (SELECT 1 FROM LogDB.dbo.tabListing WHERE schemaName = @schemaName AND tableName = @tableName AND databaseName = @databaseName)
    BEGIN
        UPDATE LogDB.dbo.tabListing
        SET isDeleted = 1, deletedOn = GETDATE(), deletedBy = SUSER_SNAME()
        WHERE schemaName = @schemaName AND tableName = @tableName AND databaseName = @databaseName
    END
END