Hi,

Would anyone tell me this?

Would I be able to script the whole database (tables, SPs, views,...)
with one command in .NET 2005 SMO objects? I cannot seem to find this.

SQL Server 2000 has command for this and was scripting the while DB in
no time (no matter how big DB is).

I wrote a script to each and every (looping through) object which is
consuming/taking the cosiderable time.

Any help is appreciated.

hi,
Chatakondu wrote:
Show quoteHide quotethe Microsoft.SqlServer.Management.Smo.Database .Script method only returns
the DDL statements to (re)create the database but not all relative internal
objects... you can have a look at the Scripter class to add all required
objects to the DependencyWalker inherited factory..

Hi Andrea,

Would you mind to provide some examples how do I use DependencyWalker
class with Scripter object. I could not find any example code from
MSDN.

Again if I add one by one object to DependencyWalker, again no use.
Which I am already doing it.

//   For DB script
sScriptCol = sDB.Script(sScrp.Options);
foreach (string sDBScript in sScriptCol)
     sScriptFileStream.WriteLine(sDBScript + Environment.NewLine + "GO"
+ Environment.NewLine);

//   For Tables script..
foreach (Table sTB in sDBs.Tables)
{
     if (!sTB.IsSystemObject)
     {
        sScriptCol = sTB.Script(sScrp.Options);
        foreach (string sTBScript in sScriptCol)
             sScriptFileStream.WriteLine(sTBScript +
Environment.NewLine + "GO" + Environment.NewLine);
      }
}

//   For scripting views
foreach (View sView in sDBs.Views)
{
     if (!sView.IsSystemObject)
     {
          sScriptCol = sView.Script(sScrp.Options);
          foreach (string sVWScript in sScriptCol)
                sScriptFileStream.WriteLine(sVWScript +
Environment.NewLine + "GO" + Environment.NewLine);
      }
}

......... like that for all other objects..

hi
Chatakondu wrote:
actually you have to do that... enlist all objects you want/need to script
out... I do that too, as the DependencyWalker allow you to even script
objects out in the correct dependency chain too,
http://www.asql.biz/files/amScript.htm... the tool is available for free at
http://www.asql.biz/en/Download2005.aspx

Andreas,

Can you provide some sample code how to use DependencyWalker in .NET?
Any language is fine C# or VB.NET.

I doubt our team will buy any tools for this. I will have to figure
this out. Appreciate your help. Could not get much help for
DependencyWalker in MSDN.

Thanks

hi,
Chatakondu wrote:
a trivial sample that uses the DependencyTree and DependencyWalker to script
out the AdventureWork.Production.BillOfMaterials dependency chain..

Imports Microsoft.SqlServer.Management.Common
Imports Microsoft.SqlServer.Management.Smo

Module Module1

    Sub Main()

        Dim Server As Microsoft.SqlServer.Management.Smo.Server = Nothing
        Server = New Server
        With Server
            .ConnectionContext.ApplicationName =
My.Application.Info.ProductName
            .ConnectionContext.AutoDisconnectMode =
AutoDisconnectMode.NoAutoDisconnect
            .ConnectionContext.BatchSeparator = "GO"
            .ConnectionContext.ServerInstance = "(Local)"
            .ConnectionContext.SqlExecutionModes =
SqlExecutionModes.ExecuteAndCaptureSql
            .ConnectionContext.ConnectTimeout = 10
            .ConnectionContext.LoginSecure = True

            .ConnectionContext.Connect()
        End With

        Dim DbName As String = "AdventureWorks"

        Dim DepWalker As New
Microsoft.SqlServer.Management.Smo.DependencyWalker(Server)
        Dim ObjScripted(0) As
Microsoft.SqlServer.Management.Smo.SqlSmoObject
        ObjScripted(0) = Server.Databases(DbName).Tables("BillOfMaterials",
"Production")

        Dim DepTree As Microsoft.SqlServer.Management.Smo.DependencyTree =
DepWalker.DiscoverDependencies(ObjScripted, True)
        If Not DepTree Is Nothing Then
            Dim treeNode As
