How to list all database objects with no dependencies?

saso 0
Viewed 25

I would like to list all database objects in a Microsoft SQL database that have no dependencies - e.g. there are no other database objects that depend on them.

I could find this for each table in SQL Server Management Studio, but this is very time consuming (right click on a table, "Check Dependencies"):

(example of a table with no dependencies)

Example of a table with no dependencies

I'm looking how to do this programatically for all database objects in a database - in T-SQL.

tsql sql-server
1 Answers

You can use the following script to get all objects with no dependendcies.

SELECT
  schema_name = s.name,
  o.name,
FROM sys.objects o
JOIN sys.schemas s ON s.schema_id = o.schema_id
WHERE NOT EXISTS (SELECT 1
    FROM sys.objects o2
    WHERE o2.parent_object_id = o.object_id
)
  AND NOT EXISTS (SELECT 1
    FROM sys.sql_expression_dependencies sed
    WHERE sed.referenced_id = o.object_id
);

Note that the first NOT EXISTS will exclude all objects with keys, indexes or defaults. If you only want to look at procedures, functions and views, then remove that.

SELECT
  schema_name = s.name,
  o.name,
FROM sys.objects o
JOIN sys.schemas s ON s.schema_id = o.schema_id
WHERE NOT EXISTS (SELECT 1
    FROM sys.sql_expression_dependencies sed
    WHERE sed.referenced_id = o.object_id
);

Note also that dependency checking is not perfect. In particular, dynamic SQL obviously doesn't work, and it won't check application code.

But it's also not reliable for cases when the schema is not specified in the reference.

Charlieface0
Related