Get schema name for dependent objects with SMO

Using a source script component in SSIS, I am attempting to retreive details of all objec开发者_运维百科ts which depend on a table. So far, I have the object type and name but can't retreive the schema. Does anyone know how to acheive this in SMO?

My script component code is:

' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common

Public Class ScriptMain
    Inherits UserComponent

    Public Overrides Sub CreateNewOutputRows()
        '
        '
        '
        Dim TargetSQLServer As Server
        Dim TargetDatabase As Database
        Dim TargetTable As Table
        Dim uc As New UrnCollection()
        Dim dw As New DependencyWalker
        Dim dt As DependencyTree
        Dim dc As DependencyCollection
        Dim dcn As DependencyCollectionNode
        Dim sp As New Scripter
        Dim outputString As String

        TargetSQLServer = New Server("localhost")
        TargetDatabase = TargetSQLServer.Databases("AdventureWorks")


        For Each TargetTable In TargetDatabase.Tables
            ' Exclude these objects 
            If TargetTable.IsSystemObject = False Then
                uc = New UrnCollection()
                uc.Add(TargetTable.Urn)
                sp = New Scripter
                sp.Server = TargetSQLServer

                ' Get dependencies 
                dw = New DependencyWalker
                dw.Server = TargetSQLServer
                dt = dw.DiscoverDependencies(uc, DependencyType.Children)
                sp = New Scripter(TargetSQLServer)

                dc = New DependencyCollection
                dc = sp.WalkDependencies(dt)
                outputString = ""
                For Each dcn In dc
                    Me.Output0Buffer.AddRow()
                    Me.Output0Buffer.Database = TargetDatabase.Name.ToString

                    Me.Output0Buffer.Table = TargetTable.Name.ToString

                    outputString = dcn.Urn.ToString
                    Me.Output0Buffer.Dependency.AddBlobData(Text.Encoding.GetEncoding(1252).GetBytes(outputString))

                    Me.Output0Buffer.ObjectType = dcn.Urn.Type.ToString

                    outputString = dcn.Urn.GetNameForType(dcn.Urn.Type.ToString).ToString
                    Me.Output0Buffer.ObjectName.AddBlobData(Text.Encoding.GetEncoding(1252).GetBytes(outputString))

                    outputString = ""
                    Me.Output0Buffer.Schema.AddBlobData(Text.Encoding.GetEncoding(1252).GetBytes(outputString))
                Next
            End If
        Next

    End Sub

End Class

Hey ekoner, I have working code that walks the dependency tree in databases, and resolved the issue with simple string parsing.

Your urn will be returned is in the form of

///StoredProcedure[@Name='uspUpdateEmployeeHireInfo' and @Schema='HumanResources']

Just parse for @Name and then for @Schema.

Download the source code for DBSourceTools : http://dbsourcetools.codeplex.com
Have a look at DBSourceToolsLib.SysObjects.UrnParser And also DBSourceToolsLib.SysObjects.SODependencyTree for working examples.