При работе с SQL Server важно понимать зависимости между объектами в вашей базе данных. В предыдущем совете мы обсудили, как найти зависимости объектов с помощью скриптов. Однако, эти скрипты не включали проверку зависимостей синонимов. В этом совете мы предоставим скрипты для поиска конкретных синонимов и их зависимостей, особенно в случае работы с несколькими базами данных.

Поиск синонимов с вложенными представлениями в одной базе данных

В большинстве случаев у вас будут синонимы в одной базе данных. Следующий скрипт можно использовать для поиска синонимов с вложенными представлениями в одной базе данных:

DECLARE @schema SYSNAME

-- найти схему пользователя по умолчанию для объектов с одной частью имени (без имени схемы)
SELECT @schema = default_schema_name FROM sys.database_principals WHERE [name] = user_name();

-- найти все представления с их зависимостями
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
  -- закомментируйте следующую строку, если вам нужно проверить все типы объектов, а не только представления 
 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
)
,
-- найти все синонимы в ТЕКУЩЕЙ базе данных
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]
-- закомментируйте следующую строку, если вы хотите видеть все зависимые объекты синонимов независимо от уровня вложенности
HAVING MAX(NestLevel) > 2 
ORDER BY MAX(NestLevel) DESC;

Результат этого скрипта покажет синонимы, связанные с вложенными представлениями в одной базе данных.

Поиск синонимов для вложенных объектов в разных базах данных

Если вам нужно найти синонимы для вложенных объектов в другой базе данных, вы можете использовать следующий запрос:

SET NOCOUNT ON;

-- проверить, есть ли в базе данных синонимы для объектов в другой базе данных
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  

 -- получить список синонимов для объектов в другой базе данных
 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
-- закомментируйте следующую строку, если вы хотите видеть все зависимые объекты синонимов независимо от уровня вложенности
HAVING MAX(nest_level) > 2; 

DROP TABLE