THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Uri Dimant

Find dependency task again

If you have ever dealt with projects where you need to script out the objects and the recreate them on the destination database  you first always have been thinking of sys.sql_dependencies system table (if you are on SQL Server 2005 and onwards) . At our company we have a project to create script  on many client machines that have variuos versions of SQL Server Express Edition from SQL Server 2000  to SQL  Server 2008 R2. The team has automated script that creates all objects on the client  by using old syscomments table. They could not rely on sys.dependencies table because it has some critical bugs as  you are aware of. They did script out all views into a text file and run it for more than once because it is possoble that for the first run it is failed (if you have nested views.).It is very complicated script and in narrow down we can reproduce the problem as the following.

USE AdventureWorks
GO
CREATE TABLE t1 (c INT)
GO
CREATE VIEW v1
AS
SELECT c FROM v2
GO
/*

Msg 208, Level 16, State 1, Procedure v1, Line 3
Invalid object name 'v2'
*/
CREATE VIEW v2
AS
SELECT * FROM t1
--Clean up
DROP VIEW v1,v2
DROP TABLE t1

It worked well for a while but very slowely and they look for improvment. I would like to share with you very reliable and fast option developed by colleague of mine by using VB.NET. Please test it before  running on production server.

This script reads the view definition, find the level of dependecy, and then set priority to create.

Var
_dsV – Data table of views and column of “PL” – Place or priority
_dsD  - data table of dependences (view on view )
_dsC –context of views


Function
CreateDependence(_dsV.Tables(0), _dsC.Tables(0), _dsD)  - find on context  of view if exist other views
CheckChildViews(_dsV.Tables(0), _dsD, _dsD.Select(), 0) -  recursive  function which set priority of views.
On the start all view with priority 1000 (it’s don’t have dependence) . When view has some dependence then it gets priority = 0  or parent priority + 1. All child views (nested view) get higher among (parent priority +1) or its priority.
GetViews(_dsV.Tables(0), _dsC.Tables(0)) -  get all view by sort of priority descending


Private Sub CreateViews()
        Dim cldb As clDB ‘  class -  connection  to database
        Dim _dsV As DataSet
        Dim _dsD As New DataTable("DEP")
        Dim _dsC As DataSet
        lblResultSqlScript.Text = ""
        Try
            cldb = New clDB
            cldb.ConnectionString = conectionString
            Dim tmp As String = ""
            'all views
            tmp = " SELECT  ID,NAME,1000 as PL "
            tmp &= " FROM dbo.sysobjects   "
            tmp &= " WHERE xtype = 'V'   and category=0  "
            tmp &= " order by name  " & vbNewLine
            _dsV = cldb.GetDataSet(tmp)

            'context of views
            tmp = " SELECT text ,COLID,ID "
            tmp &= " FROM syscomments "
            tmp &= " ORDER BY COLID " & vbNewLine
            _dsC = cldb.GetDataSet(tmp)
 
            'depandences
            CreateDependence(_dsV.Tables(0), _dsC.Tables(0), _dsD)
 
            ClearIndex(_dsV.Tables(0), _dsD)
            CheckChildViews(_dsV.Tables(0), _dsD, _dsD.Select(), 0)
            -----GetViews(_dsV.Tables(0), _dsC.Tables(0))
           RunViews (_dsV.Tables(0), _dsC.Tables(0))
        Catch ex As System.Exception
        Finally
            cldb = Nothing
        End Try
   End Sub
 

    Private Sub CreateDependence(ByVal dsV As DataTable, ByVal dsC As DataTable, ByRef dt As DataTable)
        'create datatable
        Dim cl1 As New DataColumn("PNAME", System.Type.GetType("System.String"))
        Dim cl2 As New DataColumn("PID", System.Type.GetType("System.Int32"))
        Dim cl3 As New DataColumn("CNAME", System.Type.GetType("System.String"))
        Dim cl4 As New DataColumn("CID", System.Type.GetType("System.Int32"))
        dt.Columns.Add(cl1)
        dt.Columns.Add(cl2)
        dt.Columns.Add(cl3)
        dt.Columns.Add(cl4)
        Dim cur As Integer = -1
        Dim tmp As String = ""
 
        'load view
        Dim bs As New Text.StringBuilder
        For Each ddr As DataRow In dsV.Select("", "PL DESC")
            Try
                bs.Length = 0
                For Each dr As DataRow In dsC.Select("ID=" & ddr("ID"), "COLID")
                    Try
                        bs.Append(dr("TEXT").ToString)
                    Catch ex As System.Exception
                    End Try
                Next
                'find dependence
                'loop on all view without current
                For Each ddr1 As DataRow In dsV.Select("ID<>" & ddr("ID"), "PL DESC")
                    cur = bs.ToString.IndexOf(ddr1("NAME").ToString)
                    If cur > -1 Then
                        'check if it's real name and not peace of name
                        tmp = bs.ToString.Substring(cur + ddr1("NAME").ToString.Length, 1)
                        If tmp = "" Or tmp = "." Or tmp = " " Or Asc(tmp) = 13 Then
                            'add to dependence
                            Dim newdr As DataRow = dt.NewRow
                            newdr("PNAME") = ddr("NAME")
                            newdr("PID") = ddr("ID")
                            newdr("CNAME") = ddr1("NAME")
                            newdr("CID") = ddr1("ID")
                            dt.Rows.Add(newdr)
                        End If
                    End If
                Next
            Catch ex As System.Exception
            End Try
        Next
    End Sub
    Private Sub GetViews(ByVal dsV As DataTable, ByVal dsC As DataTable)

        txtSQLScript.Text = ""
        Dim bs As New Text.StringBuilder
        bs.Append(" SET QUOTED_IDENTIFIER OFF    " & vbNewLine)
        bs.Append(" GO " & vbNewLine)
        bs.Append(" SET ANSI_NULLS OFF    " & vbNewLine)
        bs.Append(" GO " & vbNewLine)
        For Each ddr As DataRow In dsV.Select("", "PL DESC")
            bs.Append(" IF EXISTS (select * from dbo.sysobjects where id = object_id(N'" & ddr("NAME") & "') and OBJECTPROPERTY(id, N'IsView') = 1)" & vbNewLine)
            bs.Append(" DROP VIEW " & ddr("NAME") & vbNewLine)
            bs.Append(" GO " & vbNewLine)
 
            For Each dr As DataRow In dsC.Select("ID=" & ddr("ID"))
                Try
                    bs.Append(dr("TEXT").ToString)
 
                Catch ex As System.Exception
                End Try
            Next
            bs.Append(vbNewLine & " GO " & vbNewLine)
            bs.Append(vbNewLine & "--------------------------------" & vbNewLine)
        Next
        txtSQLScript.Text = bs.ToString
    End Sub
    Private Sub ClearIndex(ByRef dsV As DataTable, ByVal dsd As DataTable)
        For Each ddr As DataRow In dsV.select
            If dsd.Select("PID=" & ddr("ID").ToString).Length > 0 Then
                ddr("PL") = 0
            End If
        Next
    End Sub
 
    Private Sub CheckChildViews(ByRef dsV As DataTable, _
                        ByVal dsd As DataTable, _
                        ByVal dr() As DataRow, ByVal index As Integer)
        For Each ddr As DataRow In dr
            Try
                Dim pd As DataRow = dsV.Select("ID=" & ddr("PID").ToString)(0)
                If pd("PL") < index Then pd("PL") = index
                CheckChildViews(dsV, dsd, dsd.Select("PID=" & ddr("CID").ToString), index + 1)
            Catch ex As System.Exception
            End Try
        Next
    End Sub

