SQL server to determine whether a table or temporary table exists, SQL server

SQL server to determine whether a table or temporary table exists, SQL server

1. Determine whether a data table exists

Method 1:

Use yourdb; goif object_id (N 'tablename', N 'U') is not nullprint 'There is 'else' print' doesn't exist'

For example:

Use fireweb; goif object_id (n'temp _ TBL ', n'u') is not nullprint' There is 'else' print 'doesn't exist'

Method 2:

USE [Instance name] go if exists (SELECT * FROM dbo. sysObjects where id = object_id (n' [Table name] ') and objectproperty (ID, 'istable') = 1) PRINT' 'else PRINT 'Doesn't exist'

For example:

Use fireweb; goIF EXISTS (SELECT * FROM dbo. sysObjects where id = object_id (n'temp _ TBL ') and objectproperty (ID, 'istable') = 1) PRINT' 'else PRINT 'Doesn't exist'

2. Whether a temporary table exists:

Method 1:

Use fireweb; goif exists (select * from tempdb .. sysobjects where id = object_id ('tempdb .. # TEMP_TBL ') PRINT 'else PRINT' does not exist'

Method 2:

Use fireweb; goif exists (select * from tempdb. dbo. sysobjects where id = object_id (n' tempdb .. # TEMP_TBL ') and type = 'U') PRINT' 'else PRINT 'does not exist'

The following is a supplementary introduction:

Create a resource such as a table, view, and storage in sqlserver (it should be said that all database products currently) to check whether the created resource already exists.
In sqlserver, You can query the sys. objects system table to obtain the result. However, you can use a more convenient method.
As follows:

  if  object_id('tb_table') is not null     print 'exist'   else     print'not exist' 

As shown above, object_id () can be used to quickly achieve the same purpose. tb_table is the name of the resource to be created. Therefore, you must first determine that the current database does not have the same resource.
Object_id () can take two parameters. The first one, as shown above, represents the name of the resource. The above is the name of the table, but we often need to describe what type of resource we want to create,
In this way, the SQL statement can explicitly search for duplicate names in a type of resource, as shown below:

  if  object_id('tb_table','u') is not null     print 'exist'   else     print'not exist' 

The second parameter "u" indicates that tb_table is the table created by the user, that is, the abbreviation of USER_TABLE
You can obtain the TYPE names (TYPE columns) of various resources in sys. objects. Here are several main examples.
U ----------- user-created table, different from the system table (USER_TABLE)
S ----------- system table (SYSTEM_TABLE)
V ----------- VIEW)
P ----------- Stored Procedure (SQL _STORED_PROCEDURE)
You can use select distinct type and type_desc from sys. objects to obtain all information.

Whether the library exists

If exists (select * from master .. sysdatabases where name = n' database name') print 'exists' elseprint 'not exists' ------------- -- determine whether the table name to be created exists if exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [table name] ') and OBJECTPROPERTY (id, n' isusertable') = 1) -- delete a table drop table [dbo]. [Table name] GO --------------- whether the Column exists IF COL_LENGTH ('table name', 'column name ') is nullprint 'not exists' exists 'alter table Name drop constraint default name go alter table Name drop column name go ----- -- determine whether a temporary table to be created exists If Object_Id (' tempdb. dbo. # test') Is Not NullBeginprint 'There Is 'endelsebeginprint' Not 'end ------------- -- determine whether the name of the stored procedure to be created exists if exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [stored procedure name] ') and OBJECTPROPERTY (id, n' IsProcedure') = 1) -- delete stored procedure drop procedure [dbo]. [stored procedure name] GO --------------- -- determines whether the view name to be created exists if exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [view name] ') and OBJECTPROPERTY (id, n' isview') = 1) -- delete a view drop view [dbo]. [view name] GO --------------- -- determines whether the function name to be created exists if exists (select * from sysobjects where xtype = 'fn 'and name = 'function name ') if exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [function name] ') and xtype in (n'fn', n'if', n'tf') -- delete the function drop function [dbo]. [function name] GO if col_length ('table name', 'column name ') is nullprint 'does not exist 'select 1 from sysobjects where id in (select id from syscolumns where name = 'column name') and name = 'table name'