can't alter function

[Barry]

In sqlserver 2000 I have a UDF which works fine but I want to make a
change to it. When I do an ALTER FUNCTION ... I get an error saying
that I can't alter the function because it is referenced by an object.
Is there any way around this? I reference the UDF in over 100 tables,
do I have to go to each table, remove the all references alter the
function then edit each 100 tables again? How clumsy can it be?

Barry

[Razvan Socol]

Re: can't alter function

Barry wrote:

In sqlserver 2000 I have a UDF which works fine but I want to make a
change to it. When I do an ALTER FUNCTION ... I get an error saying
that I can't alter the function because it is referenced by an object.
Is there any way around this? I reference the UDF in over 100 tables,
do I have to go to each table, remove the all references alter the
function then edit each 100 tables again? How clumsy can it be?

Yep, that's it. You can generate a script for the drop/recreate of the
objects that reference the function, using the information from the
system tables. What kind of objects are we talking about
(defaults/check constraints/computed columns) ?

Razvan

[Barry]

Re: can't alter function

Defaults.

What would a script to look like to do this?

In Oracle I would ALTER TABLE XXX
Modify ( Column default null)

then write a script to fill in XXX from owner_tab_colum ns where column
exists

Thanks
Barry


Razvan Socol wrote:

Barry wrote:

In sqlserver 2000 I have a UDF which works fine but I want to make a
change to it. When I do an ALTER FUNCTION ... I get an error saying
that I can't alter the function because it is referenced by an object.
Is there any way around this? I reference the UDF in over 100 tables,
do I have to go to each table, remove the all references alter the
function then edit each 100 tables again? How clumsy can it be?

>
Yep, that's it. You can generate a script for the drop/recreate of the
objects that reference the function, using the information from the
system tables. What kind of objects are we talking about
(defaults/check constraints/computed columns) ?
>
Razvan

[jim_geissman@countrywide.com]

Re: can't alter function

How does one reference a UDF in a table?

Jim

[Barry]

Re: can't alter function

I'm assigning it as a default The return from the function is my
default. I use it for User_id's


jim_geiss...@co untrywide.com wrote:

How does one reference a UDF in a table?
>
Jim

[Erland Sommarskog]

Re: can't alter function

Barry (bgt0990@optonl ine.net) writes:

What would a script to look like to do this?
>
In Oracle I would ALTER TABLE XXX
Modify ( Column default null)
>
then write a script to fill in XXX from owner_tab_colum ns where column
exists

ALTER TABLE tbl DROP CONSTRAINT <nameofconstrai nt>

Here is a query that will generate all necessary DROP commands:

SELECT 'ALTER TABLE ' + o.name + ' ALTER COLUMN ' + c.name +
' DROP CONSTRAINT ' + oc.name
FROM sysdepends d
JOIN sysobjects ofn ON d.depid = ofn.id
JOIN sysobjects oc ON d.id = oc.id
JOIN sysobjects o ON o.id = oc.parent_obj
JOIN syscolumns c ON o.id = c.id
AND c.cdefault = oc.id
WHERE ofn.name = '<yourfunction> '

You can also modify it to regenerate the command to restore the default.
Run that modified query, before you execute the result of the above. :-)



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Razvan Socol]

Re: can't alter function

Hi, Barry

Here is how you can generate a script to drop/recreate the defaults
that depend on a given function:

SELECT 'ALTER TABLE '+QUOTENAME(OBJ ECT_NAME(parent _obj))
+' DROP CONSTRAINT '+QUOTENAME(nam e)
FROM sysobjects WHERE xtype='D' AND id IN (
SELECT id FROM sysdepends
WHERE depid=OBJECT_ID ('YourFunction' )
)

SELECT 'ALTER TABLE '+QUOTENAME(OBJ ECT_NAME(parent _obj))
+' ADD CONSTRAINT '+QUOTENAME(o.n ame)
+' DEFAULT '+x.text
+' FOR '+QUOTENAME(c.n ame)
FROM sysobjects o INNER JOIN syscomments x ON o.id=x.id
INNER JOIN syscolumns c ON c.cdefault=o.id
WHERE o.xtype='D' AND o.id IN (
SELECT id FROM sysdepends
WHERE depid=OBJECT_ID ('YourFunction' )
)

There is a limitation regarding the size of the definition of the
default (x.text in the above query): if it's more than 4000 characters,
the above query won't work (because there would be multiple rows in
syscomments for the same id); but I'm sure nobody would create a
default with a definition longer than 100 characters to invoke a UDF,
so that should not be a problem.

Razvan