Make sure you have proper permission to create function in the master database.
More over, you should be system administrator or member of sysadmin and serveradmin fixed server roles to execute the sp_configure procedure to create system objects.
Once you create this functions in master database, then you can use this functions in any database of the server.
fn_tables function, will list the Table Name,User Name, Date Created, Type [System/User]
from the database.
DaysOfMonth user definded function is available in Pubs database. if you want to use this function across databases, make it as a built in function, as fn_daysofmonth.
Make sure your built-in function name should be lower cases and start with "fn_" .
------------------------------------------------------------use master
go
exec sp_configure 'allow', 1
reconfigure with override
go
-------------------------------------------------------------- function
drop function system_function_schema.fn_tables
go
create function system_function_schema.fn_tables()
returns table
as
return(
select top 100 percent name as TableName,user_name(uid) as Owner,crdate as DateCreated,
case
when 1 in ( OBJECTPROPERTY(id, N'IsMSShipped'),OBJECTPROPERTY(id, N'IsSystemTable') ) then "System"
else "User" end as Type
from dbo.sysobjects
where OBJECTPROPERTY(id, N'IsTable') = 1
order by Type desc,TableName)
go
grant select on system_function_schema.fn_tables to public
go
select * from ::fn_tables()
go
-- no of days in a month - function
The following function named DaysOfMonth is available in Pubs database. if you want to use this function across databases, make it as a built in function, as follows :
-- available in Pubs database
drop function system_function_schema.fn_daysofmonth
go
create function system_function_schema.fn_daysofmonth(
)
RETURNS INT
AS
BEGIN
BEGIN
--Just return NULL if input string is NULL
RETURN NULL
END
END
go
grant exec on system_function_schema.fn_daysofmonth to public
go
select fn_daysofmonth(getdate())
go
------------------------------------------------------------exec sp_configure 'allow', 0
reconfigure with override
go
------------------------------------------------------------execute and see the results
------------------------------------------------------------select * from ::fn_tables()
select fn_daysofmonth(getdate())
-----------------------------------------------
Naras
Narasimhan Jayachandran
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
I'm not sure why having to use the two-part function name is a problem, but
if you want a workaround, here's one, made possible by the fact that
table-valued functions don't have to be qualified by the owner. I expect
your performance will suffer, however! I'm not really recommending it - just
observing that it can be done...
Instead of
CREATE FUNCTION myF (
) RETURNS varchar(10) AS BEGIN
END
GO
select dbo.myF(5)
you can get the same behavior from this:
CREATE FUNCTION Cheap (
)
return
end
select (select * from Cheap(5))
Steve Kass
Drew University
CREATE FUNCTION Cheap (
)
RETURNS TABLE
AS
But the problem will be that you cannot do:
whereas with the normal UDFs, you can do:
You can only pass constants as parameters to table-valued functions. So
the usability is limited & you can't use the UDF in complex expressions.
You can of course call a UDF like a SP:
In this case, you don't need the owner prefix but you can only pass
constants again.
--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )
--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )
To be more serious, I might have seen an explanation once, but I'm not
sure. I guess it is a pragmatical solution to distinguish built-in
functions like objectproperty() or datalength() from user-defined
functions.
There is none. But what exactly do you want to achieve? While objects canQuote:> Does anyone a workaround so not to enter the owner.
--
Erland Sommarskog, Abaris AB
SQL Server MVP
1. call a user defined function from another user-defined function
hi, there
can i call a user-defined function from another user-defined function. when
i tried to do so, sql server showed error message saying that "function_name
is not a recoginzed function".
please help.
--
Wang
3. string processing in user-defined scalar function
4. VB / ACCESS 2.0 ODBC problem.
5. Scalar User Defined Functions
7. scalar user-defined function without input params???
8. Manually creating Oracle 9i database
9. Automatically create scalar user-defined functions
10. Scalar Inline User Defined Function - slow response
11. User-defined scalar functions?
12. required for user defined functions?
13. recovering user defined function which is now a system function