In such situation, you will need a search feature which can aid you to find the word you are looking for.
I know that you are not supposed to face such case because there should be a proper sort of documentation for the database design. But, believe me in some cases this type of documentation is not found and you are asked to work on the database to fix some bugs or whatever.
So, to do your job, you may need to find a certain word in the database. Why? because this word may be a key word for your whole issue.
To imagine what I am talking about here, let's have a look on these cases:
- Want to know where a certain column in a table is updated
- Want to know where a certain stored procedure is called
- Want to know where data is inserted into a certain table
- Want to know where a certain table is used to retrieve data from
- Want to know where a certain view is used to retrieve data from
These are just some examples and there are many others. So, to carry out these tasks, you need to search the whole database items for a word or a pattern to find the database items which include this word or pattern into its code definition. Then, you can work on the analysis of the results you got.
So, I wrote a SQL script which carries out this search functionality. The script is as below:
use [DbName] --Database name GO DECLARE @SearchText varchar(max) SET @SearchText='KeyWord' --The (word)/(like pattern) to search for SELECT DISTINCT '[' + s.[name] + '].[' + obj.name + ']' AS [ObjectName] , obj.type_desc AS [Object Type] , module.definition AS [Code] , obj.create_date AS [Date Created] , obj.modify_date AS [Date Modified] FROM sys.sql_modules module INNER JOIN sys.objects obj ON module.object_id = obj.object_id INNER JOIN sys.schemas s ON s.schema_id = obj.schema_id WHERE module.definition Like '%' + @SearchText + '%' GROUP BY obj.type_desc , '[' + s.[name] + '].[' + obj.name + ']' , module.definition , obj.create_date , obj.modify_date ORDER BY obj.type_desc , '[' + s.[name] + '].[' + obj.name + ']' , module.definition , obj.create_date , obj.modify_date
To use the script, you just need to update the database name in the "use" statement (first line) and the word or the like pattern you wish to search for (fifth line).
Wish this helps you someday :)