--------------------------------------------------------------------------------
User-Defined Functions
--------------------------------------------------------------------------------
How can I create a user-defined function in SQL Server 2000?
You can use the CREATE FUNCTION statement to create user-defined functions in SQL Server 2000.
How can I create a user-defined inline table function in SQL Server 2000?
You can use the CREATE FUNCTION statement and specifiy RETURNS TABLE to create an inline table function in SQL Server 2000. An inline table function will return a table with no accompanying column list.
How can I create a user-defined multi-statement table function in SQL Server 2000?
You can use the CREATE FUNCTION statement and specify RETURNS @x TABLE ([columns]) with the table definition to create a multi-statement table function in SQL Server 2000. A multi-statement table function will return a column list along with the result set.
How can I create a user-defined function with parameters in SQL Server 2000?
You can use the CREATE FUNCTION statement and specify your parameter names to create a user-defined function with parameters in SQL Server 2000.
How can I create an encrypted user-defined function in SQL Server 2000?
You can use the CREATE FUNCTION statement and specify the WITH ENCRYPTION clause to encrypt the text of a function in SQL Server 2000.
How can I create a user-defined function in SQL Server 2000 that binds the schema of underlying objects?
You can use the CREATE FUNCTION statement and specify the WITH SCHEMABINDING to bind the schema of underlying objects in a user-defined function in SQL Server 2000. Schema bound objects cannot have their schema changed until all other objects that are bound to them release the bindings.
How can I alter the text of a user-defined function in SQL Server 2000?
You can use the ALTER FUNCTION statement to change the text of an existing user-defined function in SQL Server 2000.
How can I alter the parameters of a a user-defined function in SQL Server 2000?
You can use the ALTER FUNCTION statement to change the parameters of an existing user-defined function in SQL Server 2000.
How can I make a user-defined function encrypted in SQL Server 2000?
You can use the ALTER FUNCTION statement and specify the WITH ENCRYPTION to bind the schema of underlying objects in a user-defined function in SQL Server 2000.
How can I make a user-defined function bind the schema of an underlying object in SQL Server 2000?
You can use the ALTER FUNCTION statement and specify the WITH SCHEMABINDING to bind the schema of underlying objects in a user-defined function in SQL Server 2000.
How can I drop a user-defined function from SQL Server 2000?
You can use the DROP FUNCTION statement to drop user-defined functions.
How can I return a list of user-defined functions in a database?
Query the information_schema.routines system view or the sysobjects system table to return a listing of currently accessible user-defined functions in a database.
SELECT name FROM sysobjects WHERE type = 'FN'
How can I return column information for a user-defined function?
Execute the sp_sproc_columns system stored procedure or query the information_schema.routines_columns system view to return column information on a user-defined function in SQL Server 2000. You can also query the syscolumns system table to return column information on a user-defined function.
SELECT so.name, sc.name FROM syscolumns sc
INNER JOIN sysobjects so
ON sc.id = so.id
WHERE so.name = 'FUNCTIONNAME'
How can I return parameter information for a user-defined function?
Query the information_schema.parameters system view to return a listing of currently accessible user-defined functions in a database. You can also query the syscolumns system table to return parameter information on a user-defined function.
SELECT so.name, sc.name FROM syscolumns sc
INNER JOIN sysobjects so
ON sc.id = so.id
WHERE so.name = 'FUNCTIONNAME'
How can I return basic information about a user-defined function?
Execute the sp_help system stored procedure or query the sysobjects system table to obtain basic information on a user-defined function.
SELECT * FROM sysobjects WHERE type = 'FN'
How can I return a user-defined function's text?
Execute the sp_helptext system stored procedure, query the syscomments system table, or query the information_schema.routines system view to return the text of a user-defined function.
SELECT sc.text FROM syscomments sc
INNER JOIN sysobjects so
ON sc.id = so.id
WHERE so.type = 'FN'
How can I add an extended property to a user-defined function?
Execute the sp_addextendedproperty system stored procedure to add an extended property to a user-defined function in SQL Server 2000.
How can I update an extended property on a user-defined function?
Execute the sp_updateextendedproperty system stored procedure to update an extended property on a user-defined function in SQL Server 2000.
How can I drop an extended property from a user-defined function?
Execute the sp_dropextendedproperty system stored procedure to drop an extended property from a user-defined function in SQL Server 2000.
How can I list extended properties on a user-defined function?
Query the fn_listextendedproperty system function or the sysproperties system table to list extended properties on a user-defined function in SQL Server 2000.
SELECT so.name, sp.name
FROM sysproperties sp
INNER JOIN sysobjects so
ON sp.id = so.id
WHERE so.name = 'FUNCTIONNAME'
How can I rename a user-defined function?
You can execute the sp_rename system stored procedure to rename a user-defined function.
How can I determine what objects a user-defined function depends upon?
Execute the sp_depends system stored procedure or query the sysdepends system table to return a list of objects that a user-defined function depends upon.
SELECT DISTINCT so1.name, so2.name FROM sysobjects so1
INNER JOIN sysdepends sd
ON so1.id = sd.id
INNER JOIN sysobjects so2
ON so2.id = sd.depid
WHERE so1.name = 'FUNCTIONNAME'
Both of the methods above may not return all the objects that the specified function depends upon due to the fact that the object may not have existed when the function was created or the object is called as part of a dynamic statement. To find these missing dependencies you can use the syscomments system table and search through the text of a all objects for the specified function name.
SELECT so.name FROM sysobjects so
INNER JOIN syscomments sc
ON so.id = sc.id
WHERE sc.text LIKE '%FUNCTIONNAME%'
How can I determine what objects depend upon a user-defined function?
Execute the sp_depends system stored procedure or query the sysdepends system table to return a list of objects that depend upon a user-defined function.
SELECT DISTINCT so1.name, so2.name FROM sysobjects so1
INNER JOIN sysdepends sd
ON so1.id = sd.id
INNER JOIN sysobjects so2
ON so2.id = sd.depid
WHERE so2.name = 'FUNCTIONNAME'
Both of the methods above may not return all the objects that depend upon the specified function due to the fact that the specified function may not have existed when the object was created or the function is called as part of a dynamic statement. To find these missing dependencies you can use the syscomments system table and search through the text of a all objects for the specified function name.
SELECT so.name FROM sysobjects so
INNER JOIN syscomments sc
ON so.id = sc.id
WHERE sc.text LIKE '%FUNCTIONNAME%'
How can I return the SQL statement for a SPID?
You can use the fn_get_sql system function to return the SQL statements for most SPIDs.
|
No responses found. Be the first to respond and make money from revenue sharing program.
|