Foreign Key Heirarchy

  • Okay, first, I haven't written any real  code just test code as I am having a problem just trying to identify the logic to do what I am trying to accomplish.

    I need a query that will list all the tables in my database starting with lowest (child/grandchild/great grandchild/whatever) table in a foreign key relationship to those tables with no foreign key relationship.
    Why? So I can step through each table and truncate/delete all data without having to drop and recreate any foreign key relationships.  Using Try/Catch I can truncate or delete data with no problem.  The code I am trying to replace does this my luck.  There are a few tables with foreign key relationships in the database and the code written works simply because the tables involved at the moment just happen to sort by such that the child table comes before the parent table.  I need this code to work when that doesn't happen.

    I am also trying to use the new DMVs that aren't deprecated.  My few tests that I have trashed aren't working and the code I have found so far on the internet just show the foreign key relationships not the hierarchy that I need.  Any help, even pseudo code, would be appreciated.  I am not in a position to work on this as I will be in the secured lab most of the day running tests on production cut databases for several projects.

    Thanks

  • Hey Lynn!
    two versions for you:
    there is a built in microsoft  procedure that does this, my code is just a wrapper around that, really:
    EXEC sp_msdependencies @intrans = 1

    [code language="sql"] CREATE TABLE #MyObjectHierarchy
     (
      HID int identity(1,1) not null primary key,
      ObjectID int,
      SchemaName varchar(255),
      ObjectName varchar(255),
      ObjectType varchar(255),
      oTYPE int,
      SequenceOrder int
     )
    --our list of objects in dependancy order
    INSERT #MyObjectHierarchy (oTYPE,ObjectName,SchemaName,SequenceOrder)
      EXEC sp_msdependencies @intrans = 1

    UPDATE MyTarget
    SET MyTarget.objectID = objz.object_id,
      MyTarget.ObjectType = objz.type_desc
    FROM #MyObjectHierarchy MyTarget
    INNER JOIN sys.objects objz
    ON MyTarget.ObjectName = objz.name
    AND MyTarget.SchemaName = schema_name(objz.schema_id)
    [/code]

    the second one is a manual attempt that i did as a proof of concept;

    with  cteTbHierarchy
        as ( /* Select all table without (selfreferencing) FK */
         select distinct
            1 as LevelID
            , OBJECT_SCHEMA_NAME(Parent.object_id) as TableOwner
            , Parent.name as TableName
            , Parent.object_id as TbObjID
         from  sys.objects Parent
         left join sys.foreign_key_columns RefKey
            On RefKey.parent_object_id = Parent.object_id
             and RefKey.parent_object_id <> RefKey.referenced_object_id
             and RefKey.constraint_column_id = 1
         where  RefKey.parent_object_id is null
            and Parent.type = 'U'
            and Parent.name <> 'dtproperties'
         UNION ALL
         /* add tables that reference the anchor rows */
         SELECT H.LevelID + 1
            , OBJECT_SCHEMA_NAME(Parent.object_id) as TableOwner
            , OBJECT_NAME(Parent.object_id) as TableName
            , Parent.object_id as TbObjID
         from  sys.objects Parent
         inner join sys.foreign_key_columns RefKey
            On RefKey.parent_object_id = Parent.object_id
             and RefKey.parent_object_id <> RefKey.referenced_object_id
             and RefKey.constraint_column_id = 1
         inner join cteTbHierarchy H
            on H.TbObjID = RefKey.referenced_object_id
         where  Parent.type = 'U'
            and Parent.name <> 'dtproperties'
         )
      select *
      from  cteTbHierarchy
      order by LevelID desc -- descending order = order of row deletes
        , TableOwner
        , TableName
      Option (maxrecursion 32767)

    somewhere else i have a version that tries to work around circular references, which Entity Framework, at least in the hands of my Developers, seems to think is a good thing. If you need that, I'll dig it up.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell - Monday, August 7, 2017 11:04 AM

    Hey Lynn!
    two versions for you:
    there is a built in microsoft  procedure that does this, my code is just a wrapper around that, really:
    EXEC sp_msdependencies @intrans = 1

    [code language="sql"] CREATE TABLE #MyObjectHierarchy
     (
      HID int identity(1,1) not null primary key,
      ObjectID int,
      SchemaName varchar(255),
      ObjectName varchar(255),
      ObjectType varchar(255),
      oTYPE int,
      SequenceOrder int
     )
    --our list of objects in dependancy order
    INSERT #MyObjectHierarchy (oTYPE,ObjectName,SchemaName,SequenceOrder)
      EXEC sp_msdependencies @intrans = 1

    UPDATE MyTarget
    SET MyTarget.objectID = objz.object_id,
      MyTarget.ObjectType = objz.type_desc
    FROM #MyObjectHierarchy MyTarget
    INNER JOIN sys.objects objz
    ON MyTarget.ObjectName = objz.name
    AND MyTarget.SchemaName = schema_name(objz.schema_id)
    [/code]

    the second one is a manual attempt that i did as a proof of concept;

    with  cteTbHierarchy
        as ( /* Select all table without (selfreferencing) FK */
         select distinct
            1 as LevelID
            , OBJECT_SCHEMA_NAME(Parent.object_id) as TableOwner
            , Parent.name as TableName
            , Parent.object_id as TbObjID
         from  sys.objects Parent
         left join sys.foreign_key_columns RefKey
            On RefKey.parent_object_id = Parent.object_id
             and RefKey.parent_object_id <> RefKey.referenced_object_id
             and RefKey.constraint_column_id = 1
         where  RefKey.parent_object_id is null
            and Parent.type = 'U'
            and Parent.name <> 'dtproperties'
         UNION ALL
         /* add tables that reference the anchor rows */
         SELECT H.LevelID + 1
            , OBJECT_SCHEMA_NAME(Parent.object_id) as TableOwner
            , OBJECT_NAME(Parent.object_id) as TableName
            , Parent.object_id as TbObjID
         from  sys.objects Parent
         inner join sys.foreign_key_columns RefKey
            On RefKey.parent_object_id = Parent.object_id
             and RefKey.parent_object_id <> RefKey.referenced_object_id
             and RefKey.constraint_column_id = 1
         inner join cteTbHierarchy H
            on H.TbObjID = RefKey.referenced_object_id
         where  Parent.type = 'U'
            and Parent.name <> 'dtproperties'
         )
      select *
      from  cteTbHierarchy
      order by LevelID desc -- descending order = order of row deletes
        , TableOwner
        , TableName
      Option (maxrecursion 32767)

    somewhere else i have a version that tries to work around circular references, which Entity Framework, at least in the hands of my Developers, seems to think is a good thing. If you need that, I'll dig it up.

    Thank you for the quick response, was just getting ready to head to the lab.  I will look at this latter today.  At this time I am not sure I need to worry about circular references as there are only a few tables with foreign key references.  Having said that it would probably be a good thing to keep that in mind because who knows what the developers here will do since I don't see things until it is critical and needs to be fixed.

  • Lynn Pettis - Monday, August 7, 2017 11:12 AM

    ...
    Having said that it would probably be a good thing to keep that in mind because who knows what the developers here will do since I don't see things until it is critical and needs to be fixed.

    Have you considered instituting a peer-review process such that code does not even make it into QA without your sign-off? You might nip a few issues in the bud by doing this.
    Using Pull Requests and tools like Bitbucket help make this as painless a process as possible.


  • Okay, apparently things aren't quite ready for me in the lab.  Just tested your code and it looks like it will give me just what I need based on the current database schema.  Thanks much as I was definitely not looking in the right direct Friday and I spent several hours trying to work something out.  I guess I was making it harder than it should have been.

  • Phil Parkin - Monday, August 7, 2017 11:27 AM

    Lynn Pettis - Monday, August 7, 2017 11:12 AM

    ...
    Having said that it would probably be a good thing to keep that in mind because who knows what the developers here will do since I don't see things until it is critical and needs to be fixed.

    Have you considered instituting a peer-review process such that code does not even make it into QA without your sign-off? You might nip a few issues in the bud by doing this.
    Using Pull Requests and tools like Bitbucket help make this as painless a process as possible.

    Good luck with that.  I don't even work in the same group that does the development.  I work with the group that deploys and supports the product after it gets approved during IPAT.

  • Lynn Pettis - Monday, August 7, 2017 11:31 AM

    Phil Parkin - Monday, August 7, 2017 11:27 AM

    Lynn Pettis - Monday, August 7, 2017 11:12 AM

    ...
    Having said that it would probably be a good thing to keep that in mind because who knows what the developers here will do since I don't see things until it is critical and needs to be fixed.

    Have you considered instituting a peer-review process such that code does not even make it into QA without your sign-off? You might nip a few issues in the bud by doing this.
    Using Pull Requests and tools like Bitbucket help make this as painless a process as possible.

    Good luck with that.  I don't even work in the same group that does the development.  I work with the group that deploys and supports the product after it gets approved during IPAT.

    I didn't know that, I was only trying to offer a suggestion which you may not have considered.


  • Phil Parkin - Monday, August 7, 2017 11:33 AM

    Lynn Pettis - Monday, August 7, 2017 11:31 AM

    Phil Parkin - Monday, August 7, 2017 11:27 AM

    Lynn Pettis - Monday, August 7, 2017 11:12 AM

    ...
    Having said that it would probably be a good thing to keep that in mind because who knows what the developers here will do since I don't see things until it is critical and needs to be fixed.

    Have you considered instituting a peer-review process such that code does not even make it into QA without your sign-off? You might nip a few issues in the bud by doing this.
    Using Pull Requests and tools like Bitbucket help make this as painless a process as possible.

    Good luck with that.  I don't even work in the same group that does the development.  I work with the group that deploys and supports the product after it gets approved during IPAT.

    I didn't know that, I was only trying to offer a suggestion which you may not have considered.

    Trust me, I have considered it.  I have complained to my boss about the crap I have to fix in production because the developers refuse to even ask me for help with their SQL code and such.  He has pushed it up the chain as well, nothing happens.  It is frustrating.  The ironic part, I love working for this company.  I just wish I was better utilized.

  • Lynn Pettis - Monday, August 7, 2017 11:38 AM

    Phil Parkin - Monday, August 7, 2017 11:33 AM

    I didn't know that, I was only trying to offer a suggestion which you may not have considered.

    Trust me, I have considered it.  I have complained to my boss about the crap I have to fix in production because the developers refuse to even ask me for help with their SQL code and such.  He has pushed it up the chain as well, nothing happens.  It is frustrating.  The ironic part, I love working for this company.  I just wish I was better utilized.

    I've been in similar situations before. It requires some sort of disaster to happen (which is often an opportunity in disguise), so you can say: "If we'd implemented that process I suggested, this would not have happened."


  • Phil Parkin - Monday, August 7, 2017 11:43 AM

    Lynn Pettis - Monday, August 7, 2017 11:38 AM

    Phil Parkin - Monday, August 7, 2017 11:33 AM

    I didn't know that, I was only trying to offer a suggestion which you may not have considered.

    Trust me, I have considered it.  I have complained to my boss about the crap I have to fix in production because the developers refuse to even ask me for help with their SQL code and such.  He has pushed it up the chain as well, nothing happens.  It is frustrating.  The ironic part, I love working for this company.  I just wish I was better utilized.

    I've been in similar situations before. It requires some sort of disaster to happen (which is often an opportunity in disguise), so you can say: "If we'd implemented that process I suggested, this would not have happened."

    Unfortunately we are talking government systems here and disasters happen and nothing changes, just finger pointing and the blame game.

  • Lynn Pettis - Monday, August 7, 2017 11:38 AM

    Trust me, I have considered it.  I have complained to my boss about the crap I have to fix in production because the developers refuse to even ask me for help with their SQL code and such.  He has pushed it up the chain as well, nothing happens.  It is frustrating.  The ironic part, I love working for this company.  I just wish I was better utilized.

    I used to have the same problem where I'm at, instead of going to my boss though, I went to the development team's boss, explained to him the situation with some examples, and we got a DBA review step worked into their regular development process.  While not perfect (some developers manage to skip that step) it has greatly helped to be able to see a problem early when it's still in development or testing, instead of me just seeing the code for the first time when I'm preparing for a rollout.

  • Lynn,

    Just having a hierarchy of fk's might not be sufficient.

    For example, in case of self-referencing table you have to iterate, as many times as many levels of dependency hierarchy are created in the data.

    No matter how hard you try with metadata scripts deletion from such tables would require looping through delete statements, until the last level of data hierarchy is reached.

    _____________
    Code for TallyGenerator

  • You could also disable all your constraints and then re-enable them afterwards

    -- disable all constraints
    EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

    -- do whatever you need to do...

    -- enable all constraints
    exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

  • funbi - Tuesday, August 8, 2017 7:12 AM

    You could also disable all your constraints and then re-enable them afterwards

    -- disable all constraints
    EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

    -- do whatever you need to do...

    -- enable all constraints
    exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

    What does that buy you?  If you make a mistake, which the constraints will help prevent, you may end up with a system where the constraints cannot be re-enabled.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Tuesday, August 8, 2017 11:42 AM

    funbi - Tuesday, August 8, 2017 7:12 AM

    You could also disable all your constraints and then re-enable them afterwards

    -- disable all constraints
    EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

    -- do whatever you need to do...

    -- enable all constraints
    exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

    What does that buy you?  If you make a mistake, which the constraints will help prevent, you may end up with a system where the constraints cannot be re-enabled.

    I've done this before when taking a data dump from one environment to another, normally for testing purposes. If the OP has rights to (and needs to) delete all the data I'm sure they can run a compare to ensure the keys are synched up and correct them if not. It's easier than laboriously deleting data in the correct order anyway.

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply