SQL query to list all dependant entities.

Question

A SQL table has 100s of tables, SPs and functions.

I am trying to put together a sql query that will return all the dependencies of a given set of tables. Is there a way to accomplish this using SSMS without writing queries?

Updated: Simplified the question to the point.

Answer 1

In SSMS, just right click on the table and choose "View Dependencies". As far as scripting, take a look at this article.

EDIT: In SSMS, you can only see it for one. The reason why is because of the stored procedure that is run to view them only takes one database object. So to script multiple, you'd simply need to use multiple lines of EXEC sp_depends @objname = N'DATABASE.OBJECT'; for the tables/views/stored procedures/functions that you want to get dependencies for. One approach would be to use a script like the following to get the unique list of all dependent objects that will have to be included:

CREATE TABLE #dependents (obj_name nvarchar(255), obj_type nvarchar(255))

-- Do this for every primary object you're concerned with finding dependents for
INSERT INTO #dependents (obj_name, obj_type)
EXEC sp_depends @objname = N'DATABASE.OBJECT'
-- ...

SELECT DISTINCT obj_name, obj_type
FROM #dependents

DROP TABLE #dependents

Answer 2

I just blog something similar to this that might help: Knowing What to Test When Changing a SQL Server Object.

Another approach would be to right click the database and select "Tasks" and then "Generate Scripts...", check the checkbox "Script all objects in the selected database". This will give you a giant text file that you can then search.