webdevqa.jp.net

SQL Serverにテーブルが存在するかどうかを確認します

私はこれがSQL文を使用してSQL Server 2000/2005にテーブルが存在するかどうかをチェックする方法に関する最終的な議論になりたいと思います。

あなたが答えをグーグルするとき、あなたはとても多くの異なった答えを得る。それを行うための公式の/後方互換性のある方法はありますか?

これを行う2つの方法があります。 2つのうちどちらが標準/最善の方法ですか?

最初の方法:

IF EXISTS (SELECT 1 
           FROM INFORMATION_SCHEMA.TABLES 
           WHERE TABLE_TYPE='BASE TABLE' 
           AND TABLE_NAME='mytablename') 
   SELECT 1 AS res ELSE SELECT 0 AS res;

第二の方法:

IF OBJECT_ID (N'mytablename', N'U') IS NOT NULL 
   SELECT 1 AS res ELSE SELECT 0 AS res;

MySQL はシンプルな

SHOW TABLES LIKE '%tablename%'; 

ステートメント。私は似たようなものを探しています。

985
Vincent

このようなクエリには、常にINFORMATION_SCHEMAビューを使用するのが最善です。これらのビューは(大部分)多くの異なるデータベースにわたって標準的であり、バージョンごとに変わることはめったにありません。

テーブルが存在するかどうかを確認するには、次のようにします。

IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'TheSchema' 
                 AND  TABLE_NAME = 'TheTable'))
BEGIN
    --Do Stuff
END
1191
akmad

また、何らかの理由で一時テーブルをチェックする必要がある場合は、これを実行できます。

if OBJECT_ID('tempdb..#test') is not null
 --- temp table exists
249
James Bloomer

私が覚えている限り、私たちは常にOBJECT_IDスタイルを使います。

IF OBJECT_ID('*objectName*', 'U') IS NOT NULL 
210
Bob King

以下のアプローチを見てください。

アプローチ1:INFORMATION_SCHEMA.TABLESビューを使用する

Customersテーブルが現在のデータベースに存在するかどうかを確認するために以下のようなクエリを書くことができます。

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'Customers')
BEGIN
    PRINT 'Table Exists'
END

アプローチ2:OBJECT_ID()関数を使う

Customersテーブルが現在のデータベースに存在するかどうかを確認するために、以下のようにOBJECT_ID()関数を使用できます。

IF OBJECT_ID(N'dbo.Customers', N'U') IS NOT NULL
BEGIN
    PRINT 'Table Exists'
END

アプローチ3:sys.Objectsカタログビューを使用する

以下に示すように、Sys.Objectsカタログビューを使用してテーブルの存在を確認できます。

IF EXISTS(SELECT 1 FROM sys.Objects WHERE  Object_id = OBJECT_ID(N'dbo.Customers') AND Type = N'U')
BEGIN
   PRINT 'Table Exists'
END

方法4:sys.Tablesカタログビューを使用する

次に示すように、Sys.Tablesカタログビューを使用してテーブルの存在を確認できます。

 IF EXISTS(SELECT 1 FROM sys.Tables WHERE  Name = N'Customers' AND Type = N'U')
 BEGIN
      PRINT 'Table Exists'
 END

方法5:sys.sysobjectsシステムテーブルを使用しない

Sys.sysobjectsシステムテーブルを直接使用しないでください。直接アクセスすることは将来のSql Serverのバージョンでは推奨されなくなります。 MicrosoftのBOLリンクに従って、Microsoftはsys.sysobjectsシステムテーブルの代わりにカタログビューsys.objects/sys.tablesを直接使用することを推奨しています。

  IF EXISTS(SELECT name FROM sys.sysobjects WHERE Name = N'Customers' AND xtype = N'U')
  BEGIN
     PRINT 'Table Exists'
  END

参照元: http://sqlhints.com/2014/04/13/how-to-check-if-a-table-exists-in-sql-server/ /

107
BrainCoder

別のデータベースでテーブルを探す:

if exists (select * from MyOtherDatabase.sys.tables where name = 'MyTable')
    print 'Exists'
34
Larry Leonard

