How to make a function a system function.

Question

Post reply

How to make a function a system function.

ben.brugman

SSChampion

Points: 13369
More actions
February 6, 2014 at 4:18 am

#287986

How do I make a function a system function, for example the function:
DelimitedSplit8K
How do I put this function into the master database and use it from any other database.
I tried to use
sp_ms_marksystemobject
But I am doing something wrong, or this is not the correct method.
Any advise,
Ben

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

SQLCJ SSCommitted Points: 1791 More actions · Answer 1

Just a thought ...

why not use FQN (fully qualified name) to access function created in different database ?

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

As far as I am aware, it is impossible to create new system functions in SQL Server (unlike system procs).

Closest think you might want to do is to create function in master database and synonym to it in a model database, so all newly created databases will have it created to and refered to the single function in master. However, existing databases will have to be updated separately - you will need to create synonyms in all of them.

_____________________________________________
"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]

ben.brugman SSChampion Points: 13369 More actions · Answer 3

chetan.deshpande001 (2/6/2014)
Just a thought ...
why not use FQN (fully qualified name) to access function created in different database ?

On a Single instance this would be a reasonable solution. But deployment to other systems would be cumbersome. On the other systems this would require a database with a standard name. This is not reasonable to ask from the (internal) customers.

Using sp_ms_marksystemobject, you can easely add stored procedures and use them. Although they are still on the system in te master database they are hardly noticeble and are easier to use. I would like the same deployment for 'general' functions.

EDIT:

Only thought of this (now).

Use the fully qualified name and place the stored procedure in the master database. This solves most of the issues I had.

Thanks for the Idea.

End EDIT.

Thanks for your tip.

Ben

ben.brugman SSChampion Points: 13369 More actions · Answer 4

Eugene Elutin (2/6/2014)
As far as I am aware, it is impossible to create new system functions in SQL Server (unlike system procs).
Closest think you might want to do is to create function in master database and synonym to it in a model database, so all newly created databases will have it created to and refered to the single function in master. However, existing databases will have to be updated separately - you will need to create synonyms in all of them.

Thanks for the reply,

Allready a 'workaround' was suggested (for existing installations), so I'll probably have to go with that. Create an extra database and use fully qualified names. Or wrap everything in a stored procedure and place that in a master database.

Thanks, this anwser is a timesaver, I can stop searching for something which does not exist.

Ben

Lowell SSC Guru Points: 323518 More actions · Answer 5

i actually make things system functions , system procedures, and stuff quite a bit i have a ton of developer tools i've created and adapted that go in master.

they all go in the dbo schema, not the sys schema, but i can use them in any database.

here's a simple example:

in any given database, i might want to find a table name or column that contains a given string; lets say AuditId for example

by markign a procedure as a system object, the procedure uses the relative database context it is called from 's views like sys.objects , sys.columns, etc, and not the db that contains the actual proc.

following the rules, i create a proc that starts with sp_ and put it in the master database, then a mark it as a system object.

you cannot "unmark" an object once marked; youhave to drop it and recreate it so it's not a system object any more.

exec sp_find AuditId

and my example procedure:

IF OBJECT_ID('[dbo].[sp_find]') IS NOT NULL

DROP PROCEDURE [dbo].[sp_find]

GO

--#################################################################################################

--developer utility function added by Lowell, used in SQL Server Management Studio

--Purpose: find table/view columns containing search phrase

--#################################################################################################

CREATE procedure [dbo].[sp_find]

@findcolumn varchar(50)

AS

BEGIN

SET NOCOUNT ON

--print object_name(@@PROCID)

SELECT

TableFound,

ColumnFound,

ObjectType

FROM

(

SELECT

1 AS SortOrder,

objz.name AS TableFound,

'' AS ColumnFound,

objz.type_desc As ObjectType

FROM sys.objects objz

WHERE objz.name LIKE '%' + @findcolumn + '%'

AND objz.type_desc IN('SYSTEM_TABLE',

'VIEW',

'SQL_TABLE_VALUED_FUNCTION',

'SQL_STORED_PROCEDURE',

'SQL_INLINE_TABLE_VALUED_FUNCTION',

'USER_TABLE',

'SQL_SCALAR_FUNCTION')

UNION ALL

SELECT

2 AS SortOrder,

objz.name AS TableFound,

colz.name AS ColumnFound,

objz.type_desc As ObjectType

FROM sys.objects objz

INNER JOIN sys.columns colz

ON objz.object_id=colz.object_id

WHERE colz.name like '%' + @findcolumn + '%'

AND objz.type_desc IN('SYSTEM_TABLE',

'VIEW',

'USER_TABLE')

) X

ORDER BY

SortOrder,

TableFound,

ColumnFound

END --PROC

GO

--#################################################################################################

--Mark as a system object

EXECUTE sp_ms_marksystemobject 'sp_find'

--#################################################################################################

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 6

Lowell (2/6/2014)
i actually make things system functions , system procedures, and stuff quite a bit i have a ton of developer tools i've created and adapted that go in master.

Could you please show how to make system FUNCTION (not a proc)?

_____________________________________________
"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]

ben.brugman SSChampion Points: 13369 More actions · Answer 7

Lowell (2/6/2014)
i actually make things system functions , system procedures, and stuff quite a bit i have a ton of developer tools i've created and adapted that go in master.

Do you have an example with a Function, for example the function:

DelimitedSplit8K

I can do it with a stored procedure, but have nog managed this with a function.

Ben

Lowell SSC Guru Points: 323518 More actions · Answer 8

misspoke on "functions", sorry. unfortunately functions don't follow those nifty abilities like procs.

views can be immediately referenced if they start witk sp_, so i have things like sp_Trace02 in my db

renaming to sp_DelimitedSplit8k or fn_DelimitedSplit8k, for example, doesn't make it available in other databases; you have to fully reference it as master.dbo.fn_DelimitedSplit8k, so it doesn't matter if a function is marked as a system object or not.

select * from master.dbo.fn_DelimitedSplit8k('1,2,3,4,5',',')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!