How do I search my SQL Database for reference to any term in the definition of Views, Sprocs, Tables-CodePudding

How do I search my SQL Database for reference to any term which is employed in the definition of Views, Sprocs, Tables, etc? I'm not looking to search for data content, rather for terms which appear within the definition of objects within the database.

The proposed solution must include TABLES as well as the other database artifacts.

CodePudding user response：

Here is a script which I have been refining over a period of time. It is comprised of two separate queries which are unioned together to produce the comprehensive output; The first query scans all database artifacts except for tables, and the second query scans through the table definitions. (Credit: based on various tips found elsewhere on StackOverflow. This script puts them together for convenience.) I use this script on SQL Server 2012.

declare @SearchTerm varchar(max) = '%Role%'

select found.*
  from (
        -- Scripted artifacts, e.g. Views, Sprocs, etc.
        select schema_name(o.schema_id) as [Schema], 
               o.Name as [Name], 
               o.type as [Type]
          from sys.sql_modules m
          full JOIN sys.objects o ON o.object_id = m.object_id
         where m.definition like @SearchTerm
            or o.Name like @SearchTerm
        UNION
        -- Tables
        select distinct 
               schema_name(tab.schema_id) as [Schema],
               tab.name as [Name],
               'T' as [Type]
            from sys.columns col
            join sys.tables  tab on col.object_id = tab.object_id
         where col.name like @SearchTerm
            or tab.name like @SearchTerm
       ) found
 -- Add in any filters if you wish to limit the results
 where found.[Schema] <> 'zzz'

CodePudding user response：

Here's what I use...

--declare @nvPattern nvarchar(512) = '%INSERT INTO \[dbo\].\[Test\]%'
declare @nvPattern nvarchar(512) = '%\[SearchString\]%'
SELECT DISTINCT
s.Name AS SchemaName,
o.name AS Object_Name,
@nvPattern as Pattern,
substring(m.definition, PATINDEX(@nvPattern,m.definition)-50, PATINDEX(@nvPattern,m.definition) 50) as Found,
o.type_desc
FROM sys.sql_modules m
INNER JOIN
sys.objects o
ON m.object_id = o.object_id
INNER JOIN sys.schemas s
    ON o.schema_id = s.schema_id
WHERE m.definition Like @nvPattern ESCAPE '\'