locked
Generate script for dependent objects RRS feed

  • Question

  • Hi All,

    I have over 10 databases in which i have to generate scripts for dependent objects of 6 six tables.  Doing it thru the SSMS is very cumbersome. 

    Is there a tool to generate script for dependent objects of a table like stored procs, view, functions ?

    Thanks

    Sunday, April 1, 2018 7:49 PM

Answers

  • Hi,

    You can try like below

    IF OBJECT_ID('tempdb..##ObjectScript') IS NOT NULL
    
    BEGIN
    DROP TABLE ##ObjectScript
    END
    CREATE TABLE ##ObjectScript (ObjectName NVARCHAR(2000))
    
    EXECUTE sp_MSforeachdb '
    IF ''?'' IN (''DB1'',''DB2'',''DB3'',''DB4'') --databases
    BEGIN
        USE [?]
    	INSERT ##ObjectScript
    	SELECT text FROM sys.syscomments
    	WHERE Id IN (
    			SELECT referenced_id 
    			FROM sys.sql_expression_dependencies D
    			JOIN sys.tables T
    			ON D.referencing_id = T.object_id
    			WHERE T.name IN (''table1'',''table2'',''table3'')) --tables
    END'
    
    SELECT * FROM ##ObjectScript
    
    


    sathya - My blog ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    • Marked as answer by GKS001 Sunday, April 1, 2018 11:36 PM
    Sunday, April 1, 2018 8:43 PM
  • In SSMS, go to Tools->Options->SQL Server Object Explorer->Scripting. In the section Object scipting options, check Generate script for dependent objects. Then in the scripting context menu, select DROP and CREATE TO. (Only CREATE TO does not seem to honour this option.)

    • Marked as answer by GKS001 Sunday, April 1, 2018 11:36 PM
    Sunday, April 1, 2018 8:44 PM

All replies

  • Hi,

    You can try like below

    IF OBJECT_ID('tempdb..##ObjectScript') IS NOT NULL
    
    BEGIN
    DROP TABLE ##ObjectScript
    END
    CREATE TABLE ##ObjectScript (ObjectName NVARCHAR(2000))
    
    EXECUTE sp_MSforeachdb '
    IF ''?'' IN (''DB1'',''DB2'',''DB3'',''DB4'') --databases
    BEGIN
        USE [?]
    	INSERT ##ObjectScript
    	SELECT text FROM sys.syscomments
    	WHERE Id IN (
    			SELECT referenced_id 
    			FROM sys.sql_expression_dependencies D
    			JOIN sys.tables T
    			ON D.referencing_id = T.object_id
    			WHERE T.name IN (''table1'',''table2'',''table3'')) --tables
    END'
    
    SELECT * FROM ##ObjectScript
    
    


    sathya - My blog ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    • Marked as answer by GKS001 Sunday, April 1, 2018 11:36 PM
    Sunday, April 1, 2018 8:43 PM
  • In SSMS, go to Tools->Options->SQL Server Object Explorer->Scripting. In the section Object scipting options, check Generate script for dependent objects. Then in the scripting context menu, select DROP and CREATE TO. (Only CREATE TO does not seem to honour this option.)

    • Marked as answer by GKS001 Sunday, April 1, 2018 11:36 PM
    Sunday, April 1, 2018 8:44 PM