CODEWITHSUNDEEP
sql-serverfunction
ono2012
ono2012

Reputation: 5257

Drop Sql Function with a variable as the function name

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

Upvotes: 0

Views: 444

Answers (2)

ono2012
ono2012

Reputation: 5257

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

Upvotes: 0

Jatin Patel
Jatin Patel

Reputation: 2104

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

Upvotes: 3

Related Questions