Searching SQL Stored Procedure Source Code in Information_Schema.Routines

SQL Server 2008
-- Here's a little line of code I use a lot to find out what SQL stored 
procedures / functions use a particular table

SELECT *
FROM   information_schema.routines
WHERE  routine_definition LIKE '%GetThemeColours%'

-- INFORMATION_SCHEMA.ROUTINES is similar to sys.routines but 
has all the text names instead of objectid's  with the added bonus the full 
stored procedure text is searchable (its in ROUTINE_DEFINITION column) - its like 
a super powered sp_helptext
-- Also check out these helpful views:
-- View all the columns in tables + views with full table/view name
SELECT *
FROM   information_schema.columns

-- stored procedure / function parameters
SELECT *
FROM   information_schema.parameters

-- output columns for table valued functions (NOT stored procedures)
SELECT *
FROM   information_schema.routine_columns

-- list of tables
SELECT *
FROM   information_schema.tables

-- list of views
SELECT *
FROM   information_schema.tables 

 

-- 195cac785d844883be7a61f2b54189c6



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Searching SQL Stored Procedure Source Code in Information_Schema.Routines | 0 comments | Create New Account
The following comments are owned by whomever posted them. This site is not responsible for what they say.


Gold Coast Aquarium Maintenance | Gold Coast Marine Fish | Gold Coast Tropical Fish
Jewel Jones - Counselling Service - Penrith, Richmond
 
     
 Copyright © 2017 Julian Kuiters
 All trademarks and copyrights on this page are owned by their respective owners.