SQL Server checks whether a table exists (such as the table name, function, and stored procedure)

How to determine the existence of a table or database in SQL server, but in actual use, you need to determine the Status bit:
Some status bits can be set by the user using sp_dboption (read only, dbo use only, single user, etc:

1 = autoclose; Use sp_dboption to set. The database is completely closed, and its resources are released after the last user logs out.
4 = select into/bulkcopy; Use sp_dboption to set. The Select INTO statement and quick large-capacity replication are allowed.
8 = trunc. log on chkpt; Use sp_dboption to set. If the database is in log truncation mode, the check point truncates the non-active part of the log. This option can only be set for the master database. 16 = torn page detection, which is set using sp_dboption. Detect incomplete pages.
32 = loading.
64 = pre recovery.
128 = recovering.
256 = not recovered.
512 = offline; Use sp_dboption to set. The database is offline.
1024 = read only; Use sp_dboption. Users can only read data in the database and cannot modify it.
2048 = dbo use only; use sp_dboption to set. Only the database owner can use the database.
4096 = single user; Use sp_dboption to set. Only one user can access the database at a time.
32768 = emergency mode.
4194304 = autoshrink.
1073741824 = cleanly shutdown.

You can open multiple locations at the same time.

For example, determine whether a database is offline.
Select * From master. dbo. sysdatabases where name = 'pubs' and status <> 512

Check whether the table object exists in SQL Server:
Select count (*) from sysobjects where id = object_id ('database name. Owner. Table name ')

If exists
(Select count (*) from sysobjects where id = object_id ('database name. Owner. Table name '))
Print 'exist'
Else
Print 'nonexistent'

In SQL Server, check whether the fields in the table exist:
If exists (select * from syscolumns where name = 'colname1' and id = object_id ('database name. Owner. Table name '))
Print 'exist'
Else
Print 'nonexistent'
Indicates that the table tablename1 contains the colname1 field.
Example:
Select * from syscolumns where name = 'test' and id = object_id ('dbo. test ')

Access to determine whether the table object exists:
In fact, the Access database also has system tables and stores object names.
Select Count (*) AS Qty FROM MSysObjects Where (MSysObjects. Name) Like 'table name ');

Copy codeThe Code is as follows: whether the library exists
If exists (select * from master .. sysdatabases where name = N 'database 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)
-- Delete a table
Drop table [dbo]. [table name]
GO
---------------
----- Whether the Column exists
IF COL_LENGTH ('table name', 'column name') IS NULL
PRINT 'not exists'
ELSE
PRINT 'exists'
Alter table Name drop constraint default name
Go
Alter table Name drop column name
Go
-----
-- Determine whether the temporary table to be created exists
If Object_Id ('tempdb. dbo. # test') Is Not Null
Begin
Print 'exist'
End
Else
Begin
Print 'nonexistent'
End
---------------
-- Determine whether the stored procedure name 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 a stored procedure
Drop procedure [dbo]. [stored procedure name]
GO
---------------
-- Determine 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 View
Drop view [dbo]. [view name]
GO
---------------
-- Determine whether the name of the function 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 a function
Drop function [dbo]. [function name]
GO
If col_length ('table name', 'column name') is null
Print 'nonexistent'
Select 1 from sysobjects where id in (select id from syscolumns where name = 'column name') and name = 'table name'