0

I'm trying to write a script that can be run regardless of whether a user-defined function already exists. Per this link, I have the following:

IF OBJECT_ID('dbo.fn_myFunc') IS NOT NULL
    DROP FUNCTION dbo.fn_myFunc;
go

CREATE FUNCTION fn_myFunc (
    ...
) RETURNS BIT AS
BEGIN
    ...
END
go

The issue is, later on, I have 

ALTER TABLE my_table
ADD CONSTRAINT my_table_chk_myFunc CHECK (dbo.fn_myFunc(...) = 1)
go

So when the DROP FUNCTION happens, I get

Cannot DROP FUNCTION 'dbo.fn_myFunc' because it is being referenced by object 'my_table_chk_myFunc '.

I tried, instead of using ...IS NOT NULL DROP... CREATE FUNCTION using ...IS NULL CREATE... ALTER FUNCTION, but either I got the syntax wrong, or it doesn't work.

How do I best get around this?

Community
  • 1
  • 1
dx_over_dt
  • 13,240
  • 17
  • 54
  • 102
  • The function is used in that **CHECK** constraint - therefore, you cannot drop it. You need to **first** drop the `CHECK` constraint - **then** drop your function! – marc_s Oct 22 '14 at 19:44
  • Yes, I know. But I'd like either a dynamic way to find all references to the function so I can drop them and recreate them later, or a method to detect if it exists already and just use an alter. – dx_over_dt Oct 22 '14 at 19:46
  • Have a look at [`sys.dependencies`](http://technet.microsoft.com/en-us/library/ms174402%28v=sql.110%29.aspx) - that might be what you're looking for – marc_s Oct 22 '14 at 19:48
  • 1
    Wrap the `ALTER` and `CREATE` definitions in an `EXEC()` statement. [Like so](http://stackoverflow.com/questions/55506/how-do-i-conditionally-create-a-stored-procedure-in-sql-server). – Bacon Bits Oct 22 '14 at 19:49
  • 1
    check this link http://stackoverflow.com/a/20857526/1411000 – Hiten004 Oct 22 '14 at 21:42

0 Answers0