November 11, 2020 at 3:55 pm
I need to find all stored procedures that point to a table, function, a database that does not exist. I am using sys.sql_expression_dependencies built-in view for this.
There are two problems with it. First, in some cases referenced object in this view is just an alias; and second, it happen to be a part of XML query.
In this query I am trying to find all referenced databases that don't actually exist:
select referencing_object_type = refing_obj.type_desc,
referencing_object_name = refing_obj.name,
d.referenced_server_name,
d.referenced_database_name,
d.referenced_schema_name,
d.referenced_entity_name,
d.referenced_id
from sys.sql_expression_dependencies d
join sys.objects refing_obj on d.referencing_id = refing_obj.object_id
where d.referenced_server_name is null
and d.referenced_database_name is not null
and not exists (select name
from sys.databases
where name = d.referenced_database_name)
And the most of returned entries are actually parts of XML queries inside of stored procedures.
I also tried sp_depends, but it's deprecated, sys.sql_dependencies, but it's also going to be removed. And sp_recompile does not check anything, it just marks a S.P. to be recompiled for the next run.
What you usually use for this kind of problem?
Thanks
November 11, 2020 at 4:08 pm
What do you mean by XML query? An XPATH query?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 11, 2020 at 4:46 pm
Apparently it's XPATH. It looks like this:
SELECT ActivityLogXml.Activity.query('Comments').value('.', 'varchar(max)')
FROM @ActivityLogXml.nodes('/Activity') AS ActivityLogXml(Activity)And the part ActivityLogXml.Activity.query is interpreted by sys.sql_expression_dependencies as db.schema.object
November 11, 2020 at 6:15 pm
Stepping back a little, have you tried importing your database into a Visual Studio database project and then attempting a build? That should throw out various errors and warnings to help you find any rogue references.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 11, 2020 at 7:23 pm
No, I don't have Visual Studio. And I am looking for SQL solution.
November 12, 2020 at 10:37 am
This was removed by the editor as SPAM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply