2

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   ')
2
  • 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. May 6, 2013 at 0:52
  • Why? Is typing dbo. that hard? May 20, 2013 at 2:44

2 Answers 2

3

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.

1

For functions it's 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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