March 22, 2011 at 5:58 am
In a SQL Server DB, i have to find all the "Master"(Parent) tables and also build a
Hierarchical list of Paerent/Child tables. Finally i would like to traverse that Hierarchial
list from down and delete all the child table data at the end i can able to delete
the parent data also.
I have tried in one way, that is, Using system tables (like sys.objects etc) i
queried the metadata of the db (like its primary and Foreign keys). But i dont know how
to formulate the tree like structure.. Pls help in this regard.
Thank U.
March 22, 2011 at 6:24 am
actually, MS provides a stored procedure that will give you all objects(procs, views, tables, etc) in dependency order:
try this in SSMS:
EXEC sp_msdependencies @intrans = 1
if you insert the results into a temp table, you could then filter it to be just tables, just views, or use the other, alternative parameters for the proc to do the same thing
EXEC sp_msdependencies @intrans = 1 ,@objtype=8 --8 = tables
Lowell
March 22, 2011 at 7:18 am
how about
;
;
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 ;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 23, 2011 at 2:27 am
Hi Lowell and Alzdba,
Both guys gave to me an excellent way to built a hierarchy of tables.
Alzdba, your script includes a duplicates of record also. I dont know where it have defect. Pls take a look on it.
Lowell's (Microsoft's) way gave a nice solution.
Now the things get more complex. That is my situation or condition changed.After fetching the hierarchy, i'm not going to delete entire database tables from traversing the leaf node. For example, my 1st level (Master) table contains a specific column. consider, if that column value is 0 then we have to delete the record from that Master table.
But the problem is, when we delete that record, since its a master table we MUST delete the respective related records in Child tables FIRST. My question is the above Microsoft SP return a full set of hierarchy. Its fine. How we can start the delete process from the Master table? Put it in other way, how we can traverse from
the Leaf child and go to top (may be we can traverse up levels using Foreign key relations) and delete those records from starting Child to Master tables?
March 23, 2011 at 3:32 am
How we can start the delete process from the Master table? Put it in other way, how we can traverse from
the Leaf child and go to top (may be we can traverse up levels using Foreign key relations) and delete those records from starting Child to Master tables?
have a look of DELETE CASCADE option
----------
Ashish
March 23, 2011 at 5:18 am
Hi Ashish,
Thanks for ur reply. But the fact is, in my db there is no cascde delete option. (i.e., we didnt used that one or enabled that one). Is there any other way other than this?
Thanks.
March 23, 2011 at 5:57 am
you can build your tree based on the foreign keys, but the decision on what to do with rows of data containing FK references is still a business decision. Once business can decide that "data in this row , the FK should be set to null" or"this row can be deleted", you could fix the foreign keys to do that for you with the powerful CASCADE options mentioned above.
the problem is, you cannot(well.. SHOULD not) just assume that any row int eh database that has a FK can be deleted;, someone has to review the data for consistency.
for example, suppose an invoice(master record) is going to be deleted.
but if there is a payroll record which kept track of Invoices "Jack" was working on exists...and the sum of those line items, in another table, decides "Jacks" commission.
you blindly delete all child rows with a reference to that invoice...now the commissions are off, overall payroll is off, company profit is off, and noone knows why, "Jack" was paid too much and HR wants the money back, nor can they track down why he was overpaid in the first place.
this specific scenario might not be yours, but the idea is the same...deleting data due to FK constraints can have unintended consequences unless you think it through.
anyway, you seem pretty sure you want to delete all the child data, so here's another helpful hint.
another built in MS option is to run "EXEC sp_fkeys YourTableName"
that lists all the tables(and their columns) that have a FK to your table, which might be a better starting point for identifying data to delete from child tables.
Lowell
March 23, 2011 at 6:33 am
You can disable all contraint from table
EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"
delete data from table
enable all contrainst
EXEC sp_MSforeachtable @command1="ALTER TABLE ? CHECK CONSTRAINT ALL"
March 23, 2011 at 6:48 am
Hi Lowell,
Excellent Answer Lowell. You are 100% correct. So if after getting the hierarchy
with the SP "sp_msdependencies" i started from the last level. Consider the level is 10.
I have to pass the (10th level) table to the "sp_fkeys" SP and find out the Tables which meant
we have to made a recursive call. Am i correct?
Lowell, Could you able to write some line of code so that i can catch the remaining...
Thanks
March 23, 2011 at 8:41 am
ok see if this is even close to what you are looking for;
i'm assuming you need to generate the delete statements because you know a certain key must be deleted in some master table.
this generates two levels of deletes: the direct child tables, and potentially grandchildren tables;
SELECT
DeleteID = 1,
RefID = conz.referenced_object_id,
refTable = QUOTENAME(schema_name(conz.schema_id)) + '.' + QUOTENAME(object_name(conz.referenced_object_id)),
refColumn = ParentColz.name ,
childID = conz.parent_object_id,
childTable = QUOTENAME(schema_name(conz.schema_id)) + '.' + QUOTENAME(object_name(conz.parent_object_id)),
childColumn = ChildColz.name,
cmd = 'DELETE FROM ' + QUOTENAME(schema_name(conz.schema_id)) + '.' + QUOTENAME(object_name(conz.parent_object_id))
+ ' WHERE ' + ChildColz.name + ' IN (SELECT ' + ParentColz.name
+ ' FROM ' + QUOTENAME(schema_name(conz.schema_id)) + '.' + QUOTENAME(object_name(conz.referenced_object_id)) + ' WHERE ' + ParentColz.name + ' = 0 ) --change to the correct value'
INTO #Delete
FROM sys.foreign_keys conz
INNER JOIN sys.foreign_key_columns FKcolz
ON conz.object_id = FKcolz.constraint_object_id
INNER JOIN sys.columns ChildColz
ON FKcolz.parent_object_id = ChildColz.object_id
AND FKcolz.parent_column_id = ChildColz.column_id
INNER JOIN sys.columns ParentColz
ON FKcolz.referenced_object_id = ParentColz.object_id
AND FKcolz.referenced_column_id = ParentColz.column_id
WHERE conz.referenced_object_id = object_id('dbo.GMACT')
--now, potentially, these child table rows cannot be deleted if they, themseleves, have foreign keys.
--add them to the list
INSERT INTO #DELETE
SELECT
DeleteID = 2,
RefID = conz.referenced_object_id,
refTable = QUOTENAME(schema_name(conz.schema_id)) + '.' + QUOTENAME(object_name(conz.referenced_object_id)),
refColumn = ParentColz.name ,
childID = conz.parent_object_id,
childTable = QUOTENAME(schema_name(conz.schema_id)) + '.' + QUOTENAME(object_name(conz.parent_object_id)),
childColumn = ChildColz.name,
cmd = 'DELETE FROM ' + QUOTENAME(schema_name(conz.schema_id)) + '.' + QUOTENAME(object_name(conz.parent_object_id))
+ ' WHERE ' + ChildColz.name + ' IN (SELECT ' + ParentColz.name
+ ' FROM ' + QUOTENAME(schema_name(conz.schema_id)) + '.' + QUOTENAME(object_name(conz.referenced_object_id)) + ' WHERE ' + ParentColz.name + ' = 0 ) --change to the correct value'
FROM sys.foreign_keys conz
INNER JOIN sys.foreign_key_columns FKcolz
ON conz.object_id = FKcolz.constraint_object_id
INNER JOIN sys.columns ChildColz
ON FKcolz.parent_object_id = ChildColz.object_id
AND FKcolz.parent_column_id = ChildColz.column_id
INNER JOIN sys.columns ParentColz
ON FKcolz.referenced_object_id = ParentColz.object_id
AND FKcolz.referenced_column_id = ParentColz.column_id
WHERE conz.referenced_object_id IN (SELECT childID FROM #DELETE)
--how many hierarchys / how deep does the rabbit hole go?
SELECT cmd FROM #DELETE ORDER BY DELETEID DESC
Lowell
March 23, 2011 at 10:58 am
Hi lowell
thank you very much for your response. For example, if i have more than 5 levels means we have to recursive the above statement. Am i correct? Had any idea on this?
For example, if i have a master tables A and B.
A B
C D F
E G H
The above is the table hierarchy means, how can we traverse?
Master Child Grandchild
A C,D E
C,D E -
B F G,H
F G,H -
The Top-most master table can be identified by a spl column, say "SplCol". If that is
0, means we have to delete the respective columns in the child tables. This is my actual
scenario. Pls help.
March 23, 2011 at 12:22 pm
the # levels returned by any of our queries for hierarchys above can be more than the number of actual queries needed to delete from a parent table....it's very much dependandt on the data itself.
for example, suppose you have and a table TBPERSON that contains info about a person, and it appears at level "10" in the hierarchy above.
a table that refers to that person TBCHILD might appear at level 15.
you can delete from TBCHILD, and then TBPERSON in two statements, and the levels never enter into teh equation...
that's because, for example, if each of my fictitious tables have OTHER foreign keys to other lookup tables...tbstate, tbcity, tbzipcode.
those tables, when added to the total hierarchy add more levels, but do not affect the direct FK between those two tables.
that's why the last post i made is following the FK's from one table to the next level, because the levels don't really mean anything to a single table...its for the overall database if you were deleting all rows from all tables that it becomes important.
Lowell
January 4, 2012 at 11:06 pm
ALZDBA (3/22/2011)
how about
;;
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 ;
Hi ALZDBA,
can i ask question here,i am using bellow qurey to get all relationship tables and i am getting all the relation ship tables also now what i wan to do is i wanto get reference level of all the tables which tables are i am getting from bellow qurey. My problem is i tried some ways to get that refernce level but i am not sucessed so please could you help me to get that
DECLARE @TABLENAME VARCHAR(100)='PROJECTDOCUMENT';
with Ancestors_cte (CHILD,PARENT)
as
(
select OBJECT_NAME(f.parent_object_id)as child,OBJECT_NAME(f.referenced_object_id)as parent
from sys.foreign_keys f where OBJECT_NAME(f.referenced_object_id)=@TABLENAME
UNION ALL
select OBJECT_NAME(f.parent_object_id)as child,OBJECT_NAME(f.referenced_object_id)as parent
from sys.foreign_keys f join Ancestors_cte a on a.CHILD=OBJECT_NAME(f.referenced_object_id))
,
parent_CTE(CHILD,PARENT)
AS
(
select CHILD,PARENT
from Ancestors_cte
UNION ALL
select OBJECT_NAME(f.parent_object_id)as child,OBJECT_NAME(f.referenced_object_id)as parent
from sys.foreign_keys f join parent_CTE p on p.PARENT=OBJECT_NAME(f.parent_object_id))
,
parent_child_CTE(CHILD,PARENT)
AS
(
select CHILD,PARENT
from parent_CTE
UNION ALL
select OBJECT_NAME(f.parent_object_id)as child,OBJECT_NAME(f.referenced_object_id)as parent
from sys.foreign_keys f join parent_CTE p on p.PARENT=OBJECT_NAME(f.referenced_object_id))
,
child_subchild_CTE(CHILD,PARENT)
AS
(
select CHILD,PARENT
from parent_child_CTE
UNION ALL
select OBJECT_NAME(f.parent_object_id)as child,OBJECT_NAME(f.referenced_object_id)as parent
from sys.foreign_keys f join parent_child_CTE cs on cs.CHILD=OBJECT_NAME(f.referenced_object_id))
,
sp_table_parent_CTE (CHILD,PARENT)
as
(
select OBJECT_NAME(f.parent_object_id)as child,OBJECT_NAME(f.referenced_object_id)as parent
from sys.foreign_keys f where OBJECT_NAME(f.parent_object_id)=@TABLENAME
UNION ALL
select OBJECT_NAME(f.parent_object_id)as child,OBJECT_NAME(f.referenced_object_id)as parent
from sys.foreign_keys f join sp_table_parent_CTE sp on sp.PARENT=OBJECT_NAME(f.parent_object_id))
,
sp_table_parent_child_CTE(CHILD,PARENT)
AS
(
select CHILD,PARENT
from sp_table_parent_CTE
UNION ALL
select OBJECT_NAME(f.parent_object_id)as child,OBJECT_NAME(f.referenced_object_id)as parent
from sys.foreign_keys f join sp_table_parent_CTE spcp on spcp.PARENT=OBJECT_NAME(f.referenced_object_id))
,
sp_table_child_CTE (CHILD,PARENT)
as
(
select CHILD,PARENT
from sp_table_parent_CTE
UNION ALL
select OBJECT_NAME(f.parent_object_id)as child,OBJECT_NAME(f.referenced_object_id)as parent
from sys.foreign_keys f join sp_table_parent_child_CTE spcp on spcp.CHILD=OBJECT_NAME(f.referenced_object_id))
,
sp_table_child_parent_CTE (CHILD,PARENT)
as
(
select CHILD,PARENT
from sp_table_child_CTE
UNION ALL
select OBJECT_NAME(f.parent_object_id)as child,OBJECT_NAME(f.referenced_object_id)as parent
from sys.foreign_keys f join sp_table_child_CTE spc on spc.CHILD=OBJECT_NAME(f.parent_object_id))
select * into #result from Ancestors_cte
union
select * from parent_CTE
union
select * from parent_child_CTE
union
select * from child_subchild_CTE
union
select * from sp_table_parent_CTE
union
select * from sp_table_parent_child_CTE
union
select * from sp_table_child_CTE
union
select * from sp_table_child_parent_CTE
OPTION (MAXRECURSION 0)
January 6, 2012 at 2:29 am
manju3606 (1/4/2012)
Hi ALZDBA,
can i ask question here,i am using bellow qurey to get all relationship tables and i am getting all the relation ship tables also now what i wan to do is i wanto get reference level of all the tables which tables are i am getting from bellow qurey. My problem is i tried some ways to get that refernce level but i am not sucessed so please could you help me to get that
...
The way I troubleshoot that kind of queries is I add a column and provide trail info.
e.g.
with Ancestors_cte
as (
select OBJECT_NAME(f.parent_object_id) as child
, OBJECT_NAME(f.referenced_object_id) as parent
, 0 as ReferenceLevel
from sys.foreign_keys f
where OBJECT_NAME(f.referenced_object_id) = @TABLENAME
UNION ALL
select OBJECT_NAME(f.parent_object_id) as child
, OBJECT_NAME(f.referenced_object_id) as parent
, 1
from sys.foreign_keys f
join Ancestors_cte a
on a.CHILD = OBJECT_NAME(f.referenced_object_id)
) ,
parent_CTE
AS (
select CHILD
, PARENT
, ancestors_cte.ReferenceLevel
from Ancestors_cte
UNION ALL
select OBJECT_NAME(f.parent_object_id) as child
, OBJECT_NAME(f.referenced_object_id) as parent
, 2
from sys.foreign_keys f
join parent_CTE p
on p.PARENT = OBJECT_NAME(f.parent_object_id)
) ,
This way you can discover where the data comes from and optimize your query from there on.
btw please don't use object names to join or link your data !
use the object_id info !!
The reason is when you use object_name, you _must_ use object_schema_name as well !!
Object_id is the unique identity key for an object and links to the combination "schemaname.objectname"
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 23, 2013 at 3:14 pm
Is it possible to get insert stmt using similar logic?
Like if I want to transfer data (from prod to test env) for a given AccountID from Accounts table.. I should be able to pull all child tables in the hierarchy..
Thanks
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply