If you need to know if a function exists in the database(MS SQL) so that I can drop it and re-create it, you can use one of the following options:
1. Check using the sys.objects table
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MySuperFunction]') AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' )) SELECT 'Function Exists 1' GO
2. Check using the Information_schema.Routines table
IF EXISTS ( SELECT 1 FROM Information_schema.Routines WHERE Specific_schema = 'dbo' AND specific_name = 'MySuperFunction' AND Routine_Type = 'FUNCTION' ) SELECT 'Function Exists 2'
3. Check using the object_id function
IF object_id('MySuperFunction', 'FN') IS NOT NULL SELECT 'Function Exists 3'
The second parameter could be:
- FN: scalar function
- IF: table-valued inline function
- TF: table-valued input function
- FS: assembly (CLR) scalar function
- FT: assembly (CLR) table-valued user
4. Check using the OBJECTPROPERTY and object_id
IF OBJECTPROPERTY (object_id('dbo.MySuperFunction'), 'IsScalarFunction') = 1 SELECT 'Function Exists 4'
Example – Check Function Exists
Here is an example of how all three ways work:
click to open in a new tab
Tip
If you need to check in order to drop a function you can use the next SQL statement:
DROP FUNCTION IF EXISTS [dbo].[MySuperFunction]
Video Demonstration
Read also: Check if a column exists