March 6, 2017 at 9:30 am
OK, so here is what I need
I need a listing of tables from any database.
( Lets assume there is only one schema in the DB and that is dbo )
So the following will work
Select name from sys.tables order by 1
However I need it in a certain order.
1. When table A has a foriegn key that POINTS to TABLE B, then
B must get listed first and so on..
Now Table A may have multiple foreign keys pointing at TAB B and TAB C
So B and C must get listed first.
Also B may have a foriegn key to C. So then the order is
C
B
A
I am sure you get the idea here....
So then help me write the query....
?
March 6, 2017 at 9:39 am
There are scripts out there that will help you with stuff like this. Search for something like "query for tables in dependency order".
John
March 6, 2017 at 9:50 am
;
;
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 ;
Found this .. It may work...
March 6, 2017 at 10:39 am
Next.. challenge.. Canyou help me tweek this ?
I just need the SELECT statement to list only the TABLE name in the same order as it does now. I don't need the LEVEL ID and the TableName to repeat.
March 6, 2017 at 10:41 am
Having several foreign keys in a table is like having multiple parent inheritance. There may be many-to-many relationships where many referencing tables are linked to many referenced tables.
The query in the previous post gives me an error for exceeding 100 CTE recursion levels. On a small database I don't get the error but I notice the same table is listed many times at many levels. I conclude that the query is incorrect.
I have a solution that procedurally walks through each table and assigns it a level. For example, my big database of 500 tables assigns each table to one of 17 levels. All tables at one level must contain rows before all tables at the next level may have rows inserted assuming the foreign key columns are not null. In my case a table at the 17th level must have rows in at least one table from each of the previous 16 levels. Thus are the dependencies.
March 6, 2017 at 11:41 am
Agree it works for a small db.
Any way that you could show me how to tweek it so that I only get one column of output and the tables listed in the same order please ?
March 6, 2017 at 1:36 pm
Here is something to get you started. It doesn't find complex circular refs but you can remove those if you know what they are.
-- Get tables and sort in data entry order.
DECLARE @i int;
SET @i = 0;
DECLARE @t table (TableName varchar(128) NOT NULL, FillLevel int NOT NULL);
INSERT into @t
SELECT name, 0
FROM sys.tables t
ORDER by t.name
;
DECLARE @r table (Referenced varchar(128) NOT NULL, Referencing varchar(128) NOT NULL);
INSERT into @r
SELECT distinct
OBJECT_NAME(fk.referenced_object_id) AS Parent,
OBJECT_NAME(fk.parent_object_id) AS Child
FROM sys.foreign_keys fk
JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
JOIN sys.columns c ON fkc.parent_object_id = c.object_id AND fkc.parent_column_id = c.column_id
ORDER BY OBJECT_NAME(fk.parent_object_id),OBJECT_NAME(fk.referenced_object_id)
;
DELETE from @r where Referenced = Referencing; -- circular tables
DELETE from @r where Referenced = 'Users' AND Referencing = 'Parties'; -- other circ refs
WHILE @i < 20
BEGIN
UPDATE t2
SET fillLevel = @i + 1
FROM @t t1
JOIN @r r on t1.TableName = r.Referenced
JOIN @t t2 on r.Referencing = t2.TableName
WHERE t1.FillLevel = @i
;
SET @i = @i + 1;
END
SELECT * FROM @t order by FillLevel, TableName;
SELECT * FROM @r;
GO
March 6, 2017 at 1:49 pm
Bill Talada - Monday, March 6, 2017 1:36 PMHere is something to get you started. It doesn't find complex circular refs but you can remove those if you know what they are.
-- Get tables and sort in data entry order.DECLARE @i int;
SET @i = 0;
DECLARE @t table (TableName varchar(128) NOT NULL, FillLevel int NOT NULL);
INSERT into @t
SELECT name, 0
FROM sys.tables t
ORDER by t.name
;DECLARE @r table (Referenced varchar(128) NOT NULL, Referencing varchar(128) NOT NULL);
INSERT into @r
SELECT distinct
OBJECT_NAME(fk.referenced_object_id) AS Parent,
OBJECT_NAME(fk.parent_object_id) AS Child
FROM sys.foreign_keys fk
JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
JOIN sys.columns c ON fkc.parent_object_id = c.object_id AND fkc.parent_column_id = c.column_id
ORDER BY OBJECT_NAME(fk.parent_object_id),OBJECT_NAME(fk.referenced_object_id)
;DELETE from @r where Referenced = Referencing; -- circular tables
DELETE from @r where Referenced = 'Users' AND Referencing = 'Parties'; -- other circ refsWHILE @i < 20
BEGIN
UPDATE t2
SET fillLevel = @i + 1
FROM @t t1
JOIN @r r on t1.TableName = r.Referenced
JOIN @t t2 on r.Referencing = t2.TableName
WHERE t1.FillLevel = @i
;SET @i = @i + 1;
ENDSELECT * FROM @t order by FillLevel, TableName;
SELECT * FROM @r;
GO
Bill Talada: Can you explain what you mean by a complex circular reference ?
March 6, 2017 at 1:56 pm
A simple circular reference would be where Table A self references A or Tables A and B reference each other. A complex circular reference would be where table A references B which references C which references A.
March 6, 2017 at 2:49 pm
Bill Talada - Monday, March 6, 2017 1:56 PMA simple circular reference would be where Table A self references A or Tables A and B reference each other. A complex circular reference would be where table A references B which references C which references A.
Sorry still don't get it. You mean Table A has a foreign key where it points to a another column in TABLE A ( Is that what you mean by a simple reference )
March 6, 2017 at 3:21 pm
mw112009 - Monday, March 6, 2017 2:49 PMBill Talada - Monday, March 6, 2017 1:56 PMA simple circular reference would be where Table A self references A or Tables A and B reference each other. A complex circular reference would be where table A references B which references C which references A.Sorry still don't get it. You mean Table A has a foreign key where it points to a another column in TABLE A ( Is that what you mean by a simple reference )
Yes, exactly. Do a search for "unary relationship". A common usage is when there is an employee table, and a field containing the supervisor's employee id. The table self-references to itself.
Employee 123 may be the supervisor for employee 456, 789, and 890. In these records, the supervisor id field would contain the value 123.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply