查找DISTINCT缺少SQL依赖关系

问题描述：

我有脚本返回数据库中的所有无效依赖关系。但是这个脚本会返回许多重复项。所以我只想看到DISTINCT结果。查找DISTINCT缺少SQL依赖关系

/* 
modified version of script from http://michaeljswart.com/2009/12/find-missing-sql-dependencies/ 
Added columns for object types &amp; generated refresh module command... 
filter out user-define types: http://stackoverflow.com/questions/2330521/find-broken-objects-in-sql-server 
*/ 

SELECT TOP (100) PERCENT 
    QuoteName(OBJECT_SCHEMA_NAME(referencing_id)) + '.' + QuoteName(OBJECT_NAME(referencing_id)) AS [this Object...], 
     o.type_desc, 
    ISNULL(QuoteName(referenced_server_name) + '.', '') 
    + ISNULL(QuoteName(referenced_database_name) + '.', '') 
    + ISNULL(QuoteName(referenced_schema_name) + '.', '') 
    + QuoteName(referenced_entity_name) AS [... depends ON this missing entity name] 
    ,sed.referenced_class_desc 
    ,case when o.type_desc in('SQL_STORED_PROCEDURE' ,'SQL_SCALAR_FUNCTION' ,'SQL_TRIGGER' ,'VIEW') 
      then 'EXEC sys.sp_refreshsqlmodule ''' + QuoteName(OBJECT_SCHEMA_NAME(referencing_id)) + '.' + QuoteName(OBJECT_NAME(referencing_id)) + ''';' 
      else null 
     end as [Refresh SQL Module command] 
FROM sys.sql_expression_dependencies as sed 
LEFT JOIN sys.objects o 
      ON sed.referencing_id=o.object_id 
WHERE (is_ambiguous = 0) 
    AND (OBJECT_ID(ISNULL(QuoteName(referenced_server_name) + '.', '') 
    + ISNULL(QuoteName(referenced_database_name) + '.', '') 
    + ISNULL(QuoteName(referenced_schema_name) + '.', '') 
    + QuoteName(referenced_entity_name)) IS NULL) 
    AND NOT EXISTS 
     (SELECT * 
     FROM sys.types 
     WHERE types.name = referenced_entity_name 
     AND types.schema_id = ISNULL(SCHEMA_ID(referenced_schema_name), SCHEMA_ID('dbo')) 
     ) 
ORDER BY [this Object...], 
[... depends ON this missing entity name] 
go

这是脚本。

我试图加入GROUP BY [... depends ON this missing entity name]但我得到以下错误：

Invalid column name '... depends ON this missing entity name'.

我试图聚合函数结果添加到每一列只是为了测试，但还是同样的错误。

'SELECT DISTINCT'或'GROUP BY ISNULL（QUOTENAME（referenced_server_name）+ ''， ''） + ISNULL（QUOTENAME（referenced_database_name）+ '''，''） + ISNULL（QuoteName（referenced_schema_name）+'。'，''） + QuoteName（referenced_entity_name）' – Matt

@Matt，是的，'GROUP BY'有帮助。始终认为GROUP BY的别名应该起作用... – demo

“GROUP BY”有效吗？ – Matt

答

GROUP BY实际字段名称不是别名。

SELECT TOP (100) PERCENT 
    QuoteName(OBJECT_SCHEMA_NAME(referencing_id)) + '.' + QuoteName(OBJECT_NAME(referencing_id)) AS [this Object...], 
     o.type_desc, 
    ISNULL(QuoteName(referenced_server_name) + '.', '') 
    + ISNULL(QuoteName(referenced_database_name) + '.', '') 
    + ISNULL(QuoteName(referenced_schema_name) + '.', '') 
    + QuoteName(referenced_entity_name) AS [... depends ON this missing entity name] 
    ,sed.referenced_class_desc 
    ,case when o.type_desc in('SQL_STORED_PROCEDURE' ,'SQL_SCALAR_FUNCTION' ,'SQL_TRIGGER' ,'VIEW') 
      then 'EXEC sys.sp_refreshsqlmodule ''' + QuoteName(OBJECT_SCHEMA_NAME(referencing_id)) + '.' + QuoteName(OBJECT_NAME(referencing_id)) + ''';' 
      else null 
     end as [Refresh SQL Module command] 
FROM sys.sql_expression_dependencies as sed 
LEFT JOIN sys.objects o 
      ON sed.referencing_id=o.object_id 
WHERE (is_ambiguous = 0) 
    AND (OBJECT_ID(ISNULL(QuoteName(referenced_server_name) + '.', '') 
    + ISNULL(QuoteName(referenced_database_name) + '.', '') 
    + ISNULL(QuoteName(referenced_schema_name) + '.', '') 
    + QuoteName(referenced_entity_name)) IS NULL) 
    AND NOT EXISTS 
     (SELECT * 
     FROM sys.types 
     WHERE types.name = referenced_entity_name 
     AND types.schema_id = ISNULL(SCHEMA_ID(referenced_schema_name), SCHEMA_ID('dbo')) 
     ) 
GROUP BY ISNULL(QuoteName(referenced_server_name) + '.', '') + ISNULL(QuoteName(referenced_database_name) + '.', '') + ISNULL(QuoteName(referenced_schema_name) + '.', '') + QuoteName(referenced_entity_name) 
ORDER BY [this Object...], 
[... depends ON this missing entity name] 
go

答

您可以使用APPLY操作符访问查询中较早的别名，例如，（寻找oa.x oa.y oa.z）

SELECT 
     o.type_desc, sed.referenced_class_desc 
     , oa.x , oa.y , oa.z 
FROM sys.sql_expression_dependencies AS sed 
    LEFT JOIN sys.objects o ON sed.referencing_id = o.object_id 
    OUTER APPLY (
     SELECT 
       ISNULL(QUOTENAME(sed.referenced_server_name) + '.', '') 
      + ISNULL(QUOTENAME(sed.referenced_database_name) + '.', '') 
      + ISNULL(QUOTENAME(sed.referenced_schema_name) + '.', '') 
      + QUOTENAME(sed.referenced_entity_name)     AS x 
      , QUOTENAME(OBJECT_SCHEMA_NAME(referencing_id)) 
      + '.' 
      + QUOTENAME(OBJECT_NAME(referencing_id))     AS y 
      , CASE 
        WHEN o.type_desc IN ('SQL_STORED_PROCEDURE', 'SQL_SCALAR_FUNCTION', 'SQL_TRIGGER', 'VIEW') 
        THEN 'EXEC sys.sp_refreshsqlmodule ''' 
         + QUOTENAME(OBJECT_SCHEMA_NAME(referencing_id)) 
         + '.' + QUOTENAME(OBJECT_NAME(referencing_id)) 
         + ''';' 
        ELSE NULL 
      END              AS z 
    ) AS OA 
WHERE (is_ambiguous = 0) 
     AND (oa.x IS NULL) 
     AND NOT EXISTS (
      SELECT 
        * 
      FROM sys.types 
      WHERE types.name = referenced_entity_name 
        AND types.schema_id = ISNULL(SCHEMA_ID(referenced_schema_name), SCHEMA_ID('dbo')) 
      ) 
GROUP BY 
     o.type_desc, sed.referenced_class_desc 
     , oa.x , oa.y , oa.z 
;

查找DISTINCT缺少SQL依赖关系

相关推荐