OBJECT_IDメソッドを使用する方が多少簡単であると思われる1つの状況について言及したいだけでした。 INFORMATION_SCHEMAビューは各データベースの下のオブジェクトです。

情報スキーマビューは、INFORMATION_SCHEMAという特別なスキーマで定義されています。このスキーマは各データベースに含まれています。

https://msdn.Microsoft.com/ja-jp/library/ms186778.aspx

したがって、あなたがアクセスするすべてのテーブル

IF EXISTS (SELECT 1 
           FROM [database].INFORMATION_SCHEMA.TABLES 
           WHERE TABLE_TYPE='BASE TABLE' 
           AND TABLE_NAME='mytablename') 
   SELECT 1 AS res ELSE SELECT 0 AS res;

[database]にあるものだけを反映します。毎回[database]を動的に変更することなく、another database内のテーブルが存在するかどうかをチェックしたい場合は、OBJECT_IDを使用するとそのまま使用できます。元

IF OBJECT_ID (N'db1.schema.table1', N'U') IS NOT NULL 
   SELECT 1 AS res ELSE SELECT 0 AS res;

同様に動作します

IF OBJECT_ID (N'db2.schema.table1', N'U') IS NOT NULL 
   SELECT 1 AS res ELSE SELECT 0 AS res;

SQL Server 2016編集

2016年以降、マイクロソフトはdropステートメントにif existsキーワードを追加することにより、ドロップする前に存在しないオブジェクトをチェックする機能を簡素化しました。例えば、

drop table if exists mytablename

1行のコードで、OBJECT_ID/INFORMATION_SCHEMAラッパーと同じことをします。

https://blogs.msdn.Microsoft.com/sqlserverstorageengine/2015/11/03/drop-if-exists-new-thing-in-sql-server-2016/ /

22
iliketocode
IF OBJECT_ID('mytablename') IS NOT NULL 
20
sansalk

情報スキーマを使用するのがSQL標準の方法ですので、それをサポートするすべてのデータベースで使用する必要があります。

15
Vinko Vrsalovic

異なるデータベースで作業する必要がある場合

DECLARE @Catalog VARCHAR(255)
SET @Catalog = 'MyDatabase'

DECLARE @Schema VARCHAR(255)
SET @Schema = 'dbo'

DECLARE @Table VARCHAR(255)
SET @Table = 'MyTable'

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES   
    WHERE TABLE_CATALOG = @Catalog 
      AND TABLE_SCHEMA = @Schema 
      AND TABLE_NAME = @Table))
BEGIN
   --do stuff
END
10
Even Mien
IF EXISTS 
(
    SELECT   * 
    FROM     sys.objects 
    WHERE    object_id = OBJECT_ID(N'[dbo].[Mapping_APCToFANavigator]') 
             AND 
             type in (N'U')
)
BEGIN

    -- Do whatever you need to here.

END

上記のコードでは、テーブル名はMapping_APCToFANavigatorです。

10

私はそれが古い質問であることを知っています、しかしあなたがそれを頻繁に呼ぶつもりであるならば、私はこの可能性を見つけました。

create procedure Table_Exists
@tbl varchar(50)
as
return (select count(*) from sysobjects where type = 'U' and name = @tbl)
go
8
dko

開発者と仲間のDBAのためにここに追加するだけです。

パラメータとして@Tablenameを受け取るスクリプト

schema.tableが存在する場合(これはschemanameを含む場合も含まない場合もあります)、以下の情報を返します。

the_name                object_id   the_schema  the_table       the_type
[Facts].[FactBackOrder] 758293761   Facts       FactBackOrder   Table

このスクリプトは、テーブルまたはビューが存在するかどうかをテストする必要があるたびに他のスクリプト内で使用されるように作成し、存在する場合はそのobject_idを他の目的に使用します。

空の文字列、間違ったスキーマ名、または間違ったテーブル名を渡した場合はエラーが発生します。

これはプロシージャ内にあり、たとえば-1を返すことがあります。

例として、私のデータウェアハウスデータベースの1つに "Facts.FactBackOrder"というテーブルがあります。

これは私がこれを達成した方法です:

