SQL Server determines if there is a database, table, column, view

Original: SQL Server determines if there is a database, table, column, view

1 determining whether a database exists
if exists (SELECT * from sys.databases WHERE name = ' database name ')
drop database [DB name]

2 Determining whether a 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 determining 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 determining whether a temporary table exists
If object_id (' tempdb.. #临时表名 ') is not null
drop table #临时表名

5 Judging whether the view exists

--To determine if there is ' MyView52 ' this attempt
IF EXISTS (SELECT table_name from INFORMATION_SCHEMA. Views WHERE table_name = N ' MyView52 ')
PRINT ' exists '
Else
PRINT ' does not exist '
6 determining whether a 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 determining whether a column exists
if exists (SELECT * from syscolumns where id=object_id (' table name ') and name= ' column name ')
ALTER TABLE table name drop column name

9 Judging whether a column is self-increment
If ColumnProperty (object_id (' table '), ' col ', ' isidentity ') =1
print ' Self-increment column '
Else
print ' is not 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 ' exists '
Else
print ' does not exist '

Delete index drop the index table name. Indexed name

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

11 viewing objects in a database

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