SMO Scripting Based on Dependency

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:

• Connecting to the instance of a SQL server Using SMO
• SMO Scripting Some Basics

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)
                'sm.ScriptObject(sb, Selectedurn, scriptwriter)
                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

• 20^th May, 2014: Initial version