Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to write a simple system function so that the dbo. prefix is NOT required to access the function. I have found an example of this for SQL Server 2000 but when I run

EXEC SP_CONFIGURE 'ALLOW UPDATES', 1
RECONFIGURE WITH OVERRIDE
go

create function system_function_schema.trim
    (@input varchar(max))
    returns  varchar(max)
as
begin
    declare @result varchar(max)

    select  @result = rtrim(ltrim(@input))

    return  @result
end
go

EXEC SP_CONFIGURE 'ALLOW UPDATES', 0
RECONFIGURE WITH OVERRIDE

This is the error thrown.

The specified schema name "system_function_schema" either does not exist or you do not have permission to use it. Severity 16

The expected usage would be

select trim('   padded value   ')

Does anyone know the approach I should be taking or if I HAVE to create it as a regular user defined function that must be called with the dbo. like :

select dbo.trim('   padded value   ')
share|improve this question
    
Note that system procedures are not just for "don't need to type the schema". System stored procedures run in the scope of the current database. That is, executing exec sp_help; returns a list of objects in the current database; executing exec master.dbo.sp_help; returns a list of objects in the master database. –  Greenstone Walker May 6 '13 at 0:52
    
Why? Is typing dbo. that hard? –  Aaron Bertrand May 20 '13 at 2:44
add comment

2 Answers

up vote 3 down vote accepted

Using system_function_schema was an undocumented and unsupported process in SQL 2000. As of SQL 2005, it is no longer possible.

By design, UDFs require that the schema be included in the function call.

There is one exception: calling the function like a procedure doesn't actually require the schema, but also wouldn't be terribly useful for your function.

exec trim @input = ' foo '

Additionally, be sure to carefully test queries with scalar functions--they can have significant impact on performance.

share|improve this answer
add comment

For functions, its tricky. You can probably create a function and then create a synonym for it in Model (if you want the function for any new databases that are created as they will inherit Model objects).

Create a function in master database :

Note: You have to first create system_function_schema -- schema.

use master 
go
create function system_function_schema.trim
    (@input varchar(max))
    returns  varchar(max)
as
begin
    declare @result varchar(max)

    select  @result = rtrim(ltrim(@input))

    return  @result
end
go

Then create a SYNONYM referencing the function (for existing databases, you have to create one in the existing db's :

USE model;
GO
CREATE SYNONYM dbo.trim FOR master.system_function_schema.trim;

Now you can reference the object with a two-part name in any database, while only having to store one copy of the code.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.