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]

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]

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:

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]

How does one reference a UDF in a table?

Jim

[Barry]

I'm assigning it as a default The return from the function is my
default. I use it for User_id's
[e-mail] wrote:

How does one reference a UDF in a table?

Jim

[Erland Sommarskog]

Barry (bg*****@optonline.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_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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Razvan Socol]

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