How to check if a function exists in a SQL database?

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'.') 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:

Video Demonstration

Read also: Check if a column exists