can't alter function

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?

BarryBarry wrote:

Quote:

Originally Posted by

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|||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_columns where column
exists

Thanks
Barry

Razvan Socol wrote:

Quote:

Originally Posted by

Barry wrote:

Quote:

Originally Posted by

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

|||How does one reference a UDF in a table?

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

jim_geiss...@.countrywide.com wrote:

Quote:

Originally Posted by

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

|||Barry (bgt0990@.optonline.net) writes:

Quote:

Originally Posted by

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_columns where column
exists

ALTER TABLE tbl DROP CONSTRAINT <nameofconstraint>

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@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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(OBJECT_NAME(parent_obj))
+' DROP CONSTRAINT '+QUOTENAME(name)
FROM sysobjects WHERE xtype='D' AND id IN (
SELECT id FROM sysdepends
WHERE depid=OBJECT_ID('YourFunction')
)

SELECT 'ALTER TABLE '+QUOTENAME(OBJECT_NAME(parent_obj))
+' ADD CONSTRAINT '+QUOTENAME(o.name)
+' DEFAULT '+x.text
+' FOR '+QUOTENAME(c.name)
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