Skip to content

Instantly share code, notes, and snippets.

@andyj

andyj/findSPByName.sql

Created October 26, 2015 22:51
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save andyj/dde48ba98c1a8b55bb86 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save andyj/dde48ba98c1a8b55bb86 to your computer and use it in GitHub Desktop.
Download ZIP
Searching MSSQL database for a Stored Proc by name
Raw
findSPByName.sql
sp_MSforeachdb 'SELECT "?" AS DB,
r.SPECIFIC_SCHEMA, r.routine_name, r.ROUTINE_DEFINITION, p.ORDINAL_POSITION, p.PARAMETER_MODE, p.PARAMETER_NAME, p.DATA_TYPE
FROM [?].INFORMATION_SCHEMA.ROUTINES r
JOIN [?].INFORMATION_SCHEMA.PARAMETERS p ON r.SPECIFIC_SCHEMA = p.SPECIFIC_SCHEMA AND r.SPECIFIC_NAME = p.SPECIFIC_NAME
WHERE
r.ROUTINE_TYPE = ''PROCEDURE''
AND left(r.ROUTINE_NAME,2) <> ''dt''
AND r.routine_name LIKE ''%yourStoredProcName%''
ORDER BY r.SPECIFIC_NAME ASC, p.ORDINAL_POSITION ASC'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment