Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / Languages / SQL

SMO Scripting Based on Dependency

4.75/5 (4 votes)
17 Feb 2020CPOL1 min read 23.1K  
Prepare script for specific objects
This tip aims to provide dependency based script generate

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.

VB.NET
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:

VB.NET
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:

VB.NET
depnd.Urn.GetAttribute("Name") 

History

  • 20th May, 2014: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)