How To: Find Relationships in ISM using SQL Management Studio

Version 1


    This Solution applies to all versions of Ivanti Service Manager (current version, on the date when this article is created is 2018.1)



    How to Find Relationships in ISM using SQL Management Studio. It can be helpful if we are not able to find relationship in Admin UI.



    First we need script which searched for specific value across all tables of a database.

    Source: website.

    Link: Search a string accross multiple tables



    @search_string  VARCHAR(100),

    @table_name SYSNAME,

    @table_id INT,

    @column_name SYSNAME,

    @sql_string VARCHAR(2000)


    SET @search_string = '6822C3E155CE4BD880BADCA37535530E' -- type here value you are looking for


    DECLARE tables_cur CURSOR FOR SELECT +'.'+ [name], object_id FROM sys.objects so INNER JOIN sys.schemas ss ON so.schema_id = ss.schema_id WHERE  type = 'U'


    OPEN tables_cur


    FETCH NEXT FROM tables_cur INTO @table_name, @table_id




        DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id

            AND system_type_id IN (167, 175, 231, 239)


        OPEN columns_cur


        FETCH NEXT FROM columns_cur INTO @column_name



    SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @table_name + ' WHERE [' + @column_name + ']

                LIKE ''%' + @search_string + '%'') PRINT ''' + @table_name + ', ' + @column_name + ''''




    FETCH NEXT FROM columns_cur INTO @column_name



        CLOSE columns_cur


    DEALLOCATE columns_cur


    FETCH NEXT FROM tables_cur INTO @table_name, @table_id



    CLOSE tables_cur

    DEALLOCATE tables_cur


    Example of usage:

    For instance, Incident record has Source column and we would like to add new Source (eg. Pigeon Post), but we do not know where.


    In Incident table we see Source ( eg. Phone ) and its RecId in Source_Valid table.

    If we type RecId into our script, it will return all locations having this value. ( It can take some time before it is executed ).


    In this example results are:


    According to those results we can find that Source comes from IncidentSource table from RecId column.