SQL Server various judgments exist (table name, function, stored procedure, etc.) _mssql

How to determine the presence of a table or database in SQL Server, but in actual use, you need to determine the status State bit:
Some of these state bits can be set by the user using sp_dboption (read only, dbo use only, single user, and so on):

1 = autoclose; use sp_dboption settings. The database is completely shut down and its resources are released after the last user logs off.
4 = select into/bulkcopy; use sp_dboption settings. Allows you to use the Select into statement and fast bulk copy.
8 = trunc. Log on chkpt; use sp_dboption settings. If the database is in log truncation mode, the checkpoint truncates the inactive portion of the log. This option can only be set for the master database. = Torn page detection, using sp_dboption settings. You can detect torn pages.
Loading =.
Recovery = Pre.
128 = recovering.
256 = not recovered.
offline = use sp_dboption settings. The database will be offline.
1024 = read Only; use sp_dboption settings. Users can only read data in the database and cannot modify it.
2048 = dbo use only; sp_dboption settings. Only the database owner can use the database.
4096 = Single user, using sp_dboption settings. Only one user can access the database at a time.
32768 = emergency mode.
4194304 = autoshrink.
1073741824 = cleanly shutdown.

Multiple bits can be opened at the same time.

For example: To determine whether a database is offline
SELECT * from master.dbo.sysdatabases where name= ' pubs ' and status<>512

To determine whether a 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 ' presence '
Else
print ' does not exist '

In SQL Server, determine if a field exists in a table:
if exists (SELECT * from syscolumns where name= ' colname1 ' and id=object_id (' database name. Owner. Table name '))
print ' presence '
Else
print ' does not exist '
colname1 field exists on behalf of table tablename1
Cases:
SELECT * from syscolumns where name= ' Test ' and id=object_id (' Dbo.test ')

In access to determine whether a Table object exists:
In fact, Access databases also have system tables, with object names stored
Select Count (*) as Qty from Msysobjects Where ((msysobjects.name) like ' table name ');

Copy Code code as follows:

Whether the library exists
if exists (select * from Master. sysdatabases where name=n ' library name ')
print ' exists '
Else
print ' NOT EXISTS '
---------------
--Determine if the table name you want to create exists
if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ Table name] and OBJECTPROPERTY (ID, N ' isusertable ') = 1)
--Delete Table
drop table [dbo]. [Table name]
Go
---------------
Whether-----column exists
IF col_length (' table name ', ' column name ') is NULL
PRINT ' NOT EXISTS '
ELSE
PRINT ' exists '
ALTER TABLE name DROP constraint default value name
Go
ALTER TABLE name drop column column name
Go
-----
--Determine if there is a temporary table to create
If object_id (' tempdb.dbo. #Test ') is not Null
Begin
print ' presence '
End
Else
Begin
print ' does not exist '
End
---------------
--Determine if the stored procedure name you want to create exists
if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ Stored procedure name] ') and OBJECTPROPERTY (ID, N ' isprocedure ') = 1)
--Deleting stored procedures
drop procedure [dbo]. [Stored procedure name]
Go
---------------
--Determine if the name of the view you want to create 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 if 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 function
Drop function [dbo]. [function name]
Go
If Col_length (' Table name ', ' column name ') is null
print ' does not exist '
Select 1 from sysobjects where IDs in (select IDs from syscolumns where name= ' column names ') and name= ' table names '