how to recompile / refresh UDFs ?

[Ben]

Hi!
I need to refresh an entire database.

I can recompile SPs with sp_recompile (or DBCC FLUSHPROCINDB), and
refresh views with sp_refreshView, but I cannot find any way to
refresh my user-defined functions (some of them are like views, with
parameters).

Any help appreciated :) !

Ben

[Marcin A. Guzowski]

Hi Ben,

Hi!
I need to refresh an entire database.

I can recompile SPs with sp_recompile (or DBCC FLUSHPROCINDB), and
refresh views with sp_refreshView, but I cannot find any way to
refresh my user-defined functions (some of them are like views, with
parameters).

I'm afraid that no such procedure/DBCC command exists to recompile a
function. IMHO the best way to refresh function meta-data is to ALTER
it. That's better solution than dropping and creating (recreating) a
function, because when using ALTER FUNCTION permissions are retained.
--
Best regards,
Marcin Guzowski
http://guzowski.info

[Erland Sommarskog]

Ben (be****@gmail.com) writes:

I can recompile SPs with sp_recompile (or DBCC FLUSHPROCINDB), and
refresh views with sp_refreshView, but I cannot find any way to
refresh my user-defined functions (some of them are like views, with
parameters).

What do you really want to achieve? sp_recompile and FLUSHPROCINDB just
removes plans out the query cache. sp_refreshview on the other hand
reinterprets the definition of the view, and this is necessary if
the view definition has an * in the select list. Thus the two serve
completely different purposes.

IF the problem is that you cannot refresh your inline table functions,
the simple solution is not to use SELECT *, which is generally considered
bad practice.
--
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

[Ben]

What I'm trying to achieve, as I said:

I need to refresh an entire database.

So, all SPs, functions, and views.

The refresh problems arose when I changed a few columns' order : I
still receive all the data from the functions but they're incorrectly
ordered and labelled.
It gets even worse as some functions are nested (say, I select all
valid clients according to dates criteria, then all valid orders from
those clients, etc), or other functions are used in CHECK constraints
and so I SQL Server refuses to alter them, so I have to kil the
constraint, alter the function, and re-create the constraint... nice.

I heard about the "select * is bad practice", but I'm dealing with a
constantly evolving database (not yet in production), so I use a lot
of it to just pump everything and send it back to webpages. And even
if I didn't all that would mean is I'd have to manually go into every
function and update them, which is exactly what I've been doing so far
(open, backspace to alter, save --seems to be the only way to
refresh).

None of this is unsolvable, it just takes unnecessary time (one change
can mean 20 functions to track), and I can't find a way to do it
automatically.
Plus I find it really frustrating to be faced with compile problems
using a language that is supposed to be interpreted!
I get enough trouble updating DLLs, plus at least VS provides the
"recompile all" function...

Maybe if I do enough nagging my boss'll get me a SQL Server 2005.
Would that solve at least some of this?

Cheers, Ben.
On Mar 6, 11:50 pm, Erland Sommarskog <[e-mail]:

Ben ([e-mail]) writes:

I can recompile SPs with sp_recompile (or DBCC FLUSHPROCINDB), and
refresh views with sp_refreshView, but I cannot find any way to
refresh my user-defined functions (some of them are like views, with
parameters).

What do you really want to achieve? sp_recompile and FLUSHPROCINDB just
removes plans out the query cache. sp_refreshview on the other hand
reinterprets the definition of the view, and this is necessary if
the view definition has an * in the select list. Thus the two serve
completely different purposes.

IF the problem is that you cannot refresh your inline table functions,
the simple solution is not to use SELECT *, which is generally considered
bad practice.

--
Erland Sommarskog, SQL Server MVP, [e-mail]

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Ben]

Thanks, I knew about the ALTER solution, the only problem is I can
only do it manually (in the manager, open each function, backspace to
alter and enable the save button, and save).
Do you know any way to do the same automatically? (refresh ALL
functions, or the ENTIRE database) (PS: I can't rebuild it, have to
keep the data)

Cheers, Ben
On Mar 6, 6:17 pm, "Marcin A. Guzowski"
<[e-mail]:

Hi Ben,

Hi!
I need to refresh an entire database.

I can recompile SPs with sp_recompile (or DBCC FLUSHPROCINDB), and
refresh views with sp_refreshView, but I cannot find any way to
refresh my user-defined functions (some of them are like views, with
parameters).

I'm afraid that no such procedure/DBCC command exists to recompile a
function. IMHO the best way to refresh function meta-data is to ALTER
it. That's better solution than dropping and creating (recreating) a
function, because when using ALTER FUNCTION permissions are retained.

--
Best regards,
Marcin Guzowskihttp://guzowski.info

[Erland Sommarskog]

Ben (be****@gmail.com) writes:

I heard about the "select * is bad practice", but I'm dealing with a
constantly evolving database (not yet in production), so I use a lot
of it to just pump everything and send it back to webpages. And even
if I didn't all that would mean is I'd have to manually go into every
function and update them, which is exactly what I've been doing so far
(open, backspace to alter, save --seems to be the only way to
refresh).

Not really. If you have everything under version control, or at least
on disk, you can easily run a BAT file that loads all functions it can
find. The database is no place for source code; in my opinion that is
only a container for binaries.

And while it may seem easy to have SELECT *, it does come back and bite
you. As I understood, you got this problem because you changed the column
order. If you had used explicit column lists, you could just have
changed the column lists, and you would have to change the underlying
tables.

I work with a constantly evolving database, for over ten years now. One
thing I hate is to find a stored procedure to return about every column
in a table. Then I have to dig further into the client code so see if
the column I want to drop or redefine is actually use somewhere. So there
are very good reasons to only return the columns that actually are
in use. This makes it much easier to track down where things are used.

--
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