Or "Hierarchy , History , Histery and Anarchy"

Some background.

I'm rigth now endorsed with the fascinating task of separating historical from actual data in a SQL Server 2000 , moving data from some tables , copying data from some other tables from the actual working table to a mirror database that would be used just for reference and some puntual query.

At a first glance , the table structure is fairly well done , there are lots of FK , a good deal of normalization, etc.. a piece of cake.

There are some lacks. Documentation is only three diagrams and some sparse comments in some of the more esoterical stored procedures.... we are talking here of about 710 tables , 1328 views , and 4646 stored procedures, assorted rules , functions and data definitions....rich , i'd say.

So , i need some way to make order out of all this chaos. Some prayers to Saint Google , and i have a script that will do me some good mapping the relations in the database and a query to guess what relations should be there and are not.

So , i set a migration datatbase to do some fair ass kicking with the tables and relations and start using some views that should be very familiar for the local crowd of this forum....

---------------------------------------------------------------------------------
delete from TABLAS

DECLARE @Name varchar(100), @MAS tinyint, @Menos tinyint
select  @Mas = 99, @Menos = 2

declare @str varchar(8000)

DECLARE @IDObject int, @LastID int, @NextID int

DECLARE @SUMA INT
declare nombres cursor for
SELECT
    name as nombre
FROM DESARROLLO.dbo.sysobjects
WHERE
    xtype = 'U' and
    status > 0 AND
----------
    (NOT (name LIKE N'%_1')) AND
    (NOT (name LIKE N'@%')) AND
    (NOT (name LIKE '%PDT%')) AND
    (NOT (name LIKE '%TMP')) AND
    (NOT (name LIKE '%TEMP')) AND
    (NOT (name LIKE '%COPIA%')) AND
    (NOT (name LIKE '%bak'))
----------



open nombres
fetch next from nombres into @Name
while @@fetch_status =0
begin
    CREATE TABLE #DEPENDENCIAS
        (
        Identificador int NOT NULL IDENTITY (1, 1),
        IDPadre int NULL,
        Padre varchar(100) NULL,
        IDHijo int NULL,
        Hijo varchar(100) NULL,
        NIVEL int NOT NULL
        )  ON [PRIMARY]
   
    ALTER TABLE dbo.#DEPENDENCIAS ADD CONSTRAINT
        PK_Table1 PRIMARY KEY CLUSTERED
        (
        Identificador
        ) ON [PRIMARY]
   
select @IDObject = id from sysobjects where name = @Name


insert into #DEPENDENCIAS
--SELECT NULL AS IDPadre, NULL AS Padre, ID AS IDHijo, NAME AS Hijo,0 as NIVEL
SELECT ID AS IDPadre, NAME AS Padre, NULL AS IDHijo, NULL AS Hijo,0 as NIVEL
from DESARROLLO.dbo.sysobjects where name = @Name

SELECT @LastID = 0, @NextID = 1

/* LA PARTE DE LOS PADRES*/

select @SUMA = 1

