Forum Discussion
Helper IVCapture fabric warehouse SP Name into a table
Hi AJAJ , Thank you for reaching out to the Microsoft Community Forum.
Please try this:
DECLARE @SchemaName sysname = 'dbo';
DECLARE @TableName sysname = 'YourTableName';
SELECT
s.name AS referencing_schema,
o.name AS referencing_object,
o.type_desc AS referencing_type
FROM sys.sql_expression_dependencies d
JOIN sys.objects o
ON d.referencing_id = o.object_id
JOIN sys.schemas s
ON o.schema_id = s.schema_id
WHERE d.referenced_schema_name = @SchemaName
AND d.referenced_entity_name = @TableName
AND o.type IN ('P','V') -- P = Stored Procedure, V = View
ORDER BY o.type_desc, s.name, o.name;
Helper IVThanks deborshi_nag. Unfortunately you slightly modified code still returns null via pipeline run.
My pipeline dynamically triggers SPs from ETLConfig tables. So SP name is already a parameter to pipeline.
v-hashadapu you understood the issue exactly that when run via pipeline its not working. I didnt want to hardcode.
there are 2 ways we run sp, via pipeline or for debugging/development via ssms.
im using a simple if else conditon if parmeter via pipeline is not null, use that, else use query.
approach i took.
ALSO ANY THOUGHTS ON HOW TO TRACK IF A TABLE IS USED BY A SP OR VIEW? In fabric warehouse. SQL server has view dependency when right clicked on a table. There are also dependency scripts which point out if a table is being used by a view or sp. Do by any chance know of script if a table is used by view or sp.
- deborshi_nag
Super User
Hello AJAJ
In regards to your original query, why don't you write a stored procedure that will accept a parameter, check that the value supplied matches with a stored procedure name, and if so, insert a line in the log table and run the stored procedure.
I can supply you with the code if that helps. I have tried this approach, it works both when calling it directly, as well as via a pipeline activity.
- deborshi_nag
Hello AJAJ
See if this works:
DECLARE @SchemaName varchar(128) = 'dbo';DECLARE @TableName varchar(128) = 'ProcExecutionLog';SELECTOBJECT_SCHEMA_NAME(d.referencing_id) AS referencing_schema,OBJECT_NAME(d.referencing_id) AS referencing_object,o.type_desc AS referencing_type,d.referenced_schema_name,d.referenced_entity_nameFROM sys.sql_expression_dependencies dJOIN sys.objects oON d.referencing_id = o.object_idWHERE d.referenced_schema_name = @SchemaNameAND d.referenced_entity_name = @TableNameAND o.type IN ('V','P') -- V = View, P = Stored ProcedureORDER BY referencing_type, referencing_schema, referencing_object; - v-hashadapu
Community Support
Hi AJAJ , Thank you for reaching out to the Microsoft Community Forum.
Your IF/ELSE approach is exactly the right way to handle this. In Fabric, DMV based detection isn’t reliable in pipeline runs, so using the pipeline parameter when available and falling back to DMVs for SSMS/debug scenarios is the correct and practical pattern.
For your second question, sys.sql_expression_dependencies is the right way to track which procedures or views use a table in Fabric, but it only captures static SQL. If you’re using dynamic SQL or config-driven execution, some dependencies won’t show up there. In practice, you’ll need to combine it with a search on sys.sql_modules to catch anything missed, since Fabric doesn’t have a fully complete dependency view like SQL Server.
- AJAJ
thanks v-hashadapu . Yes most of SQLs are static. Do you by any chance could share the code based on the your suggestion to use sys.sql_expression_dependencies? Object is what fabric warehouse table is used by any sp or any view.
- v-hashadapu
Hi AJAJ , Thank you for reaching out to the Microsoft Community Forum.
Please try this:
DECLARE @SchemaName sysname = 'dbo';
DECLARE @TableName sysname = 'YourTableName';
SELECT
s.name AS referencing_schema,
o.name AS referencing_object,
o.type_desc AS referencing_type
FROM sys.sql_expression_dependencies d
JOIN sys.objects o
ON d.referencing_id = o.object_id
JOIN sys.schemas s
ON o.schema_id = s.schema_id
WHERE d.referenced_schema_name = @SchemaName
AND d.referenced_entity_name = @TableName
AND o.type IN ('P','V') -- P = Stored Procedure, V = View
ORDER BY o.type_desc, s.name, o.name;
