SQL to determine whether a stored procedure exists

SQL checks whether the stored procedure exists. 1. Determines whether the SQL code ifexists (* fromsys. databaseswherename & rsquo; Database Name & rsquo;) & nbsp; dropdatabase [database name] & nbsp; ifexists (select * fromsys. databaseswherename & SQL determines whether a stored procedure exists

1. Determine whether the service exists.
SQL code
If exists (* 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 exists.
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. Determine whether a stored procedure exists
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 a temporary table exists
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. Determine whether a view exists
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 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. Determine whether a function exists.
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 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. 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, which usually includes the following
C = CHECK Constraints
D = DEFAULT value or DEFAULT Constraint
F = foreign key constraint
L = Log
FN = scalar function
IF = embedded table functions
P = Stored Procedure
PK = primary key constraint (type: K)
RF = copy and filter the Stored Procedure
S = system table
TF = table functions
TR =
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended Stored Procedure
*/SELECT [name], [id], crdate FROM sysobjects where xtype = 'U'
/*
Xtype indicates the parameter type, which usually includes the following
C = CHECK Constraints
D = DEFAULT value or DEFAULT Constraint
F = foreign key constraint
L = Log
FN = scalar function
IF = embedded table functions
P = Stored Procedure
PK = primary key constraint (type: K)
RF = copy and filter the Stored Procedure
S = system table
TF = table functions
TR = trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended Stored Procedure
*/
8. Determine whether a Column exists
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 an auto-incrementing column.
SQL code
If columnproperty (object_id ('table'), 'col', 'isidentity ') = 1
Print 'auto-incrementing column'
Else
Print 'not 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
Print 'not auto-incrementing column'
SELECT * FROM sys. columns WHERE object_id = OBJECT_ID ('table name ')
AND is_identity = 1
10. Check whether an index exists in the table.
SQL code
If exists (select * from sysindexes where id = object_id ('table name') and name = 'index name ')
Print 'exist'
Else
Print 'if exists (select * from sysindexes where id = object_id ('table name') and name = 'index name') does not exist ')
Print 'exist'
Else
Print 'does not exist
11. view objects in the database
SQL code
SELECT * FROM sys. sysobjects WHERE name = 'object name' SELECT * FROM sys. sysobjects WHERE name = 'object name'