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'[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:

Video Demonstration

Read also: Check if a column exists