A method for determining whether a table or a temporary table exists in SQL Server _mssql

1, to determine whether the data table exists

Method One:

Use Yourdb;
Go

if OBJECT_ID (n ' tablename ', n ' U ') are NOT null
print ' exists '
else 
print ' does not exist '

For example:

Use Fireweb;
Go

if object_id (n ' temp_tbl ', n ' U ') are NOT null
print ' exists '
else 
print ' does not exist '

Method Two:

Use [instance name] 
go 

IF EXISTS (SELECT * FROM dbo. sysobjects WHERE id = object_id (N ' [table name] ') and OBJECTPROPERTY (ID, ' istable ') = 1) 
print ' exists ' 
ELSE 
print ' does not exist '

For example:

Use Fireweb;
Go to

IF EXISTS (SELECT * FROM dbo. sysobjects WHERE id = object_id (N ' temp_tbl ') and OBJECTPROPERTY (ID, ' istable ') = 1) 
PRINT ' exists ' 
ELSE 
PRINT ' does not exist '

2. Whether the temporary table exists:

Method One:

Use Fireweb;
Go to

if exists (SELECT * from tempdb. sysobjects where id=object_id (' tempdb. # #TEMP_TBL ')
print ' exists ' 
ELSE 
print ' does not exist '

Method Two:

Use Fireweb;
Go

if exists (SELECT * from tempdb.dbo.sysobjects where id = object_id (N ' tempdb). #TEMP_TBL ') and type= ' U ')
print ' exists ' 
ELSE 
print ' does not exist '

The following is a supplemental introduction:

In SQL Server (which should be said to be in all current database products), create a resource such as a table, a view, and a stored procedure that determines whether or not the resource you are creating already exists
In SQL Server, you can generally find results by querying sys.objects system tables, but there are more convenient ways
As follows:

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

As above, the object_id () can be used to quickly achieve the same goal, Tb_table is the name of the resource I am going to create, so I have to determine that the same resource does not exist in the current database
OBJECT_ID () accepts two parameters, the first as shown above, represents the name of the resource, the name of the table above, but often we want to explain what kind of resources we are going to create.
This allows SQL to explicitly look for duplicate names in one type of resource, as follows:

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

The second argument "U" means that the tb_table is a table created by the user, that is, the user_table initials
The type name (Type column) of the various resources is available in Query sys.objects, and here are a few key examples
U-----------User-created tables, different from system tables (user_table)
S-----------system table (system_table)
V-----------Views (view)
P-----------Stored Procedure (sql_stored_procedure)
You can use the SELECT distinct type, type_desc from sys.objects to get all the information

Whether the library exists

if exists (select * from Master.  sysdatabases where name=n ' library name ') print ' exists ' else print ' 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)--Deletes table drop table [dbo]. 
[Table name] Go--------------------column exists if col_length (' Table name ', ' column name ') is NULL PRINT ' not exists ' ELSE PRINT ' exists ' ALTER TABLE name D ROP constraint default value name go ALTER TABLE table name drop column Column name going-------determine if object_id (' tempdb.dbo. #Test ') is present to create a temporary table T Null Begin print ' exists ' end Else begin print ' does not exist ' end-----------------determine whether the stored procedure name to be created exists if exists (SELECT * from Dbo.s ysobjects WHERE id = object_id (N ' [dbo].[ Stored procedure name] and OBJECTPROPERTY (ID, N ' isprocedure ') = 1)--Deletes the stored procedure drop procedure [dbo]. 
[Stored procedure name] Go-----------------determine whether the name of the view to be created exists if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ View name] and OBJECTPROPERTY (ID, N ' isview ') = 1)--Deletes view drop view [dbo]. 
[View name] 
Go-----------------Determine 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.sysob jects WHERE id = object_id (N ' [dbo].[ Function name] and xtype in (n ' FN ', n ' IF ', n ' "TF '))--delete function drop functions [dbo]. 
[function name] Go if col_length (' Table name ', ' column name ') is null print ' does not exist ' select 1 from sysobjects where ID in (select id from syscolumns where NA Me= ' column name ') and name= ' table name '