Tuesday, July 13, 2010

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

13 comments:

淑娟淑娟淑娟 said...

世間事沒有一樣沒有困難,只要有信心去做,至少可以做出一些成績。..................................................

黃錢靜怡慧婷 said...

It is easier to get than to keep it.......................................................................

夏瓊陳詩蓁富 said...

Knowledge is a treasure, but practice is the key to it.................................................

劉智陳建霞堯 said...

拒絕冒險和成長的人,終將被生命的潮流陶汰。..................................................

家唐銘 said...

我的痛苦會停止,但求我的心能征服它。................................................

雅佳謙筑 said...

知識可以傳授,智慧卻不行。每個人必須成為他自己。. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

文王廷 said...

唯有用熱情、用智慧去觀察事物,這事物才會把他的秘密,洩漏給我們......................................................................

文王廷 said...

快樂,是享受工作過程的結果......................................................................

熙筠銘筠銘筠銘辰 said...

與人相處不妨多用眼睛說話,多用嘴巴思考. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

佳張張張張燕張張張張張 said...

人不能像動物一樣活著,而應該追求知識和美德............................................................

至馬馬馬馬玄馬馬馬馬 said...

我是天山,等待一輪明月。......................................................................

孫邦柔 said...

從人生中拿走友誼,猶如從生活中移走陽光......................................................................

aiya said...

Office 2010is powerful!
Microsoft Office 2010is the best software in the world.
Microsoft wordis so great!
Office 2007makes life great!
Many people likeMicrosoft Office.
Microsoft Office 2007is welcomed by the whole world.
Office 2007 keyis available here.
Office 2007 downloadis on sale now!
Office 2007 Professionalbring me so much convenience.
Outlook 2010 make life wonderful!
Microsoft outlook is my love.
Microsoft outlook 2010 is convenient!
Windows 7 is convenient!