Finding SQL Server Object Dependencies for Synonyms

Problem

In a previous tip we shared scripts that could be useful for finding SQL Server object dependencies during your application development or database upgrades. The scripts did not include checks for synonyms dependencies, so how can we check SQL Server synonym dependencies especially for cross database situations?

Solution

In this tip we will provide scripts to find specific synonyms and their dependencies. We are looking for the synonyms that have dependent (base) objects with high nest level (3 or more in our examples). We will find synonyms in our database that were created for the views in the same or in another database.

SQL Server Synonyms for nested views in the same database

In most cases you will have synonyms in the same database. The following script finds synonyms with nested views in the same database where the synonym has been created:

DECLARE @schema SYSNAME
-- find default user's schema for the one-part base objects names (no schema name)
SELECT @schema = default_schema_name FROM sys.database_principals WHERE [name] = user_name();
-- find all views with their dependencies
WITH DepTree 
 AS 
(
   SELECT o.[name], 
 s.[name]  AS oSchema, 
 o.[object_id] AS referenced_id , 
 o.[name] AS referenced_name, 
 o.[object_id] AS referencing_id, 
 o.[name] AS referencing_name,  
 0 AS NestLevel
  FROM  sys.objects o JOIN sys.schemas s 
 ON o.[schema_id] = s.[schema_id]
  WHERE o.is_ms_shipped = 0
  -- comment out next line if you need to check all object types, not only views 
 AND o.[type] = 'V' 
 
  UNION ALL
    
  SELECT r.[name], 
 r.oSchema, 
 d1.referenced_id,  
 OBJECT_NAME( d1.referenced_id) , 
 d1.referencing_id, 
 OBJECT_NAME( d1.referencing_id) , 
 NestLevel + 1
  FROM  sys.sql_expression_dependencies d1 JOIN DepTree r 
 ON d1.referenced_id =  r.referencing_id
)
,
-- find all synonyms in CURRENT database
Syn
 AS
(
    SELECT [name], 
 base_object_name,
 LTRIM(RTRIM( REPLACE(REPLACE(REPLACE(REPLACE(base_object_name ,'[',''),']',''), DB_NAME()+'..',''), DB_NAME()+'.',''))) as objectname
    FROM sys.synonyms s
    WHERE (base_object_name like '%.%.%'
  AND  LEFT (REPLACE(REPLACE(base_object_name ,'[',''),']',''), 
   CHARINDEX('.', REPLACE(REPLACE(base_object_name ,'[',''),']',''))-1)  = DB_NAME())
 OR base_object_name NOT LIKE '%.%.%' 
)
SELECT s.[name] AS syn_name, 
 base_object_name AS syn_base_object, 
 MAX(NestLevel) AS nest_level
FROM DepTree t JOIN Syn s
 ON oSchema + '.' + t.referencing_name  = 
   CASE WHEN s.objectname NOT LIKE '%.%' THEN @schema + '.' + s.objectname 
    ELSE s.objectname END
GROUP BY base_object_name, s.[name]
-- comment out next line if you want to see all synonyms' dependent objects regardless nest level
HAVING MAX(NestLevel) > 2 
ORDER BY MAX(NestLevel) DESC;
GO

The result shows synonyms to the nested views. Note that the synonyms in this example were created inconsistently using one-part, two-part and three-part base object names:

Query results

SQL Server Synonyms for nested objects in the different databases

This query will find synonyms for nested objects in another database:

SET NOCOUNT ON;
-- check if a database has synonyms to the objects in another database
IF EXISTS (SELECT base_object_name 
  FROM sys.synonyms
  WHERE base_object_name LIKE '%.%.%'
   AND     LEFT (REPLACE(REPLACE(base_object_name ,'[',''),']',''),
     CHARINDEX('.', REPLACE(REPLACE(base_object_name ,'[',''),']',''))-1) <> DB_NAME()
    )
BEGIN
 DECLARE @sql  NVARCHAR(MAX), 
 @syn_name  NVARCHAR(255), 
 @db  NVARCHAR(255), 
 @dbid  NVARCHAR(20),
 @objname  NVARCHAR(255)
 CREATE TABLE #tempTbl  ( syn_name   NVARCHAR(255),  
   syn_base_object  NVARCHAR(255), 
   syn_base_object_db  NVARCHAR(255),  
   nest_level  SMALLINT
       );
 DECLARE SYN_DB CURSOR FOR  
 -- get list of synonyms to the objects in another database
 SELECT DISTINCT [name] AS SYN_NAME,
  LEFT (REPLACE(REPLACE(base_object_name ,'[',''),']',''),
    CHARINDEX('.', REPLACE(REPLACE(base_object_name ,'[',''),']',''))-1) AS DBNM,
  DB_ID(LEFT (REPLACE(REPLACE(base_object_name ,'[',''),']',''),
    CHARINDEX('.', REPLACE(REPLACE(base_object_name ,'[',''),']',''))-1)) AS DBID,
  RIGHT (REPLACE(REPLACE(base_object_name ,'[',''),']',''),
    CHARINDEX('.', reverse(REPLACE(REPLACE(base_object_name ,'[',''),']','')))-1) AS objectname
FROM sys.synonyms
WHERE base_object_name LIKE '%.%.%'
  AND    LEFT (REPLACE(REPLACE(base_object_name ,'[',''),']',''),
   CHARINDEX('.', REPLACE(REPLACE(base_object_name ,'[',''),']',''))-1)  <> DB_NAME()
 OPEN SYN_DB;  
  
 FETCH NEXT FROM SYN_DB 
 INTO @syn_name, @db, @dbid, @objname;  
 WHILE @@FETCH_STATUS = 0  
 BEGIN  
 SET @sql = '
  WITH DepTree 
  AS 
 (
  SELECT ''' + @db + ''' AS DBNAME,  
   o.[name], 
   o.[object_id] AS referenced_id , 
   o.[name] AS referenced_name, 
   o.[object_id] AS referencing_id, 
   o.[name] AS referencing_name,  
   0 AS NestLevel
    FROM  [' + @db + '].sys.objects o 
  WHERE o.is_ms_shipped = 0 
  UNION ALL
    
  SELECT ''' + @db + ''' AS DBNAME, 
   r.[name], 
   d1.referenced_id,  
   OBJECT_NAME( d1.referenced_id,' + @dbid + ') , 
   d1.referencing_id, 
   OBJECT_NAME( d1.referencing_id,' + @dbid + ') , 
   NestLevel + 1
  FROM  [' + @db + '].sys.sql_expression_dependencies d1  JOIN DepTree r 
   ON d1.referenced_id =  r.referencing_id
 )
  INSERT INTO #tempTbl
   SELECT ''' + @syn_name + ''' AS syn_name, 
   ''' + @objname + ''' AS syn_base_object, 
   ''' + @db + ''' AS syn_base_object_db,  
   MAX(NestLevel) AS nest_level
   FROM DepTree 
   WHERE referencing_name = ''' + @objname + '''
   GROUP BY  referenced_name, DBNAME, referencing_name
   ORDER BY MAX(NestLevel) DESC'
  EXECUTE  (@sql);
   FETCH NEXT FROM SYN_DB
   INTO @syn_name, @db, @dbid, @objname;  
 END
 CLOSE SYN_DB;  
 DEALLOCATE SYN_DB; 
END ;
SELECT   syn_name, 
 syn_base_object, 
 syn_base_object_db, 
 MAX(nest_level) AS nest_level
FROM #tempTbl
GROUP BY syn_name, syn_base_object, syn_base_object_db
-- comment out next line if you want to see all synonyms' dependent objects regardless the nest level
HAVING MAX(nest_level) > 2; 
DROP TABLE #tempTbl;
GO

Here are the results of the query above:

Query #2 results

Comment out the line with the “HAVING” clause if you want to see synonyms and their dependent objects from another databases regardless the nest level.

Please note that we used “EXECUTE (@sql)” in this query. Use this query with dynamic SQL discretionally and make sure you are familiar with SQL Injection concepts.

Final Thoughts

In one of the previous tips we provided a template to use for the synonyms creation and explained why is it better to have three-part base objects names. Base objects can be referenced using one-part, two-part or three-part names (database_name.schema_name.object_name). Using three-part base objects names would make the scripts above much simpler.

We have created queries that handle specific scenarios. They could be used as base queries that you can modify for your needs.

Next Steps

  • Find synonyms to the nested views that might affect your database performance.
  • Document existing dependencies.
  • Read more tips about synonyms.
  • Read these tips about Dynamic SQL.
  • In this tip, there are several methods to list object dependencies that were used in previous versions of SQL Server.
  • Learn more about Dynamic Management Views and Functions and about Catalog Views.
  • Stay tuned for the Part 3 Objects Dependencies tip.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *