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:
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:
COALESCE(PARAMETER_NAME, ‘<no params>’) as ‘Parameter’,
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’
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!
One thought on “Some INFORMATION_SCHEMA magic”
to search an object like Procedure or View or Function, I tried another system view ‘sys.sql_modules’, working like a charm as well.