PRINT 'THE SERVER IS ' + @@SERVERNAME
--select db_name()
PRINT 'THE DATABASE IS ' + db_NAME() 
PRINT ''
GO

SET NOCOUNT ON
GO

--===================================================================================
-- @TableName is the parameter
-- the object we want to deal with (it might be an indexed view or a table)
-- the schema might or might not be specified
-- when not specified it is DBO
--===================================================================================

DECLARE @TableName SYSNAME

SELECT @TableName = 'Facts.FactBackOrder'
--===================================================================================
--===================================================================================
DECLARE @Schema SYSNAME
DECLARE @I INT
DECLARE @Z INT 

SELECT @TableName = LTRIM(RTRIM(@TableName))
SELECT @Z = LEN(@TableName)

IF (@Z = 0) BEGIN

            RAISERROR('Invalid @Tablename passed.',16,1)

END 

SELECT @I = CHARINDEX('.',@TableName )
--SELECT @TableName ,@I

IF @I > 0 BEGIN

        --===================================================================================
        -- a schema and table name have been passed
        -- example Facts.FactBackOrder 
        -- @Schema = Fact
        -- @TableName = FactBackOrder
        --===================================================================================

   SELECT @Schema    = SUBSTRING(@TABLENAME,1,@I-1)
   SELECT @TableName = SUBSTRING(@TABLENAME,@I+1,@[email protected])



END
ELSE BEGIN

        --===================================================================================
        -- just a table name have been passed
        -- so the schema will be dbo
        -- example Orders
        -- @Schema = dbo
        -- @TableName = Orders
        --===================================================================================

   SELECT @Schema    = 'DBO'     


END

        --===================================================================================
        -- Check whether the @SchemaName is valid in the current database
        --===================================================================================

IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.SCHEMATA K WHERE K.[SCHEMA_NAME] = @Schema ) BEGIN

            RAISERROR('Invalid Schema Name.',16,1)

END 

--SELECT @Schema  as [@Schema]
--      ,@TableName as [@TableName]


DECLARE @R1 TABLE (

   THE_NAME SYSNAME
  ,THE_SCHEMA SYSNAME
  ,THE_TABLE SYSNAME
  ,OBJECT_ID INT
  ,THE_TYPE SYSNAME
  ,PRIMARY KEY CLUSTERED (THE_SCHEMA,THE_NAME)

)

;WITH RADHE_01 AS (
SELECT QUOTENAME(SCHEMA_NAME(O.schema_id)) + '.' + QUOTENAME(O.NAME) AS [the_name]
      ,the_schema=SCHEMA_NAME(O.schema_id)
      ,the_table=O.NAME
      ,object_id =o.object_id 
      ,[the_type]= CASE WHEN O.TYPE = 'U' THEN 'Table' ELSE 'View' END 
from sys.objects O
where O.is_ms_shipped = 0
AND O.TYPE IN ('U','V')
)
INSERT INTO @R1 (
   THE_NAME 
  ,THE_SCHEMA 
  ,THE_TABLE 
  ,OBJECT_ID
  ,THE_TYPE 
)
SELECT  the_name
       ,the_schema
       ,the_table
       ,object_id
       ,the_type
FROM RADHE_01
WHERE the_schema = @Schema 
  AND the_table  = @TableName

IF (@@ROWCOUNT = 0) BEGIN 

             RAISERROR('Invalid Table Name.',16,1)

END 
ELSE BEGIN

    SELECT     THE_NAME 
              ,THE_SCHEMA 
              ,THE_TABLE 
              ,OBJECT_ID
              ,THE_TYPE 

    FROM @R1

END 
5

SQL Server 2000 に試すことができる:

IF EXISTS(SELECT 1 FROM sysobjects WHERE type = 'U' and name = 'MYTABLENAME')
BEGIN
   SELECT 1 AS 'res' 
END
4
dipi evil
    IF OBJECT_ID (N'dbo.T', N'U') IS NOT NULL 
      BEGIN 
          print 'deleted table';
          drop table t 
      END
    else 
      begin 
          print 'table not found' 
      end

Create table t (id int identity(1,1) not null, name varchar(30) not null, lastname varchar(25) null)
insert into t( name, lastname) values('john','doe');
insert into t( name, lastname) values('rose',NULL);