Private Sub RunViews(ByVal dsV As DataTable, ByVal dsC As DataTable)
        txtSQLScript.Text = ""
        Dim bs As New Text.StringBuilder
        Dim cldb As New dbAdministration.clDB
        For Each ddr As DataRow In dsV.Select("", "PL DESC")
            bs.Length = 0
            bs.Append(" IF EXISTS (select * from dbo.sysobjects where id = object_id(N'" & ddr("NAME") & "') and OBJECTPROPERTY(id, N'IsView') = 1)" & vbNewLine)
            bs.Append(" DROP VIEW " & ddr("NAME") & vbNewLine)
            cldb.Execute(bs.ToString()) 'run delete if exist view
 
            bs.Length = 0
         
           For Each dr As DataRow In dsC.Select("ID=" & ddr("ID"))
                Try
                    bs.Append(dr("TEXT").ToString)
                Catch ex As System.Exception
                End Try
            Next
            cldb.Execute(bs.ToString()) 'run create view
        Next
    End Sub
 

Published Tuesday, July 13, 2010 4:55 AM by Uri Dimant

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Alexander Kuznetsov said:

Hi Uri,

Did you try out SQL Compare? Apparently it has solved all such problems for my team.

July 13, 2010 10:52 AM
 

Uri Dimant said:

Sasha

This automated script (create objects) run by the clients. We do not have an access on their machines...I also have SQL Compare tool but using it for the servers I have an access to.

July 13, 2010 11:50 PM
 

Alex K said:

Uri,

SQL Compare functionality also comes as a dll. We were using that dll in some cases for automated comparisons.

July 14, 2010 10:11 PM
 

Uri Liebeskind said:

Hi Uri,

may I ask you a question regarding MSSQL backup? OK, I just do it... ;-)

We use Netabckup to save "MSSQL Dumps" to tape. Would we have any advantages backing up our MSSQL DBs directly using the Netbackup DB Agent?

Thank oyu and kind regards,

Uri

luri@zhaw.ch

March 19, 2012 6:40 AM
 

TrackBack said:

Uri Dimant : Find dependency task again

May 23, 2013 6:44 PM
 

mouse click the next document said:

Uri Dimant : Find dependency task again

October 15, 2014 12:49 AM
 

Highly recommended Internet page said:

Uri Dimant : Find dependency task again

October 18, 2014 9:51 PM

Leave a Comment

(required) 
(required) 
Submit

About Uri Dimant

Uri Dimant
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement