August 7, 2017 at 10:56 am
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
August 7, 2017 at 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.
Lowell
August 7, 2017 at 11:12 am
Lowell - Monday, August 7, 2017 11:04 AMHey 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 = 1UPDATE 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.
August 7, 2017 at 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.
August 7, 2017 at 11:28 am
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.
August 7, 2017 at 11:31 am
Phil Parkin - Monday, August 7, 2017 11:27 AMLynn 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.
August 7, 2017 at 11:33 am
Lynn Pettis - Monday, August 7, 2017 11:31 AMPhil Parkin - Monday, August 7, 2017 11:27 AMLynn 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.
August 7, 2017 at 11:38 am
Phil Parkin - Monday, August 7, 2017 11:33 AMLynn Pettis - Monday, August 7, 2017 11:31 AMPhil Parkin - Monday, August 7, 2017 11:27 AMLynn 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.
August 7, 2017 at 11:43 am
Lynn Pettis - Monday, August 7, 2017 11:38 AMPhil Parkin - Monday, August 7, 2017 11:33 AMI 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."
August 7, 2017 at 11:50 am
Phil Parkin - Monday, August 7, 2017 11:43 AMLynn Pettis - Monday, August 7, 2017 11:38 AMPhil Parkin - Monday, August 7, 2017 11:33 AMI 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.
August 7, 2017 at 12:16 pm
Lynn Pettis - Monday, August 7, 2017 11:38 AMTrust 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.
August 7, 2017 at 4:20 pm
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
August 8, 2017 at 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"
August 8, 2017 at 11:42 am
funbi - Tuesday, August 8, 2017 7:12 AMYou 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
Change is inevitable... Change for the better is not.
August 8, 2017 at 11:56 am
Jeff Moden - Tuesday, August 8, 2017 11:42 AMfunbi - Tuesday, August 8, 2017 7:12 AMYou 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