while @SUMA <= @MAS and @LastID <> @NextID
BEGIN

    insert into #DEPENDENCIAS
    select sfk.rkeyid as IDPadre, so.name as Padre,
     sfk.fkeyid as IDHijo, soh.name as Hijo,@SUMA as NIVEL
    from DESARROLLO.dbo.sysforeignkeys sfk
    inner join DESARROLLO.dbo.sysobjects so on
        sfk.rkeyid= so.id
    inner join DESARROLLO.dbo.sysobjects soh on
        soh.id = sfk.fkeyid
    where
        soh.id IN (select IDPadre from #DEPENDENCIAS where Identificador > @LastID   )
        and  soh.id  <> so.id

--        soh.id IN (select IDPadre from #DEPENDENCIAS where Identificador > @LastID or Identificador = @LastID  ) and
--        not exists (select 1 from #DEPENDENCIAS where #DEPENDENCIAS.IDHijo  = soh.id  and #DEPENDENCIAS.IDPadre = so.id  )

    SET @SUMA = @SUMA + 1
    set @LastID = @NextID
    select @NextID = IDENT_CURRENT('#DEPENDENCIAS')

END

insert into TABLAS (Nombre,Orden)
    select @Name as Nombre , max(NIVEL) as Orden
from #DEPENDENCIAS

drop TABLE #DEPENDENCIAS

fetch next from nombres into @Name
end
close nombres
deallocate nombres
----------------------------------------------------------------

Yup , i'm using cursors , i'll burn in hell for a thousand million years , but it works...sort of.

This little baby gives me an ordered table of the database tables and its level of dependency. So i could easily create scripts to insert data from the level 0 tables into the destination database without having any FK disturbed , then go up to level 1 and so on.

After level 9 , all the tables of the database are pased to the historical database , except the data i filter out in the script , so i can go from level 9 to level 0 deleting all the clutter in the working database and leaving a shinny skinny data corps after me, not killing any innocent bystander FK in the process.

Too easy.

There are two problems with this approach and i'm having real problems devising an strategy to dig me out of the hole.

problem #1 : Self referencing tables. They do not increase their count level or they count too much (depending on the commented out lines). And i don't see how i can de-reference them and leave the tables in a coherent state.

problem #2 : triangular (or polygonal ) indirect references.
 Table Orders , table OrderLines and table OrderLineDetails have a menage-a-trois as follows

OrderLineDetalis have a foreing key on OrderLines AND a foreing key on Orders.
OrderLines has a FK on Orders

In an ideal world , that would leave Order with level 4 , OrderLines on level 5 and OrderLineDetails on Level 6. This scripts stubbornly leaves OrderLineDetails at level 5 , no matter what.

Ok , coffe time is up , I'm going back to the trenches....wish me luck. I'll try to come back alive ;)

Any comment or help would be very much appreciated.

---

Hey , sysadm , leave the users alone
They are nothing more
than another brick in the firewall

Sorry I'm so long getting to this one. All you have to do is to download SQL Compare and SQL Data compare from Nice Red-Gate who pay for this site, and all your problems will be solved as these two tools will do it all for you. You will have 14 days to do the job!

Thank you for your kind answer. I'm already a pair of steps ahead, did manually put the correct order in the dozen or so tables with problems and soldiered on.

Now the problem comes from another angle. The bussines rules were changing along the times so i can not have confidence in the current rules for past or even current data. I have figured out all the correct order of inserts in the database , and decided how to go about the partition. A pity sqlserver 2000 has not partition functions , so i'm doing all this "old school" , by hand. An automatic horizontal partition tool that worked on this database would be worth millions ;)

Anyway i have decided on how to proceed with this behemoth. It will be messy and ugly , but the end result will be satisfactory. At some point i'll be using DMO/SMO so I'll be keeping an eye out for some of your awesome articles

Keep up

---

Hey , sysadm , leave the users alone
They are nothing more
than another brick in the firewall

Since i have now a little spare time , i'll like to share my findings, as i'm sure more fellows here can have some benefit from the research.

Althougth RedGate products are , put it simply , awesome , they were not quite what i was looking for. Neverless , i have some of them tagged for adquisition in the next budget i had a say on.

This .NET class is all i needed , with some tweaks to do the dirty job. Here it goes , for the benefit of the community , with the credits for the original poster/forum i found the base. I assume the poster of the code is the original author , so i credit him/her. If the original poster borrowed it from other site without giving credit and you know the source , please , let me know.




Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Imports System.Collections.Specialized
''' <summary>
''' Javier Rodríguez 11/Agosto/2008
''' Clase basada en el codigo de ejemplo de Andrea Montanari
''' http://www.devnewsgroups.net/group/microsoft.public.sqlserver.tools/topic53653.aspx
''' Devuelve en la propiedad Relaciones un arraylist de las dependencias de la tabla que le pasamos como inicio
''' Si la recorremos en orden directo , tenemos el orden en el que debemos borrar elementos
''' Si la recorremos en orden inverso , tenemos el orden en el que debemos insertar elementos.
''' </summary>
''' <remarks>
''' Class based in the code from  Andrea Montanari
''' http://www.devnewsgroups.net/group/microsoft.public.sqlserver.tools/topic53653.aspx
''' Creates an object with an arraylist property "Relaciones" where are stored the unique dependencies of the
''' start table.
''' If we walk the index , we have the order in wich elements can be deleted from the tables
''' If we walk the index in reverse , we have the order in wich elements can be inserted in the tables.
''' </remarks>
Public Class cArbolDependencias
    Private p_arrlRelaciones As ArrayList
    Public ReadOnly Property Relaciones() As ArrayList
        Get
            Return Me.p_arrlRelaciones
        End Get
    End Property
    Public Sub New(ByVal strSERVER As String, ByVal BBDD As String, ByVal Login As String, ByVal pass As String, ByVal StartTable As String)
        MyBase.New()
        Me.p_arrlRelaciones = New ArrayList
        Me.Init(strSERVER, BBDD, Login, pass, StartTable)
    End Sub

    ''' <summary>
    ''' Crea el arbol de dependencias y lo recorre para actualizar la propiedad relaciones
    ''' </summary>
    ''' <param name="strSERVER"></param>
    ''' <param name="BBDD"></param>
    ''' <param name="Login"></param>
    ''' <param name="pass"></param>
    ''' <param name="StartTable">Tabla de la que queremos saber las dependencias</param>
    ''' <remarks></remarks>
    Private Sub Init(ByVal strSERVER As String, ByVal BBDD As String, ByVal Login As String, ByVal pass As String, ByVal StartTable As String)
        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 = strSERVER
            .ConnectionContext.SqlExecutionModes = SqlExecutionModes.ExecuteAndCaptureSql
            .ConnectionContext.ConnectTimeout = 10
            .ConnectionContext.LoginSecure = False
            .ConnectionContext.Login = Login
            .ConnectionContext.Password = pass
            .ConnectionContext.Connect()
        End With
        Dim DbName As String = BBDD
        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(StartTable)
        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(Nothing, treeNode, Server, DbName)
            End If
        End If
        DepTree = Nothing
        DepWalker = Nothing
        Server = Nothing
    End Sub
    Private Sub NavigateTreeNode(ByVal ParenttreeNode As Microsoft.SqlServer.Management.Smo.DependencyTreeNode, ByVal treeNode As Microsoft.SqlServer.Management.Smo.DependencyTreeNode, ByVal Server As Microsoft.SqlServer.Management.Smo.Server, ByVal DbName As String)
        ' Procedimiento recursivo
        Dim bUnresolved As Boolean = False
        While Not treeNode Is Nothing
            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"
                Case Else
                    'case  "View", "StoredProcedure", "UserDefinedFunction"
                    '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
                            If Me.p_arrlRelaciones.Contains(sName) Then
                                Me.p_arrlRelaciones.Remove(sName)
                            End If
                            Me.p_arrlRelaciones.Add(sName)
                        End If
                        SqlObj = Nothing
                End Select
                If treeNode.HasChildNodes Then
                    NavigateTreeNode(treeNode, treeNode.FirstChild, 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 Class


The idea behind this is to traverse the database , finding all the references to a given table , and listing , in order of proximity , the referering tables , so i can then walk the Relaciones property in one way to generate the insert scripts and in the other to generate the delete scripts. This gives me the ability to move some data from the database respecting the referential integrity upon an beforehand agreeded value. As far as the rules and FKs are respected , this works great for me.

Hope  this helps others in the same case.

I'm sure Mr. Phil Factor could do some real work of art with this , like mapping out all the database at a glance , but i'm happy to be able to map one table out of the dependency forest

As a side note , the deepest dependency i have found so far is a chain 49 tables long

---

Hey , sysadm , leave the users alone
They are nothing more
than another brick in the firewall

This is pretty impressive. I can see that this gives you a lot more control over what you are doing. I did have a SQL Version of this that I wrote a while back. I'll see if I can find it...

I would be happy to give a shot at anything you throw my way ;)

---

Hey , sysadm , leave the users alone
They are nothing more
than another brick in the firewall

Did you try Red Gate tool SQL Dependency Tracker?  I was in the same boat you were in 356 tables, 227 views, and a multitude of stored procs.  It gave me everything I need to trace the dependencies.  However, I am a coder and love what you did.  I will definitely use this in the future for Change/Impact analysis work.

Thanks for sharing,

Jason

Tried Dependency tracker and is a wonderfull tool , but is not suited for the task, i needed something i could use in an automated process. Maybe i'm not very proficient with RG Dependency Tracker , but the kind of information i needed was not provided in a way i could use. On the other hand , it gives a nice and handy global vision of the database (not a little thing , bc after sanitacion , the database spans 704 Tables , 1865 Views , 4642 Stored Procedures , 14 rules , 43 user datatypes , and 25 user defined functions , plus the configuracion database (a whole different can of worms there). 94% of the tables has as primary key an identity colum. The design is curiously consistent but the performance is dismal under even ligth load. While i'm writting this note , the development server is doing the Hard Disk Fandango testing my first partition script suit for this database :)

Btw , the merit is to Andrea Montanari , whose code i copied and tweaked a little to suit my needs. I will refine it a little more and make it robust for general use and maybe add some functionanity more. I liked the way SMO works.

---

Hey , sysadm , leave the users alone
They are nothing more
than another brick in the firewall