問題描述

所以我正在創(chuàng)建一個腳本，我在其中鏈接另一個服務(wù)器中的數(shù)據(jù)庫.

So I'm creating a script where I"m linking a database that is in another server.

使用 OBJECT_ID 我想檢查外部鏈接數(shù)據(jù)庫中是否存在一個表，如下所示:

Using OBJECT_ID I want to check whether a table exists in the external linked database like so:

IF OBJECT_ID('[10.0.48.139].[DBNAME].[dbo].tblRating', 'U') IS NOT NULL
    BEGIN
        SET @Sql = N'
        INSERT INTO tblRating
                ( fldSubDivisionID ,
                  fldClientName ,
                  fldAddress ,
                  fldCountryID ,
                  fldComments ,
                  fldCreatedDate ,
                  fldCreatedBy ,
                  fldModifiedDate ,
                  fldModifiedBy
                )
        SELECT * FROM ' + @SourceDB + '.tblRating';
        EXECUTE sp_executesql @Sql;
    END
ELSE
    PRINT 'Table [tblRating] Not Found in Source Database'

即使該表存在于 [10.0.48.139].[DBNAME].[dbo] 中，由于某種原因它總是返回 null.當(dāng)你把一個 Serverlocation 或 ip 放在那里時，我不認(rèn)為 OBJECT_ID 喜歡它.

Even though the table exists in [10.0.48.139].[DBNAME].[dbo] for some reason it always returns null. I don't think OBJECT_ID likes it when you put an Serverlocation or ip in there.

推薦答案

您可以查詢鏈接數(shù)據(jù)庫的 INFORMATION_SCHEMA 來完成此操作.但是，首先，您必須在鏈接的數(shù)據(jù)庫上創(chuàng)建一個視圖，因為它不能像這樣直接查詢:

You could query the INFORMATION_SCHEMA of the linked database to accomplish this. First, though, you'd have to create a view on the linked DB since it cannot be queried directly like so:

CREATE VIEW vwInformationSchemaTables AS SELECT * FROM INFORMATION_SCHEMA.TABLES

然后你可以像這樣從鏈接的數(shù)據(jù)庫中查詢:

Then you can query from the linked database like so:

IF EXISTS(SELECT 1 FROM [10.0.48.139].[DBNAME].dbo.vwInformationSchemaTables WHERE TABLE_NAME='tblRating') 
BEGIN 
  --table exists
END

這篇關(guān)于檢查表是否存在于外部鏈接數(shù)據(jù)庫中的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！