Check Validasi Object di Sql Server

Kadang saat develop aplikasi, struktur table pada database sering berubah. Dan kadang sebagai developer, lupa check object-object apa yang kena impact dari perubahan table tersebut.

Nah, setelah gugling sana sini, ketemu referensi untuk check object yang tidak lagi valid di SQL Server.

SELECT *
  FROM sys.schemas AS s
  INNER JOIN sys.all_objects AS p
  ON s.[schema_id] = p.[schema_id]
  INNER JOIN sys.sql_expression_dependencies AS d
  ON p.[object_id] = d.referencing_id
  WHERE d.referenced_id IS NULL
    AND d.referenced_server_name IS NULL
    AND d.referenced_class = 1
    AND OBJECT_ID(QUOTENAME(COALESCE(d.referenced_database_name, DB_NAME()))
          + '.' + QUOTENAME(d.referenced_schema_name)
          + '.' + QUOTENAME(d.referenced_entity_name)) IS NULL;

Hasilnya kayak gini:

 

 

Also read...

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.