The following script gets a list of the synonyms, parses them, and then the checks if the existence of the object on the sys.objects table.
Any errors encountered are assumed as "not a valid synonym"
/* Author: Miguel Quintana Description: Reads all SYNONYMS on the database and test if they are valid. It checks their EXISTENCE on the SYSOBJECTS table and uses the Link Server if available No configuration needed. Just run it under the DB that you are testing Synonyms */ -- this variables are for the cursor DECLARE @NAME NVARCHAR(100) ,@SERVERNAME NVARCHAR(20) ,@DBName NVARCHAR(100) ,@SCHEMA NVARCHAR(100) ,@OBJECTNAME NVARCHAR(1000) ,@BaseObjectName NVARCHAR(1000) -- This are to process the data DECLARE @CMD NVARCHAR(MAX) DECLARE @RESULTS TABLE ( VALUE NVARCHAR(100) ,SYNONYM NVARCHAR(100) ,BaseObjectName NVARCHAR(100) ,Link_SERVER NVARCHAR(100) ) DECLARE CUR CURSOR FOR SELECT NAME ,PARSENAME(base_object_name, 4) ServerName ,COALESCE(PARSENAME(base_object_name, 3), DB_NAME(DB_ID())) AS dbName ,COALESCE(PARSENAME(base_object_name, 2), SCHEMA_NAME(SCHEMA_ID())) AS schemaName ,PARSENAME(base_object_name, 1) AS objectName ,BASE_OBJECT_NAME FROM sys.synonyms order by ServerName DESC, NAME OPEN CUR FETCH NEXT FROM CUR INTO @name ,@SERVERNAME ,@DBNAME ,@Schema ,@ObjectName ,@BaseObjectName WHILE @@FETCH_STATUS = 0 BEGIN -- Test SYNONYMS that use Link Servers IF @SERVERNAME IS NOT NULL BEGIN SET @cmd = 'EXEC (''IF EXISTS(select name from ' + @DBName + '.[sys].[objects] where name = ''''' + @OBJECTNAME + ''''') SELECT ''''VALID SYNONYM'''',''''' + @NAME + ''''',''''' + @BaseObjectName + ''''',''''' + @SERVERNAME + ''''' ELSE SELECT ''''NOT A VALID SYNONYM'''',''''' + @NAME + ''''',''''' + @BaseObjectName + ''''',''''' + @SERVERNAME + ''''''') AT [' + @SERVERNAME + ']' print @cmd BEGIN TRY INSERT INTO @RESULTS EXEC (@cmd) END TRY -- IF the exec fails for any reason, the SYNONYM is not valid BEGIN CATCH INSERT INTO @RESULTS VALUES ( 'NOT A VALID SYNONYM' ,@NAME ,@BaseObjectName ,@SERVERNAME ) END CATCH END -- Test SYNONYMS that do not use Link Servers ELSE BEGIN SET @cmd = 'EXEC (''IF EXISTS(select name from ' + @DBName + '.[sys].[objects] where name = ''''' + @OBJECTNAME + ''''') SELECT ''''VALID SYNONYM'''',''''' + @NAME + ''''',''''' + @BaseObjectName + ''''','''' '''' ELSE SELECT ''''NOT A VALID SYNONYM'''',''''' + @NAME + ''''',''''' + @BaseObjectName + ''''',''''' + @@SERVERNAME + ''''''')' print @cmd BEGIN TRY INSERT INTO @RESULTS EXEC (@cmd) END TRY -- IF the exec fails for any reason, the SYSNONYM is not valid BEGIN CATCH INSERT INTO @RESULTS VALUES ( 'NOT A VALID SYNONYM' ,@NAME ,@BaseObjectName ,@@SERVERNAME ) END CATCH END FETCH NEXT FROM CUR INTO @name ,@SERVERNAME ,@DBNAME ,@Schema ,@ObjectName ,@BaseObjectName END CLOSE CUR DEALLOCATE CUR SELECT Value ,[SYNONYM] ,UPPER(Link_SERVER) 'Link Server' ,BaseObjectName ,COALESCE(ss.data_source,'') 'Data Source' FROM @RESULTS r LEFT JOIN master.sys.servers ss on r.Link_SERVER = ss.name ORDER BY VALUE
Link Servers are supported.
Sometimes, it will report as not valid SYNONYM even though you are able to use them (select top 1 * from <sn_syonym_name>). but users without sysadmin cannot.
There are many reasons why that would happen, usually drop & recreate fixes the issue or even running the following:
EXEC sp_serveroption @server = '',@optname = 'remote proc transaction promotion', @optvalue = 'false' ;