I am in the middle of a project to enable our corporate databases to work with continuous integration using RedGate SQL Automation (and hopefully get a few blogs/articles out of it too.) One of the things that the tool does is create your database in multiple ways, with multiple names, in order to compare to existing databases and generate a deployment package. The ability to generate your empty database on a server with no other databases is a goal that simplifies this process (and simplifies your test processes as well).
In order to make this work, one of the considerations is to eliminate cross database dependencies, as you can’t reference objects that don’t exist in views, and even in stored procedures, which offer delayed resolution of objects, you can’t test the code without the database it is referencing.
In addition, and somewhat more important to the process, is dealing with three part names that reference the name of the database your object is in. During the comparison process, the database can be created with a name that is different from your target database to compare to (referred to as a shadow database.) So if you are in database X and have references to X.schema.table, but the database is generated as X_Shadow, the X. is now a cross database reference rather than the local reference you are desiring.
Four part names to linked servers are a different sort of nightmare, but one that is (hopefully) exceedingly rare. The queries presented will help with this as well.
Some of our databases have cross database dependencies by design. For example, a staging database may be accessible from loading code in the base database. Or we may have added a bolt on database, like a database of views or user space that we allow cross references to. In all cases however, we need to know what those dependencies are so we can handle them, and ferret out invalid ones.
To illustrate, I will use the following database with a set of references to the WideWorldImporters database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
CREATE DATABASE TestDependencies GO USE TestDependencies GO CREATE SCHEMA Demo; GO --view that accesses WideWorldImporters CREATE VIEW Demo.WWCities AS SELECT Cities.CityID, Cities.CityName FROM WideWorldImporters.Application.Cities GO --view with local three part name. Bad practice CREATE VIEW Demo.WWCityCall AS SELECT WWCities.CityId, WWCities.CityName FROM TestDependencies.Demo.WWCities GO --procedure that references a non-existant db CREATE OR ALTER PROCEDURE Demo.WideWorldI AS BEGIN SELECT * FROM WideWorldI.Application.Cities END GO --procedure with dynamic SQL to foreign database CREATE OR ALTER PROCEDURE Demo.DynamicSQL AS BEGIN DECLARE @query nvarchar(MAX) SET @query = 'SELECT * FROM WideWorldImporters.Application.Cities' EXEC (@query) END GO |
The following query will give you the cases of three and four part names that are being specifically used in the database. It uses the sys.sql_expression_dependencies view that will give you references to any object from any object, filtered such that the referenced database name isn’t null (if the third part of the name is null, it is a local reference, everything is awesome.)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SELECT OBJECT_SCHEMA_NAME(objects.object_id) AS referencing_schema_name, objects.name AS referencing_object_name, objects.type_desc referencing_object_type, CASE WHEN referenced_database_name = DB_NAME() AND referenced_server_name IS NULL THEN 'Internal' ELSE 'External' END AS referenced_database_location, COALESCE(sql_expression_dependencies.referenced_server_name, '<localserver>') AS referenced_server_name, sql_expression_dependencies.referenced_database_name, sql_expression_dependencies.referenced_schema_name, sql_expression_dependencies.referenced_entity_name AS referenced_object_name FROM sys.sql_expression_dependencies JOIN sys.objects ON objects.object_id = sql_expression_dependencies.referencing_id WHERE referenced_database_name IS NOT NULL ORDER BY referencing_schema_name,referencing_object_name |
You can see from the results that due to late binding of names in stored procedure objects, it doesn’t care that the database WideWorldI doesn’t exist. It just shows it as a dependency, just like it did for the real WideWorldImporters reference. A column not returned is the referenced_id column that can tell you if internal database stuff currently exists or not, but it does not capture ids of external objects, those are resolved when you execute the object.
The internal problems are easily fixed, but the external ones can be trouble for trying to build a database from a script. Our solution for generating empty databases with three part names was to designate “families” of databases that we must generate together, so if you are building database X, then you need to build XPrime first and allow cross database only from X to XPrime.
One last note, you can’t be 100% sure that you have everything due to possibility of uncompiled bits of code in dynamic SQL, as I included in one of the example bits of code. A tool you can use for this is sys.dm_db_uncontained_entities, which is meant for checking containment status of the db.
We will be interested in Dynamic SQL references, which will return as an unknown condition, because it is not possible to know what the dynamic SQL will access (and a truly stupid (or evil) programmer can make a string that cannot be searched for like ‘W’ + ‘ideW’ + ‘orldImporters.’ + ‘Application.Cities’. This could not be foun which you could never find with a simple query of the text of your objects.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT OBJECT_SCHEMA_NAME(objects.object_id) AS referencing_schema_name, objects.name AS referencing_object_name, objects.type_desc referencing_object_type, dm_db_uncontained_entities.statement_type, dm_db_uncontained_entities.feature_type_name, dm_db_uncontained_entities.feature_name, statement_line_number FROM sys.dm_db_uncontained_entities JOIN sys.objects ON dm_db_uncontained_entities.major_id = objects.object_id WHERE class_desc = 'OBJECT_OR_COLUMN' AND feature_name = 'Dynamic SQL' ORDER BY referencing_schema_name,referencing_object_name |
For our database, this returns the procedure I built with dynamic SQL, which you should make sure and check:
Note that this query may be quite slow, as it needs to comb through all of your code and check for all sort of issues in containment, and all of your code. The key here is, just how much dynamic SQL you have. If you have a lot of it, this could be a futile effort to hand check all your code. (if possible, I would highly consider you have automated tests of dynamic SQL to check for such abuses)
Assuming your programmers are not monsters, you could also consider SQL Search from Redgate, and search for the name of likely database references. Bonus tip: Dynamic SQL is a pain in many ways, and this is just one additional example.
Load comments