Query to return internal details about stored function in SQL Server database

Question

I have been given access to a SQL Server database that is currently used by 3rd party app. As such, I don't have any documentation on how that application stores the data or how it retrieves it.

I can figure a few things out based on the names of various tables and the parameters that the user-defined functions takes and returns, but I'm still getting errors at every other turn.

I was thinking that it would be really helpful if I could see what the stored functions were doing with the parameters given to return the output. Right now all I've been able to figure out is how to query for the input parameters and the output columns.

Is there any built-in information_schema table that will expose what the function is doing between input and output?

Answer 1

Provided you have appropriate permissions, you can simply script out all Stored Procedures and Functions:

Right-click on your database in SSMS (SQL Server Management Studio), select Tasks –> Generate Scripts, ensure your database is highlighted and click next. Ensure the options to script out Stored Procedures and Functions are selected.

You can install SSMS (client Tools) without requiring a SQL Server license.

Answer 2

If you can execute a query against your database somehow, and if you have the necessary permissions to read the system catalog views, then you could run this query to get the name, the definition (SQL code) and a few more bits of information about your functions:

SELECT 
    obj.name ,
    obj.type ,
    obj.type_desc ,
    obj.create_date ,
    obj.modify_date ,
    m.definition ,
    m.is_schema_bound 
FROM 
    sys.objects obj
INNER JOIN 
    sys.sql_modules m ON obj.object_id = m.object_id
WHERE 
    obj.type IN ('AF', 'FN', 'FS', 'FT', 'IF', 'TF')

Answer 3

Another way is sp_helptext which will show you the source of the passed SP or UDF;

sp_helptext fnBlaDeBla