How can I check if the table behind a synonym exists

Question

I'm trying to create a simple script to dump the results of a complex view out into a table for reporting. I have used synonyms to simplify tweaking the view and table names.

The idea is that the user of the script can put the name of the view they want to use as the source, and the name of the target reporting table in at the start and away they go. If the table doesn't exist then the script should create it. If the table already exists then the script should only copy the records from the view which aren't already in the table over.

The script below covers all those requirements, but I can't find a nice way to check if the table behind the synonym already exists:

CREATE SYNONYM SourceView FOR my_view
CREATE SYNONYM TargetReportingTable FOR my_table

-- Here's 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 I could just get the user of the script to copy the table name into the 'information_schema' line as well as into the synonym at the top, but that leaves scope for error.

I also know I could do something filthy like put the table name into a variable and blat the SQL out as a string, but that makes me feel a bit sick!

Is there a nice elegant SQL way for me to check if the table behind the synonym exists? Or a totally different way to solve to problem?

Solution 1

Not the most elegant of solutions, but you could join the sys.synonyms table to the sys.tables table to check whether the table exists.

If the table does not exist, the join will fail and you will get 0 rows (hence IF EXISTS will be false). If the table does exist, the join will success 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 whichever synonym you wish to check.

Solution 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 showing permissions for a specific database object, so I figure this could 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!'

Solution 3

Late to the party, I have created a query to test out 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

Solution 4

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 doesn't work with non-dynamic SQL, because the synonym reference is caught at compile-time. That means that the code just fails with a message and is not caught by the try/catch block. With dynamic SQL, the block catches the error.

View more solutions

Comments

[Barmar]

Please tag your question with the specific RDBMS, not just "sql"

[Tom]

Sorry, rookie error. Thanks for the edit.

[Jon Egerton]

Can we assume the synonym points at the database that the synonym is in, or might be point to some other db?

[Tom]

At this point it could either (the source is already a backup of the production database, specifically for reporting). This has lead me onto the next problem where my SELECT INTO doesn't work. I'm getting "There is already an object named 'TargetReportingTable' in the database". I'm guessing this is because the SELECT INTO is trying to create a table called TargetReportingTable and isn't looking up the underlying table name behind the synonym. It all works fine when the table already exists.

[Tom]

Thanks. I'm not quite seeing how this helps. It's not the information_schema table I'm trying to get the name of, it's the real table behind the synonym (the one called 'TargetReportingTable' in my script above). Have I missed something?

[Gordon Linoff]

@Tom . . . Those are just examples to show that the code works. In the database I'm testing, b doesn't exist, so it 0 gets assigned to @exists. When set to a table that does exist, 1 is assigned.

[Tom]

Thanks that works well. Although as you say it's not the most beautiful query, it at least means the user of the script doesn't have to paste the table name into multiple places.

[Nathan]

Rather than using REPLACE, a more reliable way to get at the table and schema information would be to use the PARSENAME function. See: msdn.microsoft.com/en-us/library/ms188006.aspx

[Mangusta]

I had to use "sy.sname" in my version of Oracle. Also I didn't find cross apply necessary for my purposes