Home

Find references to a record of a Siebel table in all of its foreign key tables

2010, January 12 - 18:16 — müzso
The Transact SQL code below comes handy when you're digging into Siebel's tables. It will find all references to a specific record in a specific table (identified by the table's name in the @Table parameter and the rowid of the record in the @Rowid parameter) by going through all the foreign keys that are pointing to the specified table and executing a query to get the count of referring records. Since this is a Transact SQL code snippet, it suggests you're running Siebel CRM on a Microsoft SQL Server (which is not the typical case ... but occurs anyway as it did with one of the clients of my company). Smiling

In the example I've used the S_PARTY table and the '0-1' rowid (which stand for the SADMIN user's party record). Thus it'll list a count for all table+column pairs that have a foreign key defined in the Siebel Repository on the S_PARTY table and have records with the specified rowid value in the given foreign key column. (You could use the S_USER table too and it'd list over a thousand table+column pairs that contain values referring to the admin user.)

USE siebeldb
GO

DECLARE @Table varchar(100)
SET @Table = 'S_PARTY'
DECLARE @Rowid varchar(15)
SET @Rowid = '0-1'

DECLARE Cur CURSOR FOR
        SELECT t.NAME, c.NAME
        FROM S_TABLE AS t
                INNER JOIN INFORMATION_SCHEMA.TABLES AS it ON it.TABLE_NAME = t.NAME
                INNER JOIN S_COLUMN AS c ON c.TBL_ID = t.ROW_ID
                INNER JOIN S_TABLE AS ft ON ft.ROW_ID = c.FKEY_TBL_ID
        WHERE ft.NAME = @Table
        ORDER BY t.NAME, c.NAME

OPEN Cur
DECLARE @TabName VARCHAR(100)
DECLARE @ColName VARCHAR(100)
DECLARE @Sql NVARCHAR(200)
DECLARE @Cnt INT
FETCH NEXT FROM Cur INTO @TabName, @ColName
WHILE (@@FETCH_STATUS = 0)
BEGIN
        SET @Sql = 'SELECT @Cnt = COUNT(*) FROM ' + @TabName + ' WHERE ' + @ColName + ' = @Rowid'
        EXEC sp_executesql @Sql, N'@Rowid varchar(15), @Cnt int output', @Rowid = @Rowid, @Cnt = @Cnt OUTPUT
        IF @Cnt > 0 PRINT @TabName + '.' + @ColName + ': ' + CONVERT(VARCHAR(10), @Cnt)
        FETCH NEXT FROM Cur INTO @TabName, @ColName
END
CLOSE Cur
DEALLOCATE Cur
GO

Syndicate content