November 8, 2007 at 1:41 pm
I am guess in one of the system tables in the master not sure where?
November 8, 2007 at 1:46 pm
In User database were it has created and look under User defined function
Nita
November 10, 2007 at 10:01 pm
Run this in all the databases:
SELECT *
FROM sys.objects
WHERE RIGHT(type_desc, 8) = 'FUNCTION'
K. Brian Kelley
@kbriankelley
November 11, 2007 at 8:00 am
Hi,
U can use the following:
Create Proc getAllFN
AS
Set Nocount On
Declare @dbName as sysname
Declare @Query as varchar(200)
Create Table #sys_all_objects
(dbName sysname null
,name sysname
,object_id int
,schema_id int
,principal_id int
,parent_object_id int
,type char(2)
,type_desc nvarchar(60)
,create_date datetime
,modify_date datetime
,is_ms_shipped bit
,is_published bit
,is_schema_published bit)
Declare cDB Cursor for
Select name From master.sys.databases
Open cDB
Fetch Next From cDB Into @dbName
While @@Fetch_Status=0
Begin
Set @Query= 'Use ' + @dbName + ' SELECT * FROM sys.all_objects where type in (''FN'',''AF'',''FS'',''FT'',''IF'',''TF'')'
Insert #sys_all_objects (name,object_id,schema_id,principal_id,parent_object_id,type,type_desc,create_date,modify_date,is_ms_shipped,is_published,is_schema_published)
exec(@Query)
Update #sys_all_objects
Set dbName=@dbName
Where dbName is null
Fetch Next From cDB Into @dbName
End
Close cDB
Deallocate cDB
Select * From #sys_all_objects
Drop Table #sys_all_objects
Set NoCount Off
Go
--Execute the proc
exec getAllFN
--Drop the proc
Drop proc getAllFN
--AF = Aggregate function (CLR)
--C = CHECK constraint
--D = DEFAULT (constraint or stand-alone)
--F = FOREIGN KEY constraint
--PK = PRIMARY KEY constraint
--P = SQL stored procedure
--PC = Assembly (CLR) stored procedure
--FN = SQL scalar-function
--FS = Assembly (CLR) scalar function
--FT = Assembly (CLR) table-valued function
--R = Rule (old-style, stand-alone)
--RF = Replication filter procedure
--SN = Synonym
--SQ = Service queue
--TA = Assembly (CLR) trigger
--TR = SQL trigger
--IF = SQL inlined table-valued function
--TF = SQL table-valued function
--U = Table (user-defined)
--UQ = UNIQUE constraint
--V = View
--X = Extended stored procedure
--IT = Internal table
Regards
Ahmed
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply