ApexSQL Source Control framework objects inventory (4214511)

Applies to
ApexSQL Source Control

Summary
This article gives an overview of additional database objects (called framework objects) that ApexSQL Source Control creates on initial database linking to a source control repository

Description

Dedicated development model

When linking a database using the dedicated development model, the following objects will be created:

• ApexSQL_SourceControl_DatabaseLog – system table created in a database that is selected to be linked to source control

It serves for storing all information about any changes made against the version controlled database objects. When any DDL change occurs in a database, the add-in will write the information in this table including when/who made a change, against what object, and a T-SQL statement executed prior to the change.

• ApexSQL_SourceControl_DDLTrigger – DDL trigger that is also created in a database selected to be linked and that is used to catch all DDL changes that will be written in the ApexSQL_SourceControl_DatabaseLog table.

Quick tip: The abovementioned table and database DDL trigger will be created in each database that is being linked using the dedicated model. This applies to all supported SQL Server versions.

Quick tip: Minimum permissions required for the user against a linked database in order to use the add-in in the dedicated model are: ALTER ANY DATABASE DDL TRIGGER, SELECT, INSERT, EXECUTE, UPDATE, CONNECT, CREATE TABLE, ALTER permission against dbo schema.

Shared development model

When linking a database using the shared development model, a separate database needs to be used for storing the following objects:

Tables

• Objects – is the main table that stores the information about all database objects including the status of an object (edited, checked out, or locked). The information stored in this table is used to update the object status icon in the Object Explorer pane
• Policies – stores the information about active database policies and the logging feature
• ChangeLog – stores the information about any changes made against the database objects while the logging feature is enabled
• UserMappings – stores the information about users who linked the database. When the user unlinks the database, the information for the corresponding user will be removed from the table
• ExcludedObjects - stores the information about objects that are excluded in the add-in options, under the Global settings tab. Such objects are not affected by the database policy settings and will not be version controlled

Stored procedures

• UpdateObjects – used to access the Objects table and to write the information when the status of an object is changed
• UpdateExcludedObjects – used to access the ExcludedObjects table and to write the information about objects excluded in the Global settings tab, in the add-in options

Server level DDL trigger

• ApexSQL_SourceControl_DDLTrigger – DDL trigger on a server level that is used to catch all DDL changes that will be written in the corresponding table. In addition, it reads the information from the Policies table on each DDL change, and checks if an object state follows the rule from the specified policy

Quick tip: If the SQL Server version is older than 2012 (2008, 2008R2, 2005), instead of a server level DDL trigger, a separate database DDL trigger is created in each linked database.

Quick tip: Please be careful when removing framework objects. For the shared development model especially, if someone else linked any other database from the same server, it is strongly recommended not to delete them.

/*DEDICATED DEVELOPMENT MODEL
Instead of linked_db, specify a name of a database 
linked using the dedicated model*/
USE linked_db
GO
/*for Azure SQL database 
execute just the following lines without the USE statement*/
DROP TRIGGER ApexSQL_SourceControl_DDLTrigger ON DATABASE;
DROP TABLE ApexSQL_SourceControl_DatabaseLog;


/*SHARED DEVELOPMENT MODEL

1.DROPPING DDL trigger
	1.1 If the version of SQL Server is: 
	    2005, 2008 or 2008R2
Instead of linked_db, specify a name of a database linked using the shared model*/
USE linked_db
GO
/*for Azure SQL database 
execute just the following lines without the USE statement*/

DROP TRIGGER ApexSQL_SourceControl_DDLTrigger ON DATABASE;
GO
/*	1.2 If the version of SQL Server is:
	    2012, 2014, 2016, vNext*/
USE master
GO

DROP TRIGGER ApexSQL_SourceControl_DDLTrigger ON ALL SERVER;
GO
/*2. DROPPING other framework objects

Instead of ApexSQL, specify a name of a database used to host additional objects (aka framework objects) created by ApexSQL Source Control. Leave ApexSQL database name if that one is used */
USE ApexSQL
GO

IF OBJECT_ID('UpdateExcludedObjects') IS NOT NULL
	DROP PROCEDURE ApexSQL_SourceControl.UpdateExcludedObjects;
IF OBJECT_ID('UpdateObjects') IS NOT NULL
	DROP PROCEDURE ApexSQL_SourceControl.UpdateObjects;
IF OBJECT_ID('ChangeLog') IS NOT NULL
	DROP TABLE ApexSQL_SourceControl.ChangeLog;
IF OBJECT_ID('ExcludedObjects') IS NOT NULL
	DROP TABLE ApexSQL_SourceControl.ExcludedObjects;
IF OBJECT_ID('Objects') IS NOT NULL
	DROP TABLE ApexSQL_SourceControl.Objects;
IF OBJECT_ID('Policies') IS NOT NULL
	DROP TABLE ApexSQL_SourceControl.Policies;
IF OBJECT_ID('UserMappings') IS NOT NULL
	DROP TABLE ApexSQL_SourceControl.UserMappings;

/* When using Azure SQL database only DROP statements should be used */
DROP PROCEDURE ApexSQL_SourceControl.UpdateExcludedObjects;
DROP PROCEDURE ApexSQL_SourceControl.UpdateObjects;
DROP TABLE ApexSQL_SourceControl.ChangeLog;
DROP TABLE ApexSQL_SourceControl.ExcludedObjects;
DROP TABLE ApexSQL_SourceControl.Objects;
DROP TABLE ApexSQL_SourceControl.Policies;
DROP TABLE ApexSQL_SourceControl.UserMappings;

Quick tip: In case you want to delete the entire database that keeps the framework objects, be careful that it is a database dedicated just for framework objects and that it does not contain any other information.