如何检查同义词后面的表是否存在 [英] How can I check if the table behind a synonym exists

问题描述

我正在尝试创建一个简单的脚本来将复杂视图的结果转储到表格中以进行报告.我使用同义词来简化调整视图和表名称.

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

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

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.

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

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?

推荐答案

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

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.

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

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

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

Replace 'TargetReportingTable' with whichever synonym you wish to check.

这篇关于如何检查同义词后面的表是否存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！