SQL Server determines whether an object exists
1 Determine if the database has SQL code
if exists (SELECT * from sys.databases WHERE name = ' database name ') drop database [DB name]
2 Determine if the table has SQL code
if exists (select * from sysobjects where id = object_id (' table name ') and OBJECTPROPERTY (ID, ' isusertable ') = 1) drop table [table name]
if exists (select * from sysobjects WHERE name = ' table name ' and xtype = ' U ') drop table [table name]
3 determine if the stored procedure has SQL code
if exists (select * from sysobjects where id = object_id (' [Stored procedure name] ') and OBJECTPROPERTY (ID, ' isprocedure ') = 1) Drop proced ure [Stored Procedure name]
if exists (select 1 from sysobjects where name = ' Stored procedure name ' and xtype = ' P ') drop procedure [Stored procedure name]
4 to determine if the temporary table has SQL code
If object_id (' tempdb.. #临时表名 ') is not a null drop table #临时表名
5 Judging whether the view has SQL code
--sql Server 2000
IF EXISTS (SELECT * from sysviews WHERE object_id = ' [dbo].[ View name] ')
--sql Server 2005
IF EXISTS (SELECT * from sys.views WHERE object_id = ' [dbo].[ View name] ')
6 determining if a function has SQL code
--Determine if the name of the function to be created exists
if exists (SELECT * from dbo.sysobjects WHERE id = object_id (' [dbo].[ Function name] ') and xtype in (' FN ', ' IF ', ' TF ')
Wk_ad_begin ({pid:21}); Wk_ad_after (, function () {$ ('. Ad-hidden '). Hide ();}, function () {$ ('. Ad-hidden '). Show ();});
Drop function [dbo]. [function name]
if exists (select 1 from sysobjects where name = ' function name ' and xtype in (' FN ', ' IF ', ' TF ')) drop function [function name]
7 getting user-created object information SQL code
SELECT 1 from sysobjects where name = [object name] and xtype= '/*
The xtype represents the parameter types, usually including the following C = CHECK constraints
D = defaults or default 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 has SQL code
if exists (SELECT * from syscolumns where id=object_id (' table name ') and name= ' column name ') ALTER TABLE table name drop column name
9 Determining if a column is self-adding SQL code
If ColumnProperty (object_id (' table '), ' col ', ' isidentity ') =1 print ' self-increment ' else
print ' is not self-adding column '
SELECT * from syscolumns WHERE object_id=object_id (' table name ') and Is_identity=1
10 determine if the index SQL code 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 '
11 Viewing object SQL code in the database
SELECT * from sys.sysobjects WHERE name= ' object name '
12 See if the primary key exists in the table and, if present, drop it declare @pk varchar (100)
Select @pk =name from sysobjects where parent_obj=object_id (' table name ') and xtype= ' PK ' if @pk is not null
EXEC (' ALTER TABLE table name drop ' + @pk)
Check if the SQL object exists