Discovering Three or Four Part Names in SQL Server Database Code

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.

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.)

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.

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.

The post Discovering Three or Four Part Names in SQL Server Database Code appeared first on Simple Talk.

from Simple Talk https://ift.tt/2TmNIWZ
via