Select * from t
1   john    doe
2   rose    NULL

-- clean
drop table t
3
BTE
IF EXISTS 
(
    SELECT  * 

    FROM    INFORMATION_SCHEMA.TABLES 

    WHERE   TABLE_SCHEMA = 'PutSchemaHere'     
            AND  
            TABLE_NAME   = 'PutTableNameHere'
)
3
Moccassin

まだ解決策を見つけていない人にとって知っておくべき重要なこと:SQL server!= MYSQL。あなたがMYSQLでそれをやりたいなら、それは非常に簡単です。

    $sql = "SELECT 1 FROM `db_name`.`table_name` LIMIT 1;";
    $result = mysql_query($sql);
    if( $result == false )
        echo "table DOES NOT EXIST";
    else
        echo "table exists";

これがグーグルのトップヒットだからここに投稿する。

2
Blauhirn
IF EXISTS (   SELECT * FROM   dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.TableName') AND OBJECTPROPERTY(id, N'IsUserTable') = 1 )
BEGIN
  SELECT * FROM dbo.TableName;
END
GO
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE 
TABLE_CATALOG = 'Database Name' and
TABLE_NAME = 'Table Name' and 
TABLE_SCHEMA = 'Schema Name') -- Database and Schema name in where statement can be deleted

BEGIN
--TABLE EXISTS
END

ELSE BEGIN
--TABLE DOES NOT EXISTS
END
1
select name from SysObjects where xType='U' and name like '%xxx%' order by name
1
MarceloMadnezz

- - テーブルが存在するかどうかを確認するプロシージャを作成します


DELIMITER $$

DROP PROCEDURE IF EXISTS `checkIfTableExists`;

CREATE PROCEDURE checkIfTableExists(
    IN databaseName CHAR(255),
    IN tableName CHAR(255),
    OUT boolExistsOrNot CHAR(40)
)

  BEGIN
      SELECT count(*) INTO boolExistsOrNot FROM information_schema.TABLES
      WHERE (TABLE_SCHEMA = databaseName)
      AND (TABLE_NAME = tableName);
  END $$

DELIMITER ;

- - 使い方:テーブルの移行があるか確認する


 CALL checkIfTableExists('muDbName', 'migrations', @output);
0
Mathieu Dierckx

これが「究極の」議論になるのであれば、Larry Leonardのスクリプトは、サーバーがリンクされている場合にもリモートサーバーに照会できることに注意してください。

if exists (select * from REMOTE_SERVER.MyOtherDatabase.sys.tables where name = 'MyTable')
    print 'Exists'
0
user3651072

誰かがlinq to sql(または特にlinqpad)でこれと同じことをやろうとしているのなら、システムテーブルとビューを含めて次のコードを実行するオプションをオンにします。

let oSchema = sys.Schemas.FirstOrDefault(s=>s.Name==a.schema )
where oSchema !=null
let o=oSchema!=null?sys.Objects.FirstOrDefault (o => o.Name==a.item && o.Schema_id==oSchema.Schema_id):null
where o!=null

itemという名前のプロパティ内に名前を持つオブジェクトがあり、source変数の名前がaであるschemaという名前のプロパティ内にスキーマがあるとします。

0
Maslow

INFORMATIONAL_SCHEMEとOBJECT_IDから選択することに問題がありました。それがODBCドライバの問題なのか、それとも何かなのか、私にはわかりません。SQLManagement Studioからの問い合わせは、どちらも問題ありませんでした。

これが解決策です。

SELECT COUNT(*) FROM <yourTableNameHere>

そのため、クエリが失敗した場合、おそらくデータベースにそのようなテーブルが存在しません(またはアクセス権がありません)。

チェックはODBCドライバを扱うSQL executorによって返された値(私の場合は整数)を比較することによって行われます。

if (sqlexec(conectionHandle, 'SELECT COUNT(*) FROM myTable') == -1) {
  // myTable doesn't exist..
}
0
Michael Quad

このクエリを実行して、テーブルがデータベースに存在するかどうかを確認します。

IF(SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'YourTableName') IS NOT NULL
PRINT 'Table Exists';
0
S Krishna