• Simple answer:

    There is no "more elegant way". You can script executing of sp_refreshsqlmodule for each UDF in your database and run this script after altering any view/table (please note that sp_refreshsqlmodule does not work for schemabound functions...).

    BTW. It is generaly a bad practicve to do SELECT *...

    You can add SCHEMABINDING option in your function. It will stop anyone altering objects referred in function without dropping function first. I know it is not what you really want, but at least it will insure your functions integrity...

    Just a sample of script to refresh all non-schemabound functions:

    DECLARE @SQL NVARCHAR(1000)

    DECLARE myFoos CURSOR FAST_FORWARD

    FOR

    SELECT 'EXEC sys.sp_refreshsqlmodule ''' + ROUTINE_SCHEMA + '.' + ROUTINE_NAME + ''''

    FROM INFORMATION_SCHEMA.ROUTINES

    WHERE ROUTINE_TYPE = 'FUNCTION' AND ROUTINE_DEFINITION NOT LIKE '%SCHEMABINDING%'

    OPEN myFoos

    FETCH NEXT FROM myFoos INTO @SQL

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT @SQL

    EXEC sp_executesql @SQL

    FETCH NEXT FROM myFoos INTO @SQL

    END

    CLOSE myFoos

    DEALLOCATE myFoos

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]