1

i have this query to get all sp's and depending(nested) sp's on those sp's. I need to create all these procedures on another database . Any idea how to get them in the correct order.

select distinct  procs.NAME AS ProcedureName ,OBJDEP.NAME as DEP_ProcedureName_NAME
FROM    sysdepends
        INNER JOIN sys.objects OBJ ON sysdepends.ID = OBJ.OBJECT_ID
        INNER JOIN sys.objects OBJDEP ON sysdepends.DEPID = OBJDEP.OBJECT_ID
        inner join   sys.procedures procs  on sysdepends.id = procs.object_id
        where OBJDEP.type='P' AND OBJ.type='P'
Dan Guzman
  • 28,989
  • 2
  • 46
  • 71
Viz Krishna
  • 109
  • 8

1 Answers1

1

The trivial answer is: Backup your database Restore it TRUNCATE, DELETE, and/or DROP whatever you don't want.

The simplistic answer is: Don't try get them in the correct order; script them all as "CREATE OR ALTER" and simply run the entire set repeatedly until you get the same errors twice in a row (i.e. you're no longer reducing the # of errors each run).

This also allows you to avoid issues caused by the dependency tree being incorrect (which I've seen before), circular dependencies (A calls B, and B calls A, possibly inside IF statements), and so on.

Anti-weakpasswords
  • 1,716
  • 10
  • 13