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:
Hope this helps you!!!!!
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
No comments:
Post a Comment