Computed column UDF - Drop Alt Recr instead of Alter
Lxocram
Posts: 25 Bronze 3
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
so->
*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)
TIP:
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
so->
*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)
TIP:
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
