Drop Sql Function with a variable as the function name

Question

I want to drop all the functions in a schema, and I don't want to write something that requires me to get all the function names ahead of time and write it in the SQL

DROP FUNCTION MySchema.FunctionName1
DROP FUNCTION MySchema.FunctionName2
... etc

I tried:

DECLARE @FuncName varchar(100)

WHILE (SELECT Count(*) From information_schema.routines 
         WHERE SPECIFIC_SCHEMA = 'MySchema' AND ROUTINE_TYPE = 'function') > 0
BEGIN
    SELECT top(1) @FuncName = ROUTINE_NAME FROM information_schema.routines
       WHERE SPECIFIC_SCHEMA = 'MySchema' AND ROUTINE_TYPE = 'function'

    BEGIN
        DROP FUNCTION @FuncName  -- it doesn't like it because it is a string variable
    END     

END

sql script to drop old versions of stored procedures and functions is kinda similar but just generates SQL like the first example and doesn't actually run it

The technique you are looking for is called dynamic SQL. Be extra careful to ensure your dynamic code is secure. This technique can open SQL injection vulnerabilities. — David Rushton
– David Rushton, Commented Aug 1, 2016 at 11:32
Unless a function has been put onto the DB (either by an authorized or unauthorized person), and it was called "MySchema.FunctionName GO DROP TABLE users" or something equally ridiculous I don't see what that comment is trying to tell me — ono2012
– ono2012, Commented Aug 1, 2016 at 11:54

Jatin Patel · Accepted Answer · 2016-08-01 13:20:00Z

3

try this dynamic sql,

DECLARE @FuncName nvarchar(500)

WHILE (SELECT Count(*) From information_schema.routines 
         WHERE SPECIFIC_SCHEMA = 'MySchema' AND ROUTINE_TYPE = 'function') > 0
BEGIN
    SELECT top(1) @FuncName = 'DROP FUNCTION ' + SPECIFIC_SCHEMA + '.' + ROUTINE_NAME FROM information_schema.routines
       WHERE SPECIFIC_SCHEMA = 'MySchema' AND ROUTINE_TYPE = 'function'

    BEGIN
        EXEC (@FuncName)  -- it doesn't like it because it is a string variable
    END     

END

Also you can use below to drop all functions at one go,

DECLARE @FuncName nvarchar(MAX) = ''

SELECT @FuncName = @FuncName + 'DROP FUNCTION ' + SPECIFIC_SCHEMA + '.' + ROUTINE_NAME + CHAR(10) 
FROM information_schema.routines
WHERE SPECIFIC_SCHEMA = 'MySchema' AND ROUTINE_TYPE = 'function'

EXEC sp_executesql @FuncName

edited Aug 1, 2016 at 13:20

answered Aug 1, 2016 at 11:19

Jatin Patel

2,11413 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Rushton Over a year ago

You could also write this without the loop. Just ensure your drop statements are GO delimited and the dynamic query variable is large enough.

ono2012 · Accepted Answer · 2016-08-16 09:04:57Z

0

Based off Jatin Patel's answer I needed to know the dynamic sql bit! (This would have been a comment but stackoverflow couldn't cope with the @ symbols) I used the following version for readability and the @FuncName keeps it's original meaning

DECLARE @FuncName varchar(100)

WHILE (SELECT COUNT(*) 
       FROM information_schema.routines 
       WHERE SPECIFIC_SCHEMA = 'MySchema' AND ROUTINE_TYPE = 'function' ) > 0
BEGIN
    SELECT TOP(1) @FuncName = ROUTINE_NAME
    FROM information_schema.routines
    WHERE SPECIFIC_SCHEMA = 'MySchema' AND ROUTINE_TYPE = 'function'

    BEGIN
        EXEC ('DROP FUNCTION MySchema.' + @FuncName)
    END     

END

edited Aug 16, 2016 at 9:04

answered Aug 1, 2016 at 11:47

ono2012

5,3072 gold badges35 silver badges43 bronze badges

Collectives™ on Stack Overflow

Drop Sql Function with a variable as the function name

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related