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:

check if ms sql function already exists
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

Leave a Reply

Your email address will not be published.