I needed to determine the order of operation on tables within a Sybase 12 database and couldn't find an easy solution. I came up with the following procedures and thought I might send them our for review / comment. Please feel free to use them if they prove helpful.
Jim
-- James J. Diaz
-- 2016.05.30
CREATE OR REPLACE VIEW DBA.ViewForeignKeys
AS
-- Include all tables without a foreign key
SELECT Entities.Table_Id AS PrimaryTableId,
Entities.Object_Id AS PrimaryObjectId,
Owners.user_name AS PrimaryOwner,
Entities.table_name AS PrimaryTableName,
NULL AS ForeignTableId,
NULL AS ForeignObjectId,
NULL AS ForeignOwner,
NULL AS ForeignTableName,
'Bogus' AS LastColumn
FROM SYS.SYSTAB AS Entities
JOIN SYS.SYSUSER AS Owners ON ( Owners.user_id = Entities.creator )
WHERE table_type = 1
AND PrimaryTableId NOT IN ( SELECT table_id FROM SYS.SYSIDX AS Indexes WHERE index_category = 2 )
UNION ALL
SELECT PrimaryTables.Table_Id AS PrimaryTableId,
PrimaryTables.Object_Id AS PrimaryObjectId,
PrimaryOwners.user_name AS PrimaryOwner,
PrimaryTables.table_name AS PrimaryTableName,
ForeignTables.Table_Id AS ForeignTableId,
ForeignTables.Object_Id AS ForeignObjectId,
ForeignOwners.user_name AS ForeignOwner,
ForeignTables.table_name AS ForeignTableName,
'Bogus' AS LastColumn
FROM SYS.SYSFKEY AS ForeignKeys
JOIN SYS.SYSIDX AS PrimaryIndexes ON ( PrimaryIndexes.table_id = ForeignKeys.primary_table_id
AND PrimaryIndexes.index_id = ForeignKeys.primary_index_id )
JOIN SYS.SYSTAB AS PrimaryTables ON ( PrimaryIndexes.table_id = PrimaryTables.table_id )
JOIN SYS.SYSUSER AS PrimaryOwners ON ( PrimaryOwners.user_id = PrimaryTables.creator )
JOIN SYS.SYSIDX AS ForeignIndexes ON ( ForeignIndexes.table_id = ForeignKeys.foreign_table_id
AND ForeignIndexes.index_id = ForeignKeys.foreign_index_id )
JOIN SYS.SYSTAB AS ForeignTables ON ( ForeignIndexes.table_id = ForeignTables.table_id )
JOIN SYS.SYSUSER AS ForeignOwners ON ( ForeignOwners.user_id = ForeignTables.creator );
CREATE OR REPLACE PROCEDURE DBA.EntityOrder()
RESULT(
EntityId INTEGER,
EntityOwner CHAR(128),
EntityName CHAR(128),
EntityOrder INTEGER,
ReferenceLevel INTEGER
)
BEGIN
DECLARE @EntityOrder INTEGER;
DECLARE @ReferenceLevel INTEGER;
DECLARE @LastCount INTEGER;
DECLARE @ThisCount INTEGER;
DECLARE LOCAL TEMPORARY TABLE @Results (
EntityId INTEGER,
EntityOwner CHAR(128),
EntityName CHAR(128),
EntityOrder INTEGER,
ReferenceLevel INTEGER,
) NOT TRANSACTIONAL;
SET @EntityOrder = 0;
SET @ReferenceLevel = 0;
FOR EachPrimaryTable AS PrimaryTableCursor INSENSITIVE CURSOR FOR
SELECT DISTINCT
PrimaryTableId AS @PrimaryTableId,
PrimaryOwner AS @PrimaryOwner,
PrimaryTableName AS @PrimaryTableName
FROM DBA.ViewForeignKeys
WHERE @PrimaryOwner NOT IN ( 'DBO', 'SYS', 'rs_systabgroup')
AND ForeignTableId IS NULL
DO
SET @EntityOrder = @EntityOrder + 1;
INSERT INTO @Results( EntityId, EntityOwner, EntityName, EntityOrder, ReferenceLevel ) VALUES ( @PrimaryTableId, @PrimaryOwner, @PrimaryTableName, @EntityOrder, @ReferenceLevel );
END FOR;
SET @LastCount = 0;
SELECT COUNT(*) INTO @ThisCount FROM @Results;
WHILE @ThisCount > @LastCount LOOP
SET @ReferenceLevel = @ReferenceLevel + 1;
SET @LastCount = @ThisCount;
FOR EachResult AS ResultCursor INSENSITIVE CURSOR FOR
SELECT DISTINCT
ForeignTableId AS @ForeignTableId,
ForeignOwner AS @ForeignOwner,
ForeignTableName AS @ForeignTableName
FROM DBA.ViewForeignKeys
WHERE @ForeignOwner NOT IN ( 'DBO', 'SYS', 'rs_systabgroup')
AND PrimaryTableId IN ( SELECT EntityId FROM @Results )
AND ForeignTableId NOT IN ( SELECT EntityId FROM @Results )
DO
-- don't include those in current result set
IF @ForeignTableId NOT IN (
SELECT PrimaryTableId
FROM DBA.ViewForeignKeys
WHERE @ForeignOwner NOT IN ( 'DBO', 'SYS', 'rs_systabgroup')
AND PrimaryTableId IN ( SELECT EntityId FROM @Results )
AND ForeignTableId NOT IN ( SELECT EntityId FROM @Results )
AND ForeignTableId <> @ForeignTableId )
THEN
SET @EntityOrder = @EntityOrder + 1;
INSERT INTO @Results( EntityId, EntityOwner, EntityName, EntityOrder, ReferenceLevel ) VALUES ( @ForeignTableId, @ForeignOwner, @ForeignTableName, @EntityOrder, @ReferenceLevel );
END IF;
END FOR;
SELECT COUNT(*) INTO @ThisCount FROM @Results;
END LOOP;
SELECT EntityId, EntityOwner, EntityName, EntityOrder, ReferenceLevel FROM @Results ORDER BY EntityOrder;
END;
CREATE OR REPLACE PROCEDURE DBA.DeleteOrder(
IN @TableOwner CHAR(128),
IN @TableName CHAR(128),
IN @AttributeName CHAR(128) DEFAULT NULL,
IN @AttributeValue CHAR(128) DEFAULT NULL,
IN @QuotedIdentifier BIT DEFAULT 0
)
RESULT(
EntityId INTEGER,
EntityOwner CHAR(128),
EntityName CHAR(128),
EntityOrder INTEGER,
ReferenceLevel INTEGER,
SQLStatement CHAR(2048)
)
BEGIN
DECLARE @EntityOrder INTEGER;
DECLARE @ReferenceLevel INTEGER;
DECLARE @LastCount INTEGER;
DECLARE @ThisCount INTEGER;
DECLARE @SQLStatement CHAR(2048);
DECLARE LOCAL TEMPORARY TABLE @Results (
EntityId INTEGER,
EntityOwner CHAR(128),
EntityName CHAR(128),
EntityOrder INTEGER,
ReferenceLevel INTEGER,
SQLStatement CHAR(2048),
) NOT TRANSACTIONAL;
SET @EntityOrder = 0;
SET @ReferenceLevel = 0;
FOR EachPrimaryTable AS PrimaryTableCursor INSENSITIVE CURSOR FOR
-- Get the tableid of the requested table and make sure
-- it is foreign keyed to another table
SELECT DISTINCT
PrimaryTableId AS @PrimaryTableId,
PrimaryOwner AS @PrimaryOwner,
PrimaryTableName AS @PrimaryTableName
FROM DBA.ViewForeignKeys
WHERE PrimaryOwner = @TableOwner
AND PrimaryTableName = @TableName
DO
SET @EntityOrder = @EntityOrder + 1;
SET @SQLStatement = STRING( 'DELETE FROM ', @PrimaryOwner, '.', @PrimaryTableName );
IF @AttributeName IS NOT NULL THEN
SET @SQLStatement = STRING( @SQLStatement, ' WHERE ', @AttributeName, ' = ');
IF @AttributeValue IS NOT NULL THEN
IF @QuotedIdentifier = 1 THEN
SET @SQLStatement = STRING( @SQLStatement, '''', @AttributeValue, '''');
ELSE
SET @SQLStatement = STRING( @SQLStatement, @AttributeValue );
END IF;
END IF;
END IF;
SET @SQLStatement = STRING( @SQLStatement, '; COMMIT WORK;');
INSERT INTO @Results( EntityId, EntityOwner, EntityName, EntityOrder, ReferenceLevel, SQLStatement ) VALUES ( @PrimaryTableId, @PrimaryOwner, @PrimaryTableName, @EntityOrder, @ReferenceLevel, @SQLStatement );
END FOR;
SET @LastCount = 0;
SELECT COUNT(*) INTO @ThisCount FROM @Results;
-- Table is not referenced may not actually exist
IF @ThisCount = 0 THEN
SET @SQLStatement = STRING( 'DELETE FROM ', @TableOwner, '.', @TableName );
IF @AttributeName IS NOT NULL THEN
SET @SQLStatement = STRING( @SQLStatement, ' WHERE ', @AttributeName, ' = ');
IF @AttributeValue IS NOT NULL THEN
IF @QuotedIdentifier = 1 THEN
SET @SQLStatement = STRING( @SQLStatement, '''', @AttributeValue, '''');
ELSE
SET @SQLStatement = STRING( @SQLStatement, @AttributeValue );
END IF;
END IF;
END IF;
SET @SQLStatement = STRING( @SQLStatement, '; COMMIT WORK;');
INSERT INTO @Results( EntityId, EntityOwner, EntityName, EntityOrder, ReferenceLevel, SQLStatement ) VALUES ( -1, @TableOwner, @TableName, @EntityOrder, @ReferenceLevel, @SQLStatement );
ELSE
WHILE @ThisCount > @LastCount LOOP
SET @ReferenceLevel = @ReferenceLevel + 1;
SET @LastCount = @ThisCount;
FOR EachResult AS ResultCursor INSENSITIVE CURSOR FOR
SELECT DISTINCT
ForeignTableId AS @ForeignTableId,
ForeignOwner AS @ForeignOwner,
ForeignTableName AS @ForeignTableName
FROM DBA.ViewForeignKeys
WHERE PrimaryTableId IN ( SELECT EntityId FROM @Results )
AND ForeignTableId NOT IN ( SELECT EntityId FROM @Results )
DO
-- don't include those in current result set
IF @ForeignTableId NOT IN (
SELECT PrimaryTableId
FROM DBA.ViewForeignKeys
WHERE PrimaryTableId IN ( SELECT EntityId FROM @Results )
AND ForeignTableId NOT IN ( SELECT EntityId FROM @Results )
AND ForeignTableId <> @ForeignTableId )
THEN
SET @EntityOrder = @EntityOrder + 1;
SET @SQLStatement = STRING( 'DELETE FROM ', @ForeignOwner, '.', @ForeignTableName );
IF @AttributeName IS NOT NULL THEN
SET @SQLStatement = STRING( @SQLStatement, ' WHERE ', @AttributeName, ' = ');
IF @AttributeValue IS NOT NULL THEN
IF @QuotedIdentifier = 1 THEN
SET @SQLStatement = STRING( @SQLStatement, '''', @AttributeValue, '''');
ELSE
SET @SQLStatement = STRING( @SQLStatement, @AttributeValue );
END IF;
END IF;
END IF;
SET @SQLStatement = STRING( @SQLStatement, '; COMMIT WORK;');
INSERT INTO @Results( EntityId, EntityOwner, EntityName, EntityOrder, ReferenceLevel, SQLStatement ) VALUES ( @ForeignTableId, @ForeignOwner, @ForeignTableName, @EntityOrder, @ReferenceLevel, @SQLStatement );
END IF;
END FOR;
SELECT COUNT(*) INTO @ThisCount FROM @Results;
END LOOP;
END IF;
SELECT EntityId, EntityOwner, EntityName, EntityOrder, ReferenceLevel, SQLStatement FROM @Results ORDER BY EntityOrder DESC;
END;
CREATE OR REPLACE PROCEDURE DBA.DeleteOrderOwner(
IN @TableOwner CHAR(128)
)
RESULT(
EntityId INTEGER,
EntityOwner CHAR(128),
EntityName CHAR(128),
EntityOrder INTEGER,
ReferenceLevel INTEGER,
SQLStatement CHAR(2048)
)
BEGIN
DECLARE @EntityOrder INTEGER;
DECLARE @ReferenceLevel INTEGER;
DECLARE @LastCount INTEGER;
DECLARE @ThisCount INTEGER;
DECLARE @SQLStatement CHAR(2048);
DECLARE LOCAL TEMPORARY TABLE @Results (
EntityId INTEGER,
EntityOwner CHAR(128),
EntityName CHAR(128),
EntityOrder INTEGER,
ReferenceLevel INTEGER,
SQLStatement CHAR(2048),
) NOT TRANSACTIONAL;
SET @EntityOrder = 0;
SET @ReferenceLevel = 0;
FOR EachPrimaryTable AS PrimaryTableCursor INSENSITIVE CURSOR FOR
SELECT DISTINCT
PrimaryTableId AS @PrimaryTableId,
PrimaryOwner AS @PrimaryOwner,
PrimaryTableName AS @PrimaryTableName
FROM DBA.ViewForeignKeys
WHERE @PrimaryOwner = @TableOwner
AND ForeignTableId IS NULL
UNION ALL
SELECT DISTINCT
ForeignTableId AS @PrimaryTableId,
ForeignOwner AS @PrimaryOwner,
ForeignTableName AS @PrimaryTableName
FROM DBA.ViewForeignKeys
WHERE ForeignOwner = @TableOwner
AND @TableOwner NOT IN ( SELECT PrimaryOwner FROM DBA.ViewForeignKeys WHERE ForeignTableId = @PrimaryTableId )
DO
SET @EntityOrder = @EntityOrder + 1;
SET @SQLStatement = STRING( 'DELETE FROM ', @PrimaryOwner, '.', @PrimaryTableName );
SET @SQLStatement = STRING( @SQLStatement, '; COMMIT WORK;');
INSERT INTO @Results( EntityId, EntityOwner, EntityName, EntityOrder, ReferenceLevel, SQLStatement ) VALUES ( @PrimaryTableId, @PrimaryOwner, @PrimaryTableName, @EntityOrder, @ReferenceLevel, @SQLStatement );
END FOR;
SET @LastCount = 0;
SELECT COUNT(*) INTO @ThisCount FROM @Results;
WHILE @ThisCount > @LastCount LOOP
SET @ReferenceLevel = @ReferenceLevel + 1;
SET @LastCount = @ThisCount;
FOR EachResult AS ResultCursor INSENSITIVE CURSOR FOR
-- Get all tables with foreign keys that come from results
SELECT DISTINCT
ForeignTableId AS @ForeignTableId,
ForeignOwner AS @ForeignOwner,
ForeignTableName AS @ForeignTableName
FROM DBA.ViewForeignKeys
WHERE PrimaryTableId IN ( SELECT EntityId FROM @Results )
-- don't include those that have foreign keys from tables not already identified
AND ForeignTableId NOT IN ( SELECT EntityId FROM @Results )
DO
-- don't include those in current result set
IF @ForeignTableId NOT IN (
SELECT PrimaryTableId
FROM DBA.ViewForeignKeys
WHERE PrimaryTableId IN ( SELECT EntityId FROM @Results )
AND ForeignTableId NOT IN ( SELECT EntityId FROM @Results )
AND ForeignTableId <> @ForeignTableId )
THEN
SET @EntityOrder = @EntityOrder + 1;
SET @SQLStatement = STRING( 'DELETE FROM ', @ForeignOwner, '.', @ForeignTableName );
SET @SQLStatement = STRING( @SQLStatement, '; COMMIT WORK;');
INSERT INTO @Results( EntityId, EntityOwner, EntityName, EntityOrder, ReferenceLevel, SQLStatement ) VALUES ( @ForeignTableId, @ForeignOwner, @ForeignTableName, @EntityOrder, @ReferenceLevel, @SQLStatement );
END IF;
END FOR;
SELECT COUNT(*) INTO @ThisCount FROM @Results;
END LOOP;
SELECT EntityId, EntityOwner, EntityName, EntityOrder, ReferenceLevel, SQLStatement FROM @Results ORDER BY EntityOrder DESC;
END;
Why is the order important to you - what are you trying to achieve?
For example if I want to delete a record that does have on delete cascade
Okay this doesn't work as planned. Does anyone know of a way to determine the order of operations required for referential integrity?
Sorry, I still don't get the point: If you use a FK with ON DELETE CASCADE semantics, why would you need to know the FK order - do you run into troubles because of FK cycles or the like? (Otherwise I would think the cascading simply just should work as expected...)
Or do you use DELETE RESTRICT semantics and want to find out all depending child records and delete them expliticly before deleting a parent record (which would otherwise fail, of course)? (I guess your code seems to imply that, though I'm not that good in studying long SQL procedures without much comments...)
Note, when trying to build a tree of FK dependencies (which will fail if they build a DAG and not a tree...), a RECURSIVE UNION might be a natural way to do so...
FWIW, in case you do use MobiLink: As the MobiLink client generates an automatic FK order based on the publication's definition by default (and would warn about FK cycles) - you might try to capture the queries that DBMLSYNC issues initially to get the according logic - possibly by comparing a RLL with the extended option TableOrderChecking (toc) extended option both set/unset - beware, that's a veeeeeeeeeery wild guess...:)
Sorry, just another question: What do you mean by that expression? - Note, you can always set option WAIT_FOR_COMMIT = ON to defer that checking...
I am in the process of merging two databases both of which are involved in SQL Remote replication. Once merged I will be keeping these databases in sync using Mobilink. Our approach is to first merge and than once merged to sync. The databases are very complex each having over 1000 tables. One of my objectives is to minimize the impact on the SQL remote messages going out to those databases involved in SQL Remote replication so WAIT_FOR_COMMIT as I understand it is not an option.
Having said this I can see many other reasons why it would be nice to be able to determine the "Order Of Operations Required for Referential Integrity" some of which both you and I mentioned above.
Thanks for your help when able I will try your suggestion regarding tracing the DBMLSYNC.