SQL Query to retrieve data in Creatio

Hi Bhumika, 

Please replace 

B.UsrRevenueTreatyNumber.Id with B.UsrRevenueTreatyNumberId

Best regards,

Yurii.

Can you pls clarify here like in our 2 tables we do have a relationship through which we get values?

Can you pls explain do we have any interlinked relationship between 2 objects? 

For example in HDFC Revenue and Revenue booking has one to many relationships Revenue booking is linked with Revenue ID?

Do we have the same type of relationship in the audit log also? Can you pls confirm it will be better for me to execute queries?

Table seems different to me.

- Audit table of the same section is different from the object tables.

Bhumika Bisht,

You can do the SELECT from SysOperationAudit to check the relations that this table has.

The detailed info can be found with the help of the query:

SELECT * FROM information_schema.columns where table_name = 'SysOperationAudit'

Please use it to find the dependencies.

Best regards,

Bogdan S.

Hy Thankyou for sharing the query with me but I am still not able to locate dependencies with other tables. Can you pls guide me on how will I find the dependencies with other tables?

Bhumika Bisht,

you can use this query to find all the foreign keys, as well as referencing and referenced table/column. 

You can also add a "WHERE" clause to only find foreign keys of a specific table.

SELECT  obj.name AS FK_NAME,
    sch.name AS [schema_name],
    tab1.name AS [table],
    col1.name AS [column],
    tab2.name AS [referenced_table],
    col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
    ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id

Please find more options here

Best regards,

Yurii.