Introduction
Most of the time, we work in a development environment. If we want to deploy our corrections to the production environment, we usually bundle our script, We know what are the objects needed to script in our development database. What we don't know is the dependency between them.
Consider this case in my development environment. I create a New view. Using that view, I create a function. Using that function, I create a procedure. If I create only a few of the objects, then it is possible to order the script easily. What if I have more than 100 and also what happens if I have more that 5 levels of hierarchy?
Do Some Basics
Give a quick look at the following links:
Code that Does the Trick
The following snippet is used to generate scripts based on dependency and moreover, it just scripts the mentioned objects only, not all the dependent objects.
Private Sub ScriptObjects(ByVal objval() As String)
Dim Dw As New DependencyWalker
Dim db As Database = sm.Database
Dim ObjTbl As DataTable = db.EnumObjects()
Dim Selectedurn As New UrnCollection
Dim DepWalk As DependencyWalker
Dim DepTree As DependencyTree
Dim DepColl As DependencyCollection
Dim Drows() As DataRow
For Each objName As String In objval
If objName.Length = 0 Then Continue For
Drows = ObjTbl.Select("Name ='" + objName + "'")
If Drows.Length > 0 Then
Selectedurn.Add(New Urn(Drows(0)("Urn").ToString))
End If
Next
DepWalk = New DependencyWalker(db.Parent)
DepTree = DepWalk.DiscoverDependencies(Selectedurn, True)
DepColl = DepWalk.WalkDependencies(DepTree)
Dim sb As New StringBuilder
Dim scriptwriter As New Scripter
scriptwriter.Server = sm.Database.Parent
Selectedurn.Clear()
For Each depnd As DependencyCollectionNode In DepColl
Dim obName As String = depnd.Urn.GetAttribute("Name")
If objval.Contains(obName.ToUpper) Then
Selectedurn.Add(depnd.Urn)
scriptwriter.Script(Selectedurn)
Selectedurn.Clear()
End If
Next
Using Sr As New StreamWriter("GeneratedScript.sql")
Sr.WriteLine(sb)
End Using
End Sub
How to Use the Code
Just create a string arrray of object names that need to be scripted. Just call it like this:
Dim MyObjects() as string={"TestProc","TestFunc", "Testview"}
ScriptObjects(MyObjects)
Points of Interest
The following bit of code makes me happy to script the specified objects only:
depnd.Urn.GetAttribute("Name")
History
- 20th May, 2014: Initial version