Microsoft.SqlServer.Management.Smo.DependencyTreeNode
            If DepTree.HasChildNodes Then
                treeNode = DepTree.FirstChild
                NavigateTreeNode(treeNode, Server, DbName)
            End If

        End If
        DepTree = Nothing
        DepWalker = Nothing
        Server = Nothing

    End Sub
    Private Sub NavigateTreeNode(ByVal treeNode As
Microsoft.SqlServer.Management.Smo.DependencyTreeNode, ByVal Server As
Microsoft.SqlServer.Management.Smo.Server, ByVal DbName As String)
        ' Recursive Procedure
        Dim sOutPut As Specialized.StringCollection = Nothing
        Dim bUnresolved As Boolean = False

        While Not treeNode Is Nothing
            Debug.WriteLine(treeNode.ToString)
            Debug.WriteLine(treeNode.Urn)
            Dim urn As Microsoft.SqlServer.Management.Smo.Urn
            urn = treeNode.Urn

            bUnresolved = False

            Dim sName As String = urn.GetAttribute("Name")
            Dim sSchema As String = urn.GetAttribute("Schema")
            Dim sType As String = urn.Type
            urn = Nothing

            Select Case sType
                Case "Table", "View", "StoredProcedure",
"UserDefinedFunction"
                Case Else
                    'Case "UserDefinedDataType"
                    'Case "UserDefinedType"
                    'Case "XmlSchemaCollection"
                    'Case "SqlAssembly"
                    'Case "UserDefinedAggregate"
                    'Case "UserDefinedFunction"
                    'Case "Rule"
                    'Case "Default"
                    'Case "Synonym"
                    'Case "PartitionScheme"
                    'Case "PartitionFunction"

                    bUnresolved = True
            End Select

            If Not bUnresolved Then

                Select Case sType
                    Case "Table"
                        Dim SqlObj As
Microsoft.SqlServer.Management.Smo.Table =
Server.Databases(DbName).Tables(sName, sSchema)
                        If Not SqlObj Is Nothing Then sOutPut =
SqlObj.Script
                        SqlObj = Nothing
                    Case "View"
                        Dim SqlObj As
Microsoft.SqlServer.Management.Smo.View =
Server.Databases(DbName).Views(sName, sSchema)
                        If Not SqlObj Is Nothing Then sOutPut =
SqlObj.Script
                        SqlObj = Nothing
                    Case "StoredProcedure"
                        Dim SqlObj As
Microsoft.SqlServer.Management.Smo.StoredProcedure =
Server.Databases(DbName).StoredProcedures(sName, sSchema)
                        If Not SqlObj Is Nothing Then sOutPut =
SqlObj.Script
                        SqlObj = Nothing
                    Case "UserDefinedFunction"
                        Dim SqlObj As
Microsoft.SqlServer.Management.Smo.UserDefinedFunction =
Server.Databases(DbName).UserDefinedFunctions(sName, sSchema)
                        If Not SqlObj Is Nothing Then sOutPut =
SqlObj.Script
                        SqlObj = Nothing
                End Select

                Console.Write(StringCollection2String(sOutPut))
                sOutPut = Nothing

                If treeNode.HasChildNodes Then
                    Dim treeSubNode As
Microsoft.SqlServer.Management.Smo.DependencyTreeNode
                    treeSubNode = treeNode.FirstChild
                    NavigateTreeNode(treeSubNode, Server, DbName)
                End If

            Else
                Console.Write(String.Format("Unresolved object: type {0} -
schema {1} - name {2}", sType, sSchema, sName))
            End If
            treeNode = treeNode.NextSibling

        End While

    End Sub
    Private Function StringCollection2String(ByVal value As
Specialized.StringCollection) As String
        Dim sBuf As String = ""
        If value.Count <> 0 Then
            For Each s As String In value
                If sBuf.Length <> 0 Then sBuf = sBuf & Environment.NewLine
                sBuf &= s
            Next
        End If
        Return sBuf
    End Function

End Module

Andrea,

Thanks much. I will convert this into C# and will try it today. You are
a life safer. I will let you know the result.

Appreciate your great help.

Regards,
Suresh Chatakondu

SQL Server 2000 - Setting EM to check status of servers
Bug in Enterprise Manager (SQL 2000)
copy database error 12291 and 208
Database Id prb !
.Working with jobs when Agent is stopped.