-3

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.

Yossi G.
  • 939
  • 1
  • 8
  • 22
  • Does this answer your question? [How to find a text inside SQL Server procedures / triggers?](https://stackoverflow.com/questions/674623/how-to-find-a-text-inside-sql-server-procedures-triggers) – Thom A Dec 15 '22 at 16:31
  • 1
    I don't often suggest using software but this is an exception. SQL Search from RedGate does this really well. It is a free plugin for SSMS. – Sean Lange Dec 15 '22 at 16:32
  • 2
    I'd propose lack of research, @SeanLange ; it didn't take me wrong, for example, to find that duplicate candidate. – Thom A Dec 15 '22 at 16:33
  • I figured others might benefit from this script which I and my team find immensely useful. It's true no good deed goes unpunished. I took the time to post this to help others out and I've been dinged six points on my reputation here. I won't make that mistake again. – Yossi G. Dec 15 '22 at 19:17
  • @Larnu no your suggestion does NOT match my solution. My solution searches table definitions as well. Your solution found EmployeeId in SQL_SCALAR_FUNCTION, SQL_STORED_PROCEDURE, SQL_TABLE_VALUED_FUNCTION, SQL_TRIGGER and VIEW but NOT tables. – Yossi G. Dec 15 '22 at 19:23
  • @YossiGeretz where did I say I downvoted...? Did you, perhaps, consider that the problem isn't the comprehension of 3+ users, but the unclearness of your question or the lack of evidenced research which might have included a link to the proposed duplicate and an explanation of why it isn't helpful? – Thom A Dec 15 '22 at 19:37
  • 1
    For reference: [Why shouldn't I assume I know who downvoted my post?](https://meta.stackoverflow.com/questions/388686/why-shouldnt-i-assume-i-know-who-downvoted-my-post) it's such assumptions that are one of the reasons some users refuse to comment on unclear posts, because if they offer criticism of the question *they* are blamed for any and all downvotes... I answered why it might have been downvoted and you've read that as "I downvoted because..."; they aren't synonyms. As a note as well, [so] isnt a "great forum" as it isn't a forum; using it like one often leads to poor experiences. – Thom A Dec 15 '22 at 19:47

1 Answers1

1

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.

Clarification: This script is unique with respect to other scripts which I've seen elsewhere, in that it combines the standard DEFINITIONS search script, which does not search table definitions, with a listing of Table Names and Column Names in order to apply the search criteria against TABLE definitions as well.

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 ALL
        -- 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'
Yossi G.
  • 939
  • 1
  • 8
  • 22
  • 1
    Why use `UNION` *and* `DISTINCT` in the same scope? Why force the data engine to get the distinct columns and then implicitly distinct that again with the other dataset? The `DISTINCT` is unnecessary overhead here. – Thom A Dec 15 '22 at 19:42
  • You can pop that distinct out to the top if you like. Have you examined the respective plans? If the database engine knows it's going to apply the distinct directive to the unioned set, why doesn't it just defer the operation? Are you sure it doesn't? I did some primitive SQL using both options and a comparison of the estimated execution plan shows the same plan. – Yossi G. Dec 15 '22 at 19:57
  • 1
    The `DISTINCT` shouldn't be there; `UNION` forces the dataset to be distinct rows already. Why run the risk of having it do a costly operation twice? Alternatively switch to `UNION ALL`. That actually might be the more performant option if the top dataset can never return duplicates. – Thom A Dec 15 '22 at 20:14
  • I've given this an upvote any way, but I stand by that it could have some minor improvement. – Thom A Dec 15 '22 at 20:27
  • @Larnu Thank you! I did not know that UNION is inherently distinct. I thought that the UNION and DISTICTness are two separate operations. You are correct and I will edit the solution to remove the DISTINCT since the UNION will do it. Unless you recommend that UNION ALL with DISTINCT on the second block is preferable. There is only the possibility of duplication within the second SQL block and the rows returned by the second block can never be duplicates of rows returned by the first block. Because the second block searches tables and the first block searches everything else. – Yossi G. Dec 15 '22 at 20:39
  • 1
    I'd switch to`UNION ALL` then. – Thom A Dec 15 '22 at 20:40
  • @Larnu Done. Thanks! I appreciate what you just taught me. – Yossi G. Dec 15 '22 at 20:43