MS SQL procedures - lineage not being pickup and process

[kb-ong]

Affected module Ingestion Framework

Describe the bug MS SQL procedures - lineage not being pickup and process

To Reproduce create two tables namely (test1,test2) and 1 storedprocedure "myproc" with simple logic as

create PROCEDURE myproc AS BEGIN insert into test2 select * from test1 END GO

Expected behavior

1. run metadata ingestion.
2. run the "exec myproc" in any SQL client tool
3. run lineage ingestion
4. test1 and test2 lineage should build in UI

Version:

• OS: window
• Python version: 3.8.10
• OpenMetadata version: 1.4.7
• OpenMetadata Ingestion package version: 1.4.7.1

Additional context The storedprocedure lineage not being calculated is because the sql server log the "exec myproc" as "create procedure..." in the dm_exec_sql_text table and this was being ignored/filtered by the openmetadata SQL default filtering query.

as we can see from the SQL query below. SELECT TOP 1000
db.NAME database_name,
t.text query_text,
s.last_execution_time start_time,
DATEADD(s, s.total_elapsed_time/1000, s.last_execution_time) end_time,
s.total_elapsed_time/1000 duration,
NULL schema_name,
NULL query_type,
NULL user_name,
NULL aborted FROM sys.dm_exec_cached_plans AS p INNER JOIN sys.dm_exec_query_stats AS s ON p.plan_handle = s.plan_handle CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t INNER JOIN sys.databases db ON db.database_id = t.dbid WHERE s.last_execution_time between '2024-08-25 00:00:00' and '2024-08-27 00:00:00' AND t.text NOT LIKE '/ {"app": "OpenMetadata", %%} /%%' AND t.text NOT LIKE '/ {"app": "dbt", %%} /%%' AND p.objtype != 'Prepared' AND (lower(t.text) LIKE '%%select%%into%%' OR lower(t.text) LIKE '%%insert%%into%%select%%' OR lower(t.text) LIKE '%%update%%' OR lower(t.text) LIKE '%%merge%%') AND lower(t.text) NOT LIKE '%%create%%procedure%%'
AND lower(t.text) NOT LIKE '%%create%%function%%'
AND lower(t.text) NOT LIKE '%%declare%%'
ORDER BY s.last_execution_time DESC

when we triggered the "lineage ingestion", the query run in procedure is being omitted, i.e. "insert into test2 select * from test1"

because openmetadata SQL logic is to ignore the "create procedure..." when building lineage.

[RickLeite]

Same bug here using OpenMetadata in production environment. Version: 1.4.8

[kb-ong]

@SumanMaharana, do you manage to reproduce on your end ?