Click here to Skip to main content
12,691,097 members (30,871 online)
Click here to Skip to main content
Add your own
alternative version

Stats

7.2K views
3 bookmarked
Posted

Smo Scripting Based on Dependency

, 20 Jan 2015 CPOL
Rate this:
Please Sign up or sign in to vote.
Prepare script for specific objects

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 happen if I have more that 5 levels of hierarchy

Do Some Basics

Give a quick look at the following links:

The Code that done 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") 

License

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

Share

About the Author

Thava Rajan
Team Leader Nihon Technology
India India
No Biography provided

You may also be interested in...

Pro

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170117.1 | Last Updated 20 Jan 2015
Article Copyright 2014 by Thava Rajan
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid