I'm writing an sql script that automatically inserts "Unknown" records in dimension tables. Since there are foreign key constraints in the datawarehouse, I needed to have a list of tables in the right order, so I wouldn't get an integrity violation. The following scripts seems to be the answer:
WITH AllTables (TableId, TableName, ParentTableId) AS
(
SELECT
so.id,
so.name,
sr.rkeyid
FROM sysobjects so
LEFT OUTER JOIN sysreferences sr ON sr.fkeyid = so.id
WHERE so.xtype = 'U' AND so.name LIKE 'Dim%'
),
TableList (TableId, TableName, ParentTableId) AS
(
SELECT
at.TableId,
at.TableName,
at.ParentTableId
FROM AllTables at
WHERE ParentTableId IS NULL
UNION ALL
SELECT
at.TableId,
at.TableName,
at.ParentTableId
FROM AllTables at
WHERE at.ParentTableId = at.TableId
UNION ALL
SELECT
at.TableId,
at.TableName,
at.ParentTableId
FROM AllTables at
INNER JOIN TableList tl ON tl.TableId = at.ParentTableId
WHERE at.TableId <> at.ParentTableId
)
SELECT *
FROM TableList
NOTE: I used a WHERE-clause on the table names, since I use a naming convention where dimension tables always start with 'Dim'