A new project, incomplete documentation, and the original author gone. I need to know “what updates and uses this object?”. So, how do I search for it?.<\/p>\n
If there is good source control and code management, a simple search will work. In many cases, there isn’t the luxury of code management.<\/p>\n
I have cooked up these short scripts to help with the search through through objects. The code which makes up views and stored procedures is accessible through some sys views. This allows us to find what uses a table or other object, or even look for a magic number.<\/p>\n
Both scripts dynamically create an SQL statement, which searches through the object definitions. Note, this type of dynamic SQL is not ideal for production code for security reasons. As this isn’t a production procedure, this is a good time to use dynamic SQL.<\/p>\n
Most of the time, we are only interested in a single database\/catalogue in SQL server. The below allows you to change the value for @search_text to anything you’d like to search for.<\/p>\n
-------------------------------------------------------------------------------\r\n-- Current DB Catalogue\r\n-------------------------------------------------------------------------------\r\nDECLARE @search_text VARCHAR(200);\r\nSET @search_text = '%reporting_entites%';\r\nSELECT o.[object_id],\r\n db_name() AS [db_catalog_name],\r\n o.[name] AS [Object_Name],\r\n m.[definition],\r\n o.[type_desc],\r\n s.[name] AS [schema_name],\r\n '[' + s.[name] + '].[' + o.[name] + ']' AS [formatted_Name]\r\n FROM sys.objects o\r\n LEFT JOIN sys.sql_modules m ON m.[object_id] = o.[object_id]\r\n LEFT JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]\r\n WHERE(LOWER(m.definition) LIKE LOWER(@search_text)\r\n OR LOWER(o.name) LIKE LOWER(@search_text)\r\n )\r\n;<\/pre>\nV2<\/h2>\n
Allowing for quotes in the string and dynamically adding the percentages<\/p>\n