sql-server - tutorial - what is the function of sql server




檢查SQL Server中是否存在表 (14)

    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

我希望這是關於如何使用SQL語句檢查SQL Server 2000/2005中是否存在表的最終討論。

當你回答Google時,你會得到很多不同的答案。 是否有官方/落後和前瞻兼容的方式?

以下是兩種可能的方式。 兩者中哪一個是標準/最好的做法?

第一種方式:

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%' ; 聲明。 我正在尋找類似的東西。


- 創建過程來檢查表是否存在

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);

另外請注意,如果出於任何原因需要檢查臨時表,您可以這樣做:

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

只是想提一下使用OBJECT_ID方法可能會更容易的一種情況。 INFORMATION_SCHEMA視圖是每個數據庫下的對象 -

信息模式視圖是在名為INFORMATION_SCHEMA的特殊模式中定義的。 該架構包含在每個數據庫中。

https://msdn.microsoft.com/en-us/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] ,那麼OBJECT_ID將允許您在開箱即用的情況下執行此操作。 EX-

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/


只需在這裡添加,為開發人員和其他DBA帶來好處

接收@Tablename作為參數的腳本

(可能包含或不包含schemaname),如果schema.table存在,則返回以下信息:

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

我在每次需要測試表或視圖是否存在時都會生成此腳本,以便在其他腳本中使用此腳本,以及何時獲取其object_id以用於其他目的。

當您傳遞空字符串,錯誤的模式名稱或錯誤的表名稱時,會引發錯誤。

這可能在一個過程中,例如返回-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 

SQL Server 2000中,您可以嘗試:

IF EXISTS(SELECT 1 FROM sysobjects WHERE type = 'U' and name = 'MYTABLENAME')
BEGIN
   SELECT 1 AS 'res' 
END

如果您需要處理不同的數據庫:

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

如果有人試圖在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的名稱的對象,並且名為schema的屬性中的模式的源變量名稱是


對於這樣的查詢,最好使用INFORMATION_SCHEMA視圖。 這些視圖(大部分)是跨許多不同數據庫的標準,很少從版本變為版本。

要檢查表是否存在,請使用:

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

對於還沒有找到解決方案的人來說,重要的是要知道: 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";

在這裡發布,因為它是Google的熱門話題。


考慮在一個數據庫中你有一張表t1。 你想在其他數據庫上運行腳本,比如 - 如果t1存在,那麼除了創建t1之外別無其他。 要做到這一點打開Visual Studio並執行以下操作:

右鍵單擊t1,然後選擇腳本表作為,然後DROP和創建到,然後新建查詢編輯器

你會發現你想要的查詢。 但是在執行該腳本之前,不要忘記在查詢中註釋掉drop語句,因為如果已經存在一個,您不想創建新語句。

謝謝


請看下面的方法,

方法1:使用INFORMATION_SCHEMA.TABLES視圖

我們可以編寫如下查詢來檢查當前數據庫中是否存在客戶表。

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

方法2:使用OBJECT_ID()函數

我們可以使用如下所示的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.objects / sys.tables而不是sys.sysobjects系統表。

  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/ : http://sqlhints.com/2014/04/13/how-to-check-if-a-table-exists-in-sql-server/


IF EXISTS 
(
    SELECT  * 

    FROM    INFORMATION_SCHEMA.TABLES 

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

IF OBJECT_ID('mytablename') IS NOT NULL 




tsql