Some INFORMATION_SCHEMA magic

There are times when you want to get a little creative about your investigative work when it comes to SQL Server.  In this case, this is a very short article highlighting some efficiencies in locating information about specific programmatic resources within a database.

If you haven’t used the INFORMATION_SCHEMA views before, well you’re in for a treat. 
Let’s say you need to query the details of a Stored Procedure, and all you know is the name of the proc. 

How would you go about retrieving the information?

One way is to use the INFORMATION_SCHEMA.ROUTINES.  For this example, let us assume our proc is called “usp_TestProcedure”, and execute the following:

SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = ‘PROCEDURE’
  AND ROUTINE_NAME = ‘usp_TestProcedure’
ORDER BY ROUTINE_NAME

This results in a plethora of information (some of it not used).  Of interest are the following columns:

ROUTINE_CATALOG – location of the routine
ROUTINE_SCHEMA – schema of the routine
ROUTINE_NAME – name of the routine

ROUTINE_TYPE – Type of routine, e.g. Procedure/Function etc
ROUTINE_BODY – Whether it is T-SQL, other etc
ROUTINE_DEFINITION – The actual text of the routine

CREATED  – Created Date
LAST_ALTERED – Modified date

All well and good.. but what of those Parameters?  We can query those details from INFORMATION_SCHEMA.PARAMETERS as follows:

SELECT 
  COALESCE(PARAMETER_NAME, ‘<no params>’) as ‘Parameter’,
  COALESCE(UPPER(DATA_TYPE) +
  CASE
    WHEN DATA_TYPE IN (‘NUMERIC’, ‘DECIMAL’) THEN
     ‘(‘ + CAST(NUMERIC_PRECISION AS VARCHAR) 
        + ‘, ‘ + CAST(NUMERIC_SCALE AS VARCHAR) + ‘)’ 
    WHEN RIGHT(DATA_TYPE, 4) = ‘CHAR’ THEN
        ‘(‘ + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ‘)’
        ELSE ” END + CASE PARAMETER_MODE
    WHEN ‘INOUT’ THEN ‘ OUTPUT’ ELSE ‘ ‘
    END, ‘-‘) as ‘Data Type’
FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_NAME = ‘usp_TestProcedure’
ORDER BY ORDINAL_POSITION

Obviously this applies a bit of logic to format the output, depending on the type of parameter, there are plenty of useful columns in this view which would help you determine more about each parameter, such as numeric precision, maximum lengths, parameter direction (in/out) and so on.

Now, what happens when you alter a Stored Procedure and the information isn’t immediately refreshed in the INFORMATION_SCHEMA?  Well, for SQL Server, try running a Stored Procedure recompile by executing the following:

EXECUTE sp_recompile <proc name>

Enjoy using INFORMATION_SCHEMA!