Saturday, July 25, 2020

Tables dependency order

Understanding Dependency Listings in SQL Server

When managing SQL Server databases, identifying the dependencies between objects like tables, views, and stored procedures can be crucial for maintenance and troubleshooting. There are several ways to list these dependencies, but one simple method involves using a recursive query that joins tables and foreign keys.

SQL Query for Listing Dependencies

Here’s an SQL query that can help list table dependencies:

;
WITH a AS 
(
    SELECT 
        0 AS lvl, t.OBJECT_ID AS tblID 
    FROM [sys].tables t
    WHERE t.is_ms_shipped = 0
      AND t.OBJECT_ID NOT IN 
          (SELECT f.referenced_object_id FROM [sys].foreign_keys f)
    UNION ALL
    SELECT 
        a.lvl + 1 AS lvl, f.referenced_object_id AS tblId
    FROM a
    INNER JOIN [sys].foreign_keys f 
        ON a.tblId = f.parent_object_id 
        AND a.tblID != f.referenced_object_id
)
SELECT 
    OBJECT_SCHEMA_NAME(tblID) [schema_name],  
    OBJECT_NAME(tblId) [table_name], a.lvl
FROM a
GROUP BY tblId, a.lvl 
ORDER BY MAX(lvl), 1;

This query recursively tracks the relationships between tables using foreign keys, returning a hierarchical view of the dependency levels.

Using the sp_MSdependencies Stored Procedure

SQL Server provides a system stored procedure, sp_MSdependencies, that is particularly useful for checking the dependencies of specific objects, such as tables or views.

Here is how you can use it:

EXEC sp_MSdependencies N'Sales.Customer'

Parameters for sp_MSdependencies

  • name: The name of the object (e.g., a table or view).
  • type: A numeric value representing the type of object. Some common types include:
    • 0 (1 - UDF)
    • 3 (8 - User Table)
    • 4 (16 - Procedure)
    • 8 (256 - Trigger)
    • 12 (1024 - UDDT)

Additionally, you can specify flags that control the output:

  • 0x10000: Return multiple parent/child rows per object.
  • 0x20000: Descending return order.
  • 0x40000: Return children instead of parents.
  • 0x80000: Include the input object in the output.
  • 0x100000: Return only immediate parents/children.

This stored procedure is a powerful way to see which objects depend on a specific table, procedure, or other SQL Server objects.

Visualizing Dependencies

Here is a visual representation of the SQL Server object dependency hierarchy:

SQL Server Object Dependency

Additional Resources

To explore more on SQL Server topics, here are some useful articles:

This approach to listing dependencies helps in understanding the relationships between various SQL Server objects and is particularly useful when performing database analysis or maintenance.

at

Popular Posts