Is there a T-SQL command that checks whether the object to which a synonym refers exists and is valid?
That looks like an awfully long script for such a simple check; what's wrong with just:
if exists (select * from sys.synonyms where name = @currentSynonym and object_id(base_object_name) is not null)
begin
--Add logic here
end
You can use OBJECT_ID
with the base_object_name
column of sys.synonyms
to test whether the base objects exist:
SELECT [Schema] = sch.name,
[Name] = syn.name,
syn.base_object_name,
[Base object exists?] = CASE WHEN OBJECT_ID(syn.base_object_name) IS NOT NULL THEN 'Yes' ELSE 'No' END
FROM sys.synonyms syn
JOIN sys.schemas ON sch ON syn.schema_id = sch.schema_id
ORDER BY [Base object exists?], [Schema], [Name];
The query was adapted from this answer to this same question.