Determine if database tables, attempts, stored procedures, etc. exist

1 determine if the database exists if exists (SELECT * from sys.databases WHERE name = ' database name ') drop database [DB 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 the 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 determine if the temporary table exists if object_id (' tempdb. #临时表名 ') is not a null drop table #临时表名

5 Judging whether the view exists

--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 judgment function exists--determine if the function name to be created exists if exists (select * FR Om 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 '/* xtype represents the parameter type, usually including the following C = CHECK constraint D = default value, or defaults constraint   F = FOREIGN key constraint L = log FN = scalar function IF = inline table function P = stored procedure PK = PRIMARY KEY constraint (type is K) RF = copy Filter stored procedure S = system table TF = Table function TR = trigger U = User Table UQ = UNIQUE constraint (type is K) V = view X = Extended stored procedure */

8 Determine if the 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 Determine whether the column is self-increment if columnproperty (object_id (' table '), ' col ', ' isidentity ') =1 print ' self-increment ' else print ' is not self-increment ' 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 PR int ' does not exist '

11 viewing objects in a database

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