SQL 如何检查同义词后面的表是否存在

How can I check if the table behind a synonym exists

提问by Tom

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.

我知道我可以让脚本的用户将表名复制到“information_schema”行以及顶部的同义词中，但这留下了错误的余地。

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!

我也知道我可以做一些肮脏的事情，比如将表名放入一个变量中，然后将 SQL 作为字符串输出，但这让我觉得有点恶心！

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?

有没有一种很好的优雅 SQL 方式来检查同义词后面的表是否存在？还是用完全不同的方式解决问题？

采纳答案by Martin

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

不是最优雅的解决方案，但您可以将sys.synonyms表加入sys.tables表以检查表是否存在。

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

如果表不存在，连接将失败，您将获得 0 行（因此IF EXISTS为假）。如果该表确实存在，则连接将成功，您将获得 1 行（并且为真）：

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.

替换'TargetReportingTable'为您要检查的任何同义词。

回答by EquiJoin

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:

如果同义词引用了另一个数据库，则上述解决方案对我不起作用。我最近发现了 [fn_my_permissions] 函数，它对于显示特定数据库对象的权限很有用，因此我认为可以按如下方式使用它：

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!'

回答by Eric

Late to the party, I have created a query to test out the existence of Synonymsand share with you.

晚会晚了，我创建了一个查询来测试是否存在Synonyms并与您分享。

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

回答by Gordon Linoff

You can do this with dynamic SQL:

您可以使用动态 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/catchblock. With dynamic SQL, the block catches the error.

这不适用于非动态 SQL，因为同义词引用是在编译时捕获的。这意味着代码只是失败并显示一条消息，而不会被try/catch块捕获。使用动态 SQL，该块会捕获错误。

回答by Rodolfo Faquin

You can test if Synonym exists in your database using the Object_Id function avaliable in SQL Server

您可以使用 SQL Server 中的 Object_Id 函数测试您的数据库中是否存在 Synonym

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

回答by Client Relations

Another simpler solution:

另一个更简单的解决方案：

IF (EXISTS (SELECT * FROM sys.synonyms WHERE NAME ='mySynonymName'))
BEGIN
    UPDATE mySynonymName
    SET [Win] = 1
END

In this case, I do database setup first. I drop all Synonyms in my database (database1) first, then run a SPROC to create synonyms for all tables in the destination database(database2). Some SPROCS in database1 call on tables in DB2. If table doesnt exist in DB2 the SPROC fails. If table doesnt exist in DB2, the synonmy is not automatically created on database setup. So I just use the above to check if the Synonym exist, and skip that part of the SPROC if the Synonym is not present.

在这种情况下，我首先进行数据库设置。我首先删除数据库 (database1) 中的所有同义词，然后运行 ​​SPROC 为目标数据库 (database2) 中的所有表创建同义词。database1 中的某些 SPROCS 调用 DB2 中的表。如果 DB2 中不存在表，则 SPROC 将失败。如果 DB2 中不存在表，则不会在数据库设置时自动创建同义词。所以我只是使用上面的来检查同义词是否存在，如果同义词不存在，则跳过 SPROC 的那部分。