SQL Server determines whether a database, table, column, or view exists _mssql

1 determine if the database exists

if exists (SELECT * from sys.databases WHERE name = ' database name ')
Drop DB [Database name]

2 Determine if the table exists

if exists (select * from sysobjects where id = object_id (n ' [table name] ') and OBJECTPROPERTY (ID, n ' isusertable ') = 1)
drop table [table name]

3 determine if a stored procedure exists

if exists (select * from sysobjects where id = object_id (n ' [Stored procedure name] ') and OBJECTPROPERTY (ID, N ' isprocedure ') = 1)
drop procedure [Stored procedure name]

4 to determine whether a temporary table exists

If object_id (' tempdb.. #临时表名 ') is not null
drop table #临时表名

5 determining whether a view exists

--to determine whether there is a ' MyView52 ' this attempt
IF EXISTS (SELECT table_name from INFORMATION_SCHEMA. Views WHERE table_name = N ' MyView52 ')
PRINT ' presence '
Else
PRINT ' does not exist '

6 determine if the function exists

--Determine if the name of the function to be created exists
if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ Function name] and xtype in (n ' FN ', n ' IF ', n ' TF '))
Drop function [dbo]. [function name]

7 getting user-created object information

SELECT [name],[id],crdate from sysobjects where xtype= ' U '

8 Determine whether a column exists

if exists (SELECT * from syscolumns where id=object_id (' table name ') and name= ' column name ')
ALTER TABLE name drop column column name

9 Determine if the column is self added

If ColumnProperty (object_id (' table '), ' col ', ' isidentity ') =1
print ' Self-add column '
Else
print ' is not a self-adding column '

SELECT * from Sys.columns WHERE object_id=object_id (' table name ') and Is_identity=1

10 determine if an index exists in the table

if exists (SELECT * from sysindexes where id=object_id (' table name ') and name= ' index name ')
print ' presence '
Else
print ' does not exist '

Deletes index table name. Index Name

Or: Drop index index on table name (seemingly 2000 not possible)

11 viewing objects in a database

SELECT * from sys.sysobjects where name= ' object name ' select * from sys.sysobjects where name= ' object name '