Script out objects that depend on in SQL Server

Question

I have a requirement wherein I need to script out the definition of all objects that depend on a single table. See the below example.

Let's say I have a table and there are 15 objects(views, SPs, Functions, triggers etc) that depend on it. Then I need definition of all those 15 objects.

This needs to be done on 15 servers. Is there a way to script all those objects using a T-SQL script? Currently I am noting all the objects down in an excel and scripting out definitions of all the objects manually which is very tedious. :(

Answer 1

It looks like you can get dependency information from many places, one of which is sys.sql_expression_dependencies.

Sample query below:

SELECT OBJECT_NAME([referencing_id]), OBJECT_DEFINITION([referencing_id])
FROM sys.sql_expression_dependencies
WHERE referenced_entity_name = 'myTable'
GROUP BY [referencing_id]

I'm not confident that this system view is sufficient for all dependencies, but hopefully this is enough to get you started until someone more knowledgeable chimes in.

More info can easily be found by Googling "sql dependencies." E.g. https://stackoverflow.com/questions/22005698/how-to-find-all-the-dependencies-of-a-table-in-sql-server

Answer 2

In SSMS...

1. Rt Click on the Database in question, navigate to Tasks and then Generate Scripts...
2. Select Choose Objects on the left and then click the radio button to Select specific database objects
3. Select the object you wish to script
4. In Save to file click the Advanced button
5. 6th option down, set Generate Script for Dependent Objects to true
6. Set any other options you want and click the OK button
7. Set the File name and path or Save to Clipboard or Save to new query window
8. Click the Next buttons to finish.