Computed column UDF - Drop Alt Recr instead of Alter

LxocramLxocram Posts: 25 Bronze 3
edited March 1, 2012 9:26AM in SQL Compare Previous Versions
When a table has a computed column that references a UDF and the definition of the UDF changes the computed column should be dropped and re-added
*select computed columns that reference the UDF
*drop these columns
*alter the function
*readd computed columns

Otherwise the synchronization script throws errors like

cannot ALTER 'dbo.get_Username_Personeelslid_COMPUTED_COLUMN' because it is being referenced by object 'sec_Dossiergebruikers'.

This of course should be an option (drop and recreate computed columns) just like the assembly option (do not use alter assembly)

I tend to use 2 functions
*'dbo.get_Username_Personeelslid_COMPUTED_COLUMN' is the one being referenced by computed columns
It calls upon a second function

'dbo.get_Username_Personeelslid' with the same parameters

That way you can work around the "referenced by" safety and update your function definition
Sign In or Register to comment.