Framework for Monitoring Metadata changes

It is really important for a handshake model to exist between most upstream and downstream systems. Normally there is a mode of communication between the upstream Dev and Downstream Dev teams in order to track these changes. Now I have created a simple framework. Now rather than a pull system where the downstream pulls the meta data changes from the source, I have created a push system that the upstream will send across to the destination systems. Let us see how the framework works:
Step 1: Create a table that will store the metadata changes like addition or updation of tables and views as follows-
--Table to store Metadata changes for views and tables CREATE TABLE DB_Table_View_Changes ( DB_Table_View_Changes_ID INT IDENTITY(1,1) PRIMARY KEY ,DB_Schema_Name VARCHAR(256) ,DB_TableView_Name VARCHAR(256) ,DB_TableView_Type VARCHAR(5) ,ModifiedDate DATETIME )
Step 2: The next step is to create the table that will track the Column changes made to views or tables-
--Table to track the Column MetaData Changes CREATE TABLE DB_Column_Changes ( DB_Column_Changes_ID INT IDENTITY(1,1) PRIMARY KEY ,DB_Schema_Name VARCHAR(256) ,DB_Table_Name VARCHAR(256) ,DB_Column_Name VARCHAR(256) ,Is_Nullable varchar(3) default 'YES' ,Data_Type VARCHAR(20) ,Data_Type_Length SMALLINT ,Data_Type_Precision SMALLINT ,Data_Type_Scale SMALLINT ,ModifiedDate DATETIME ,Is_Deleted TINYINT default 0 )
Step 3: We create a table to store the list of downstream MetaData Tables (one for table/view changes and the other for the column changes) that these changes have to move to from the upstream system-
--List of downstream tables that the changes need to flow into --drop table DB_Downstream_Tables CREATE TABLE DB_Downstream_Tables ( DB_Downstream_Server VARCHAR(256) ,DB_Downstream_DB VARCHAR(256) ,DB_Downstream_Schema VARCHAR(256) --Downstream table name to store table/view changes ,DB_Downstream_TableChanges VARCHAR(256) --Downstram table name to store column changes ,DB_Downstream_ColumnChanges VARCHAR(256) -- this is to verify whether the link server for downstream tables have been created ,Link_Server_Created TINYINT default 0 ,DB_DownStream_Owner VARCHAR(100) ,DB_LOGIN VARCHAR(200) ,EmailID VARCHAR(256) )
Step 4: The next step is to write up an SP (Stored Procedure) that will create the linked servers (provided the upstream Dev team has access to a metadata repository on the downstream systems. (The reason for creating a separate SP for doing this is that we cannot wrap the "master.dbo.sp_addlinkedserver" system proc within a transaction.)- --Procedure to create linked servers from DB_Downstream_Tables --DROP PROCEDURE DB_Add_Linked_Server CREATE PROCEDURE DB_Add_Linked_Server AS BEGIN DECLARE cursorTest CURSOR FOR SELECT DB_Downstream_Server FROM DB_Downstream_Tables WHERE Link_Server_Created <> 1 DECLARE @ServerVal NVARCHAR(100) OPEN cursorTest FETCH NEXT FROM cursorTest INTO @ServerVal WHILE @@FETCH_STATUS = 0 BEGIN EXEC master.dbo.sp_addlinkedserver @server = @ServerVal, @srvproduct=N'SQL Server' ; FETCH NEXT FROM cursorTest INTO @ServerVal END CLOSE cursorTest DEALLOCATE cursorTest END
Step 5: The next step would be to create the downstream tables hosted at the destination metadata DB layer (The prior ones were all at the source level)- --Tracking the Table/view changes from the source CREATE TABLE TableChange ( DB_Schema_Name VARCHAR(256) ,DB_TableView_Name VARCHAR(256) ,DB_TableView_Type VARCHAR(5) ,ModifiedDate DATETIME ) --Tracking the column changes from the source CREATE TABLE ColumnChange ( DB_Schema_Name VARCHAR(256) ,DB_Table_Name VARCHAR(256) ,DB_Column_Name VARCHAR(256) ,Is_Nullable varchar(3) ,Data_Type VARCHAR(20) ,Data_Type_Length SMALLINT ,Data_Type_Precision SMALLINT ,Data_Type_Scale SMALLINT ,ModifiedDate DATETIME ,Is_Deleted TINYINT )
Step 6: The final step is to create the Stored proc to populate the metadata changes in the destination tables at the upstream system. Note: The procedure can be modified to be made set based rather than cursor based. Configuration of the DataBase Mail at the source level is required for the "sp_send_dbmail" task. You can also pass a query parameter to the "sp_send_dbmail" task if required. All I am trying to create is some sort of notification for the downstream owners. --DROP PROCEDURE DB_SP_Verify_Metadata_Changes --EXEC DB_SP_Verify_Metadata_Changes CREATE PROCEDURE DB_SP_Verify_Metadata_Changes AS BEGIN BEGIN TRAN BEGIN TRY DECLARE @DB_Downstream_Server VARCHAR(256) DECLARE @DB_Downstream_DB VARCHAR(256) DECLARE @DB_Downstream_Schema VARCHAR(256) DECLARE @DB_Downstream_TableChanges VARCHAR(256) DECLARE @DB_Downstream_ColumnChanges VARCHAR(256) DECLARE @Link_Server_Created TINYINT DECLARE @DB_DownStream_Owner VARCHAR(100) DECLARE @DB_LOGIN VARCHAR(200) DECLARE @EmailID VARCHAR(256) DECLARE @tablechanges INT DECLARE @columnchanges INT --Populate DB_Table_View_Changes PRINT 'Inserting into DB_Table_View_Changes' INSERT INTO DB_Table_View_Changes(DB_Schema_Name,DB_TableView_Name,DB_TableView_Type,ModifiedDate) SELECT T.Table_Schema,T.Table_Name, Case when T.Table_Type='BASE TABLE' Then 'Table' when T.Table_Type='VIEW' Then 'View' end as Table_Type,getdate() as ModifiedDateTime FROM Information_Schema.Tables T where lower(T.TABLE_SCHEMA+'.'+T.TABLE_NAME) not in (SELECT lower(ISNULL(C.DB_Schema_Name,'Test')+'.'+ISNULL(C.DB_TableView_Name,'Test')) FROM DB_Table_View_Changes C) --Populate DB_Column_Changes PRINT 'Inserting into DB_Column_Changes' INSERT INTO DB_Column_Changes ( DB_Schema_Name ,DB_Table_Name ,DB_Column_Name ,Is_Nullable ,Data_Type ,Data_Type_Length ,Data_Type_Precision ,Data_Type_Scale ,ModifiedDate ,Is_Deleted ) SELECT Table_Schema,Table_Name,Column_Name,Is_Nullable,Data_Type,Character_Maximum_Length,Numeric_Precision,Numeric_Scale,getdate(),0 FROM Information_Schema.Columns Source where lower(Source.Table_Schema+'.'+Source.Table_Name+'.'+Source.Column_Name) not in ( SELECT lower(d.DB_Schema_Name+'.'+d.DB_Table_Name+'.'+d.DB_Column_Name) FROM DB_Column_Changes d ) PRINT 'Update Table Changes' UPDATE d SET Is_Deleted = 1 ,ModifiedDate=getdate() FROM DB_Column_Changes d WHERE lower(d.DB_Schema_Name+'.'+d.DB_Table_Name+'.'+d.DB_Column_Name) not in ( SELECT lower(Source.Table_Schema+'.'+Source.Table_Name+'.'+Source.Column_Name) FROM Information_Schema.Columns Source ) PRINT 'Update Column Changes' Update d SET d.Is_Nullable=Source.Is_Nullable ,d.Data_Type = Source.Data_Type ,d.Data_Type_Length=Source.Character_Maximum_Length ,d.Data_Type_Precision = Source.Numeric_Precision ,d.Data_Type_Scale= Source.Numeric_Scale ,ModifiedDate=getdate() FROM DB_Column_Changes d, Information_Schema.Columns Source WHERE lower(d.DB_Schema_Name+'.'+d.DB_Table_Name+'.'+d.DB_Column_Name)= lower(Source.Table_Schema+'.'+Source.Table_Name+'.'+Source.Column_Name) and ( d.Is_Nullable <> Source.Is_Nullable OR d.Data_Type <> Source.Data_Type OR ISNULL(d.Data_Type_Length,0) <> ISNULL(Source.Character_Maximum_Length,0) OR ISNULL(d.Data_Type_Precision,0) <> ISNULL(Source.Numeric_Precision,0) OR ISNULL(d.Data_Type_Scale,0) <> ISNULL(Source.Numeric_Scale,0) ) SELECT @tablechanges = COUNT(1) FROM DB_Table_View_Changes d WHERE CONVERT(VARCHAR, GETDATE(), 101) = CONVERT(VARCHAR, d.ModifiedDate, 101) SELECT @columnchanges = COUNT(1) FROM DB_Column_Changes d WHERE CONVERT(VARCHAR, GETDATE(), 101) = CONVERT(VARCHAR, d.ModifiedDate, 101) print 'Table Count' + convert(varchar,@tablechanges) print 'Column Count' + convert(varchar,@columnchanges) IF (@tablechanges > 1 OR @columnchanges > 1) BEGIN PRINT 'Begin Cursor Loop' DECLARE cursorTest CURSOR FOR SELECT * FROM DB_Downstream_Tables DECLARE @INSCOLSQL NVARCHAR(MAX) DECLARE @INSTABSQL NVARCHAR(MAX) OPEN cursorTest FETCH NEXT FROM cursorTest INTO @DB_Downstream_Server ,@DB_Downstream_DB ,@DB_Downstream_Schema ,@DB_Downstream_TableChanges ,@DB_Downstream_ColumnChanges ,@Link_Server_Created ,@DB_DownStream_Owner ,@DB_LOGIN ,@EmailID WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'Insert DB_Downstream_ColumnChanges' SET @INSCOLSQL='INSERT INTO ['+@DB_Downstream_Server+'].['+@DB_Downstream_DB+'].['+@DB_Downstream_Schema+'].['+@DB_Downstream_ColumnChanges+'] ( DB_Schema_Name ,DB_Table_Name ,DB_Column_Name ,Is_Nullable ,Data_Type ,Data_Type_Length ,Data_Type_Precision ,Data_Type_Scale ,ModifiedDate ) SELECT DB_Schema_Name ,DB_Table_Name ,DB_Column_Name ,Is_Nullable ,Data_Type ,Data_Type_Length ,Data_Type_Precision ,Data_Type_Scale ,ModifiedDate FROM DB_Column_Changes d WHERE CONVERT(VARCHAR, GETDATE(), 101) = CONVERT(VARCHAR, d.ModifiedDate, 101)' PRINT @INSCOLSQL EXEC sp_executesql @INSCOLSQL PRINT 'Insert DB_Downstream_TableChanges' SET @INSTABSQL='INSERT INTO ['+@DB_Downstream_Server+'].['+@DB_Downstream_DB+'].['+@DB_Downstream_Schema+'].['+@DB_Downstream_TableChanges+'] ( DB_Schema_Name ,DB_TableView_Name ,DB_TableView_Type ,ModifiedDate ) SELECT DB_Schema_Name ,DB_TableView_Name ,DB_TableView_Type ,ModifiedDate FROM DB_Table_View_Changes d WHERE CONVERT(VARCHAR, GETDATE(), 101) = CONVERT(VARCHAR, d.ModifiedDate, 101)' PRINT @INSTABSQL EXEC sp_executesql @INSTABSQL EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQLSERVER2012', @recipients = @DB_LOGIN, @body = 'New Source Tables added by the Source Team', @subject = 'Column Changes from Source' ; EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQLSERVER2012', @recipients = @DB_LOGIN, @body = 'The new Source Column changes made by the Source Team', @subject = 'Column Changes from Source' ; FETCH NEXT FROM cursorTest INTO @DB_Downstream_Server ,@DB_Downstream_DB ,@DB_Downstream_Schema ,@DB_Downstream_TableChanges ,@DB_Downstream_ColumnChanges ,@Link_Server_Created ,@DB_DownStream_Owner ,@DB_LOGIN ,@EmailID END CLOSE cursorTest DEALLOCATE cursorTest END END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_MESSAGE() AS ErrorMessage; Declare @CursorStatX int select @CursorStatX=cursor_status('global','cursorTest') print 'CursorValX:' + Convert(varchar(5),@CursorStatX) If(@CursorStatX > 0) BEGIN CLOSE cursorTest DEALLOCATE cursorTest END ROLLBACK TRAN print @@ERROR END CATCH; COMMIT TRAN END
Populate the DB_Downstream_Tables with the required information as shown in the figure below-

The last and final step is to create a SQL agent job at the source level to run the Stored Procs in the following Order:
1. EXEC DB_Add_Linked_Server
2. EXEC DB_SP_Verify_Metadata_Changes