How to check if the table underlying the synonym exists

Question

I am trying to create a simple script to upload the results of a complex view into a table for reporting. I used synonyms to simplify the configuration of view and table names.

The idea is that the script user can put the name of the view they want to use as the source and the name of the target report table at the beginning and at the beginning. If the table does not exist, then the script should create it. If the table already exists, then the script should only copy records from the view that are not already in the table.

The following is a list of the script, but I cannot find a good way to check if the table underlying the synonym exists:

CREATE SYNONYM SourceView FOR my_view CREATE SYNONYM TargetReportingTable FOR my_table -- Here where I'm having trouble, how do I check if the underlying table exists? IF (SELECT COUNT(*) FROM information_schema.tables WHERE table_name = TargetReportingTable) = 0 BEGIN -- Table does not exists, so insert into. SELECT * INTO TargetReportingTable FROM SourceView END ELSE BEGIN -- Table already exists so work out the last record which was copied over -- and insert only the newer records. DECLARE @LastReportedRecordId INT; SET @LastReportedRecordId = (SELECT MAX(RecordId) FROM TargetReportingTable) INSERT INTO TargetReportingTable SELECT * FROM SourceView WHERE RecordId > @LastReportedRecordId END DROP SYNONYM SourceView DROP SYNONYM TargetReportingTable 

I know that I can just make the script user copy the table name into the string 'information_schema', as well as into the synonym at the top, but this leaves the visibility for an error.

I also know that I can do something dirty, for example, put the table name in a variable and rip out SQL as a string, but it makes me feel a little sick!

Is there a nice elegant way of SQL to check if a table underlies a synonym? Or a completely different way to solve the problem?

Answer 1

Not the most elegant solution, but you can join the sys.synonyms table to the sys.tables table to check if the table exists.

If the table does not exist, the connection will fail and you will get 0 rows (hence IF EXISTS will be false). If the table exists, the join will succeed, and you will get 1 row (and true):

 IF EXISTS( SELECT * FROM sys.synonyms s INNER JOIN sys.tables t ON REPLACE(REPLACE(s.base_object_name, '[', ''), ']', '') = t.name WHERE s.name = 'TargetReportingTable') BEGIN -- Does exist END ELSE BEGIN -- Does not exist END 

Replace 'TargetReportingTable' with any synonym you want to check.

Answer 2

The above solutions did not work for me if the synonym referenced another database. I recently discovered the function [fn_my_permissions], which is useful for displaying permissions for a specific database object, so I believe that this can be used as follows:

 IF EXISTS ( select * from sys.synonyms sy cross apply fn_my_permissions(sy.base_object_name, 'OBJECT') WHERE sy.name = 'TargetReportingTable' ) print 'yes - I exist!' 

Answer 3

You can do this with dynamic SQL:

 -- create synonym a for information_schema.tables create synonym a for b declare @exists int = 1; begin try exec('select top 0 * from a'); end try begin catch set @exists = 0; end catch select @exists; 

This does not work with non-dynamic SQL, because the synonym link gets at compile time. This means that the code simply fails with the message and does not fall into the try / catch . With dynamic SQL, the block blocks the error.

Answer 4

Late to the side, I created a request to check the existence of Synonyms and share with you.

 DECLARE @Synonyms table ( ID int identity(1,1), SynonymsDatabaseName sysname, SynonymsSchemaName sysname, SynonymsName sysname, DatabaseName nvarchar(128), SchemaName nvarchar(128), ObjectName nvarchar(128), Remark nvarchar(max), IsExists bit default(0) ) INSERT @Synonyms (SynonymsDatabaseName, SynonymsSchemaName, SynonymsName, DatabaseName, SchemaName, ObjectName) SELECT DB_NAME() AS SynonymsDatabaseName, SCHEMA_NAME(schema_id) AS SynonymsSchemaName, name AS SynonymsName, PARSENAME(base_object_name,3) AS DatabaseName, PARSENAME(base_object_name,2) AS SchemaName, PARSENAME(base_object_name,1) AS ObjectName FROM sys.synonyms SET NOCOUNT ON DECLARE @ID int = 1, @Query nvarchar(max), @Remark nvarchar(max) WHILE EXISTS(SELECT * FROM @Synonyms WHERE ID = @ID) BEGIN SELECT @Query = 'SELECT @Remark = o.type_desc FROM [' + DatabaseName + '].sys.objects o INNER JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE s.name = ''' + SchemaName + ''' AND o.name = ''' + ObjectName + '''' FROM @Synonyms WHERE ID = @ID EXEC sp_executesql @Query, N'@Remark nvarchar(max) OUTPUT', @Remark OUTPUT; UPDATE @Synonyms SET IsExists = CASE WHEN @Remark IS NULL THEN 0 ELSE 1 END, Remark = @Remark WHERE ID = @ID SELECT @ID += 1, @Remark = NULL END SELECT * FROM @Synonyms 

Answer 5

You can check if a synonym exists in your database using the Object_Id function available in SQL Server

 IF OBJECT_ID('YourDatabaseName..YourSynonymName') IS NOT NULL PRINT 'Exist SYNONYM' ELSE PRINT 'Not Exist SYNONYM'