Here is a segment of code you should add to any T-SQL that is dropping a function or stored procedure to make sure you are doing so safely:
This T-SQL below is if you prefer using the sys.objects approach:
|
1 2 3 4 5 6 7 |
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[foo]') AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' )) DROP FUNCTION [dbo].[foo] GO |
…or you can use the INFORMATION_SCHEMA approach:
Functions
|
1 2 3 4 5 |
IF EXISTS ( SELECT 1 FROM Information_schema.Routines WHERE Specific_schema = 'dbo' AND specific_name = 'Foo' AND Routine_Type = 'FUNCTION' ) |
and simply change the “Routine_Type” to check for stored procedures:
Stored Procedures
|
1 2 3 4 5 |
IF EXISTS ( SELECT 1 FROM Information_schema.Routines WHERE Specific_schema = 'dbo' AND specific_name = 'Foo' AND Routine_Type = 'PROCEDURE' ) |
Here is how you check for the existence of a database in SQL Server:
|
1 2 3 4 |
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'SQLServerPlanet') -- add some SQL here |
Here is another way to use db_id to see if it is null.
|
1 2 3 4 5 6 7 8 |
IF db_id('SQLServerPlanet') IS NOT NULL BEGIN SELECT 'database does exist' END ELSE BEGIN SELECT 'database does not exist' END |
Views – 1803