Thursday, March 22, 2012

Searching Text in Procedures, Tables and Functions

In my project there is requirement to change the field length eg CompanyCode. Problem is with this field name there are many tables who not created the foriegn keys and this field is used in many procedures and functions. To find the text company code I found this simple code:

CREATE PROCEDURE adhoc_SearchText(@text VARCHAR(1024))
AS
BEGIN
 -- tables
 SELECT
  TABLE_NAME
 FROM
  INFORMATION_SCHEMA.TABLES T
 WHERE
  charindex(@text, T.TABLE_NAME)>0
  
 -- columns
 SELECT 
  C.TABLE_NAME, C.COLUMN_NAME
 FROM
  INFORMATION_SCHEMA.COLUMNS C
 WHERE
  charindex(@text, C.COLUMN_NAME)>0

 -- views
 SELECT 
  V.TABLE_NAME AS VIEW_NAME
 FROM 
  information_schema.VIEWS V
 WHERE 
  charindex(@text, V.VIEW_DEFINITION)>0
  
 -- stored procs
 SELECT 
  R.ROUTINE_NAME 
 FROM 
  information_schema.routines r 
 WHERE 
  charindex(@text, r.ROUTINE_DEFINITION)>0
END
Hope this helps you!!!!!

No comments:

Post a Comment