March 17, 2015 at 8:16 am
How can I find calls which do not exist in stored procedures and functions?
We have many stored procedures, sometimes a stored procedure or function which is called does not exist. Is there a query/script or something that I can identify which stored procedures do not 'work' and which procedure/function they are calling?
Edit:
I am searching for stored procedures and functions which are still called, but do not exist in the current database.
Thanks for your time and attention,
Ben
March 17, 2015 at 11:35 am
Do you mean for procedures that are called within procedures i.e ("other" procedures get executed from within a calling procedure?)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 17, 2015 at 2:53 pm
MyDoggieJessie (3/17/2015)
Do you mean for procedures that are called within procedures i.e ("other" procedures get executed from within a calling procedure?)
Sorry that I was not clear.
I would like a list of routines, which call routines which do not exist (anymore), together with the name of the routine name of the not existing routine.
So if routine AA calls routine BB and BB does not exist, I would like names AA and BB.
Routines can be stored procedures and functions. (In the database).
Ben
March 17, 2015 at 4:20 pm
How about something like this?SELECT
OBJECT_NAME(referencing_id) AS referencing_object,
referenced_entity_name
FROM
sys.sql_expression_dependencies
WHERE
referenced_database_name IS NULL
AND is_ambiguous = 0 AND referenced_schema_name IS NOT NULL
AND referencing_class = 1 AND referenced_id IS NULL AND is_caller_dependent = 0
--AND OBJECT_NAME(referencing_id) = '_Test'
ORDER BY
OBJECT_NAME(referencing_id) Did a quick test and it looked like it might work...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 18, 2015 at 5:14 am
March 19, 2015 at 4:06 am
MyDoggieJessie (3/17/2015)
How about something like this? Did a quick test and it looked like it might work...
Used the code:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[A_test]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[A_test]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[B_test]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[B_test]
GO
create procedure A_test as
begin
print 'Hallo'
end
GO
create procedure B_test as
begin
exec A_test
exec A_does_not_exist
end
GO
-----------------------------------------------------------------------------------------
---- Code given by SSCarpal Tunnel 20150317
-----------------------------------------------------------------------------------------
SELECT
OBJECT_NAME(referencing_id) AS referencing_object,
referenced_entity_name
FROM
sys.sql_expression_dependencies
WHERE
referenced_database_name IS NULL
AND is_ambiguous = 0 AND referenced_schema_name IS NOT NULL
AND referencing_class = 1 AND referenced_id IS NULL AND is_caller_dependent = 0
--AND OBJECT_NAME(referencing_id) = '_Test'
ORDER BY
OBJECT_NAME(referencing_id)
-----------------------------------------------------------------------------------------
-- Results in
-- No Rows
drop procedure [dbo].[A_test]
GO
drop procedure [dbo].[B_test]
GO
But this came up Empty. (No rows).
Used also the code below.
select 'A_does_not_exist' as CALLED_ROUTINE, * from sys.dm_sql_referencing_entities ('A_does_not_exist', 'OBJECT')
-- No Rows
select 'A_test' as CALLED_ROUTINE, * from sys.dm_sql_referencing_entities ('A_test', 'OBJECT')
-- Single row which indicates that B_test is doing the call
select 'B_test' as CALLED_ROUTINE, * from sys.dm_sql_referencing_entities ('B_test', 'OBJECT')
-- No Rows
select 'A_does_not_exist' as callingroutine, * from sys.dm_sql_referenced_entities ('A_does_not_exist', 'OBJECT')
-- No Rows
select 'A_test' as callingroutine, * from sys.dm_sql_referenced_entities ('A_test', 'OBJECT')
-- No Rows
select 'B_test' as callingroutine, * from sys.dm_sql_referenced_entities ('B_test', 'OBJECT')
-- No Rows
Haven't tried the free software yet.(I' have to take some time for that, sorry).
(I have seen a search for the impact of a name change of an object. But doubt that there is a function for finding calls to ' non-existing' objects. I'll check this again.).
So the questions still is how can I find the Call 'A_does_not_exist' in the stored procedure B_test
Thx,
Ben
March 19, 2015 at 5:25 am
still using sys.sql_expression_dependencies, this seems to work for me, but gives some falsepositives due to CTE names appearing in the dependancies as well.
i deleted some staging tables that i know some views require, as well as a procedure that another procedure calls.
obviously, this only works for compiled objects like procs/views/functions, but it's a start
/*
schemaname objectname type_desc referenced_server_name referenced_database_name referenced_schema_name referenced_entity_name Filler
------------- -------------------------- ------------------------ ------------------------- -------------------------- ----------------------- ------------------------ ------
dbo vwStagePatientaccountnote VIEW NULL NULL NULL StagePatientaccountnote
dbo vwStageDocument VIEW NULL NULL NULL StageDocument
dbo vwStageKickreason VIEW NULL NULL NULL StageKickreason
dbo vwStageClaimnote VIEW NULL NULL NULL StageClaimnote
dbo vwStageInterfacemap VIEW NULL NULL NULL StageInterfacemap
dbo vwStagePhimodification VIEW NULL NULL NULL StagePhimodification
dbo sp_export_all SQL_STORED_PROCEDURE NULL NULL NULL sp_getddl
(7 row(s) affected)
*/
SELECT SCHEMA_NAME(so.SCHEMA_ID) AS schemaname,
so.NAME AS objectname,
so.type_desc,
sed.referenced_server_name,
sed.referenced_database_name,
sed.referenced_schema_name,
sed.referenced_entity_name,
'' As Filler--*
FROM sys.sql_expression_dependencies sed
INNER JOIN sys.all_objects so
ON sed.referencing_id = so.OBJECT_ID
LEFT JOIN sys.all_objects sx
--case statement because joins might not explicitly mention the schema.
ON CASE WHEN sed.referenced_schema_name IS NULL THEN SCHEMA_NAME(sx.schema_id) ELSE sed.referenced_schema_name END
= SCHEMA_NAME(sx.schema_id) AND sed.referenced_entity_name = sx.name
WHERE sx.object_id IS NULL
and referenced_database_name is null
Lowell
March 19, 2015 at 9:56 am
Lowell (3/19/2015)
obviously, this only works for compiled objects like procs/views/functions, but it's a start
Thanks, get some 'false positives' for calls to stored procedures in the master, but this is working nice.
I do realise that dynamic code and maybe some other situations are not catered for.
Thanks,
Ben
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply