SQLServer checks whether the object exists and does not exist. 1 checks whether the database has SQL code ifexists (select * fromsys. databaseswherename 'database name') dropdatabase [database name] ifexists (select * fromsys. databaseswherename 'database name') dropdatabase [database name] 2
SQLServer checks whether the object exists and does not exist. 1 checks whether the database has SQL code if exists (select * from sys. databases where name = 'database name') drop database [database name] if exists (select * from sys. databases where name = 'database name') drop database [database name] 2
SQL Server determines whether an object exists <无>
1. Determine whether the database has the SQL code if exists (select * from sys. databases where name = 'database name') drop database [database name] if exists (select * from sys. databases where name = 'database name ') drop database [database name] 2 check whether the table has SQL code if exists (select * from sysobjects where id = object_id (n' [Table name] ') and OBJECTPROPERTY (id, N 'isusertable') = 1) drop table [table name] if exists (select * from sysobjects where id = object_id (n' [table name] ') and OBJECTPROPERTY (id, N 'isusertable') = 1) drop table [table name] 3 check whether the stored procedure has the SQL code if exists (select * from sysobjects where id = object_id (n' [stored procedure name] ') and OBJECTPROPERTY (id, N 'isprocedure ') = 1) drop procedure [stored procedure name] 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 whether the temporary table has the SQL code if object_id ('tempdb .. # temporary table name ') is not null drop table # temporary table name if object_id ('tempdb .. # temporary table name ') is not null drop table # temporary table name 5 judge 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] '-- SQL Server 2000IF EXISTS (SELECT * FROM sysviews WHERE object_id =' [dbo]. [view name] '-- SQL Server 2005IF EXISTS (SELECT * FROM sys. views WHERE object_id = '[dbo]. [view name] '6 determine whether the function has SQL code -- determine whether 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] -- determines whether the function name 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 obtain information about the object created by the user SQL code SELECT [name], [id], crdate FROM sysobjects where xtype = 'U'/* xtype indicates the parameter type, the following C = CHECK constraints are usually included: D = DEFAULT value or DEFAULT constraint F = foreign key constraint L = Log FN = scalar function IF = embedded table function P = Stored Procedure PK = primary key constraint (K type) RF = copy and filter Stored Procedure S = system table TF = table function TR = trigger U = User table UQ = UNIQUE constraint (type: K) V = view X = Extended Stored Procedure */SELECT [name], [id], crdate FROM sysobjects where xtype = 'U'/* xtype indicates the parameter type, the following C = CHECK constraints are usually included: D = DEFAULT value or DEFAULT constraint F = foreign key constraint L = Log FN = scalar function IF = embedded table function P = Stored Procedure PK = primary key constraint (K type) RF = copy and filter Stored Procedure S = system table TF = table function TR = trigger U = User table UQ = UNIQUE constraint (type: K) V = view X = Extended Stored Procedure */8 determine whether the column contains the SQL code if exists (select * from syscolumns where id = object_id ('table name ') and name = 'column name') alter table name drop column name if exists (select * from syscolumns where id = object_id ('table name') and name = 'column name ') alter table Name drop column name 9 determine whether the column is auto-incrementing SQL code if columnproperty (object_id ('table'), 'col', 'isidentity ') = 1 print 'auto-incrementing column 'else' is not an auto-incrementing column 'select * FROM sys. columns WHERE object_id = OBJECT_ID ('table name') AND is_identity = 1 if columnproperty (object_id ('table'), 'col', 'isidentity ') = 1 print 'auto-incrementing column 'else' is not an auto-incrementing column 'select * FROM sys. columns WHERE object_id = OBJECT_ID ('table name') AND is_identity = 110 determine whether the table has an index SQL code if exists (select * from sysindexes where id = object_id ('table name ') and name = 'index name') print 'else' does not exist if exists (select * from sysindexes where id = object_id ('table name ') and name = 'index name') print 'else print' does not exist 11 view the database object SQL code SELECT * FROM sys. sysobjects WHERE name = 'object name' SELECT * FROM sys. sysobjects WHERE name = 'object name'