UDF system function

[Fred]

Has anyone managed to create a User Defined System Function that returns a
table?

I have read that the following rules should apply when creating a system UDF

1) Function should be prefixed 'fn_' and live on Master.
2) The name should be all lower case
3) The owner should be system_function_schema, not dbo.
4) System table updates should be on when creating the function.

I am trying to list the columns that form the PK of a table.
When creating the function in PUBS under dbo it works fine.

The following scripts creates the same function but as a system User Defined
function.
When I try to call it it give me an error i.e:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'fn_pkcolumns'.

The odd thing is that I have managed to create a a function that returns a
string, (makes all char upper) and that works.

Any Idea anyone.

Use Master
go

--The owner of the stored proc MUST BE system_function_schema, and requires
updates to systems tables to be allowed
--for the Create function to work
EXEC SP_CONFIGURE 'ALLOW UPDATES', 1
go
RECONFIGURE WITH OVERRIDE
go

--UID = 4 is system_function_schema
--The function name must start 'fn_' and be all lower case
If (select count(*) from sysobjects where type = 'TF' and Name =
'fn_pkcolumns' and UID = 4) = 1 Begin
Drop Function system_function_schema.fn_pkcolumns --Must be in 2 part name
Print 'Dropping FUNCTION fn_pkcolumns'
End
go

Create function system_function_schema.fn_pkcolumns(@TableName sysname)
Returns @ColumnName Table(Name sysname) AS
BEGIN
Declare @IndexID as integer
Declare @ColCounter as integer

Select
@ColCounter = 1,
@IndexID = IndID
from sysindexes I
where I.id = object_ID(@TableName)
and I.indid > 0
and I.indid < 255
and (I.status & 64)=0 --Probaly not relevant but used in sh_helpIndex so
keep it
and (I.status & 2048)=2048 --Primary Key

While Index_col(@TableName, @IndexID, @ColCounter) is Not Null Begin
Insert into @ColumnName(Name) select Index_col(@TableName, @IndexID,
@ColCounter)
Set @ColCounter = @ColCounter + 1
End

Return
END
go

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

Use Pubs
go
select Name from fn_pkcolumns('Titles')
go

[Itzik Ben-Gan]

Fred,

Table Valued System UDFs should be referred to with preceding double colons:

SELECT Name FROM ::fn_pkcolumns('Titles')

--
BG, SQL Server MVP
Solid Quality Learning
www.solidqualitylearning.com

"Fred" <fred.bo...@dorsetsoftware.com> wrote in message
news:u289byKsCHA.436@TK2MSFTNGP12...

[oj]

Fred,

You need :: to return the data...

select Name from ::fn_pkcolumns('titles')

--
-oj
http://www.rac4sql.net

"Fred" <fred.bo...@dorsetsoftware.com> wrote in message
news:u289byKsCHA.436@TK2MSFTNGP12...

[Uri Dimant]

Hi,all (SQL SERVER 2000,SP 2)
Does it depend on version of sql server or service pack?
I got error "sql server does not recognize this function"

select Name from ::fn_pkcolumns('titles')

oj <nospam...@home.com> wrote in message
news:uqJx#ELsCHA.2532@TK2MSFTNGP10...

[oj]

Uri,

No, you must first create the function, however...

use master
go

exec sp_configure 'allow updates', 1
go
reconfigure with override
go

if exists(select * from sysobjects where type = 'tf' and name='fn_pkcolumns'
and uid = 4)
begin
drop function system_function_schema.fn_pkcolumns
print 'dropping function fn_pkcolumns'
end
go

create function system_function_schema.fn_pkcolumns(
@tablename sysname)
returns @columnname table(name sysname) as
begin
insert @columnname
select index_col(@tablename, i.indid, n)
from sysindexes i cross join (select 1 union select 2
union select 3 union select 4 union select 5 union select 6
union select 7 union select 8 union select 9 union select 10
union select 11 union select 12 union select 13 union select 14
union select 15 union select 16) d(n)
where i.id = object_id(@tablename)
and i.indid > 0
and i.indid < 255
and (i.status & 64)=0
and (i.status & 2048)=2048
and index_col(@tablename, i.indid, n) is not null
return
end
go

exec sp_configure 'allow updates', 0
go
reconfigure with override
go

use pubs
go
select name from ::fn_pkcolumns('titles')
go

--
-oj
http://www.rac4sql.net

"Uri Dimant" <ur...@iscar.co.il> wrote in message
news:uczL5NLsCHA.1624@TK2MSFTNGP10...

[Uri Dimant]

Thank,you
Happy New Year!!!!!!!!!!!!!!!!!!!

oj <nospam...@home.com> wrote in message

news:O9lRAaLsCHA.2308@TK2MSFTNGP09...

[oj]

Same to you! :~)

you could also replace
....

and I.indid > 0
and I.indid < 255
and (I.status & 64)=0

and (I.status & 2048)=2048
....
with
....
and (i.status & 0x800) = 0x800
....

and it would still work.

--
-oj
http://www.rac4sql.net

"Uri Dimant" <ur...@iscar.co.il> wrote in message

news:u0YgEhLsCHA.2628@TK2MSFTNGP09...
> Thank,you
> Happy New Year!!!!!!!!!!!!!!!!!!!
>
>
>

[Fred]

Thanks for the answer, '::' did the trick.

I now have another question.

The function is to be used in a system stored proc so that I can get
SQLServer to generate SP code for me
e.g.
Stored proc header:
Create Procedure sp_CreateStoredProcForInsert
@ObjectName sysname, --Table Name
@TablePrefix varchar(5) = 'sp', --If no default prefix for the stored
proc supplied we use 'sp'
@IncludePKColumns bit = 0, --By default do no include PK Fields
@IncludeIndentity bit = 0 as --same with Identity columns

and then on the Pubs DB I can run:
execute sp_CreateStoredProcForInsert 'titles', 'XP_',0,0

Which gives me:
----------------------------------------------------------------------------
--------------------------------------------------------
If (select count(*) from sysobjects where type = 'P' and Name =
'XP_titlesAdd' and UID = 1) = 1 Begin
DROP PROCEDURE XP_titlesAdd
Print 'Dropping PROCEDURE XP_titlesAdd'
End

Print 'Creating PROCEDURE XP_titlesAdd'
Create Procedure XP_titlesAdd
@title_id tid,
@title varchar(80) ,
@type char(12) ,
@pub_id char(4) ,
@price money,
@advance money,
@royalty int,
@ytd_sales int,
@notes varchar(200) ,
@pubdate datetime AS

Insert into titles
(title_id, title, type, pub_id, price, advance, royalty, ytd_sales, notes,
pubdate)
Values
(@title_id, @title, @type, @pub_id, @price, @advance, @royalty, @ytd_sales,
@notes, @pubdate)
go

Grant execute on XP_titlesAdd to Public
go

----------------------------------------------------------------------------
--------------------------------------------------------
This is great except for the fact that title_id should be excluded, and it
is not, even though @IncludePKColumns is 0.

I guess this is because ::fn_pkcolumns is called in the context of the
master database, since this is where sp_CreateStoredProcForInsert lives.

Is there a way of telling ::fn_pkcolumns to run in the contect of the DB
that called sp_CreateStoredProcForInsert?

"Fred" <fred.bo...@dorsetsoftware.com> wrote in message
news:u289byKsCHA.436@TK2MSFTNGP12...

[oj]

Look like there is a simple mistake in your filtering. If you post the
sproc's code, someone might be able to help correct it.

--
-oj
http://www.rac4sql.net

"Fred" <fred.bo...@dorsetsoftware.com> wrote in message

news:ekTHLIOsCHA.2352@TK2MSFTNGP09...