January 19, 2016 at 10:08 am
Hi,
I have a lot of stored procedures which use several tables.
Is there a way to extract the information of which tables used by which stored procedures?
I mean, suppose I have a stored procedure that has 20 tables inside (Joins , inserts, etc...)
How can I know which are those tables without having to see one by one?
Thank you
January 19, 2016 at 11:05 am
You can use the catalog view sys.sql_dependencies to extract the information you want. See here for more detail: https://msdn.microsoft.com/en-us/library/ms174402(v=sql.90).aspx
-- Gianluca Sartori
January 19, 2016 at 11:24 am
When I try to use that I get empty back. No records returned
January 19, 2016 at 11:38 am
river1 (1/19/2016)
When I try to use that I get empty back. No records returned
Can you post the code that you're using?
January 19, 2016 at 11:41 am
river1 (1/19/2016)
When I try to use that I get empty back. No records returned
Also keep in mind the database context. If you're running this without specifying your database, and your connection is in master, model, or tempdb it's not at all uncommon to see no dependencies.
Cheers!
January 19, 2016 at 11:46 am
USE arf_etl
GO
SELECT OBJECT_NAME(object_id) AS referencing_object_name
,COALESCE(COL_NAME(object_id, column_id), '(n/a)') AS referencing_column_name
,*
FROM sys.sql_dependencies
WHERE referenced_major_id = OBJECT_ID('dbo.bmb_ms.usp_BMBBI_ft_ingredient_consumption_direct_insert')
ORDER BY OBJECT_NAME(object_id), COL_NAME(object_id, column_id);
GO
January 19, 2016 at 11:47 am
This way:
USE arf_etl
GO
SELECT OBJECT_NAME(object_id) AS referencing_object_name
,COALESCE(COL_NAME(object_id, column_id), '(n/a)') AS referencing_column_name
,*
FROM sys.sql_dependencies
WHERE referenced_major_id = OBJECT_ID('[bmb_ms].[usp_BMBBI_ft_estimated_sale_control_insertupdate]')
ORDER BY OBJECT_NAME(object_id), COL_NAME(object_id, column_id);
GO
January 19, 2016 at 11:48 am
But if I do like this:
sp_helptext '[bmb_ms].[usp_BMBBI_ft_estimated_sale_control_insertupdate]'
I can see the text inside the procedure
January 19, 2016 at 11:49 am
You have your logic inverted
SELECT OBJECT_NAME(referenced_major_id) AS referenced_object_name
,COALESCE(COL_NAME(referenced_major_id, referenced_minor_id), '(n/a)') AS referenced_column_name
,*
FROM sys.sql_dependencies
WHERE object_id = OBJECT_ID('YourStoredProcedureNameAndSchema')
ORDER BY referenced_object_name, referenced_column_name;
January 19, 2016 at 11:51 am
If you're wanting to see the objects that are referenced by that stored procedure, then you're checking the wrong thing in the WHERE clause.
As written, that query is looking for objects that reference the stored procedure. You want the WHERE clause to check for sys.sql_dependencies.object_id=<your stored procedure object_id>.
Cheers!
EDIT: Luis beat me to it 🙂
January 19, 2016 at 12:29 pm
It is not returning everything.
For example:
select distinct(referenced_object_name)
from
(
SELECT OBJECT_NAME(referenced_major_id) AS referenced_object_name
,COALESCE(COL_NAME(referenced_major_id, referenced_minor_id), '(n/a)') AS referenced_column_name
,*
FROM sys.sql_dependencies
WHERE object_id = OBJECT_ID('bmb_ms.usp_BMBBI_ft_cup_and_ingredient_consumption_worktable_insert')
--ORDER BY referenced_object_name, referenced_column_name
)r
Returns only two tables:
machine_counter_history
market
But when I go inside the procedure I see a lot more....
Any idea why?
Thank you
January 19, 2016 at 12:50 pm
If you're trying to get cross-database references, maybe you can change these options (I'm not sure if they're available in 2005)
SELECT referenced_server_name
,referenced_database_name
,referenced_schema_name
,referenced_entity_name
,is_ambiguous
FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID('YourStoredProcedureNameAndSchema');
SELECT *
FROM sys.dm_sql_referenced_entities ('YourStoredProcedureNameAndSchema' , 'OBJECT' )
If you're using dynamic sql, it's going to be near to impossible to extract the tables referenced by the procedure. One option could be taking the tables from cached plans, but that would only work if the tables included don't depend on your parameters.
January 19, 2016 at 12:52 pm
I am not using dynamic SQL
January 19, 2016 at 12:54 pm
SQL Server 2005 does not recognise that command.
January 19, 2016 at 2:30 pm
dm_sql_referenced_entities is 2008 and above. sys.sql_dependencies can be inaccurate, as procedures can be created before the tables they reference, if they are the dependency info won't be there. No good solution on SQL 2005 and under short of writing a T-SQL parser or dropping and recreating all procedures and then checking sys.sql_dependencies.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply