February 6, 2014 at 4:18 am
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
February 6, 2014 at 4:36 am
Just a thought ...
why not use FQN (fully qualified name) to access function created in different database ?
February 6, 2014 at 5:42 am
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.
February 6, 2014 at 5:44 am
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
February 6, 2014 at 5:52 am
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
February 6, 2014 at 5:52 am
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
February 6, 2014 at 6:04 am
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)?
February 6, 2014 at 6:08 am
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
February 6, 2014 at 6:25 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply