[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1716
  • Last Modified:

How to find / list all views with specific string (SQL 2005)

How to find / list all views with specific string?

I found the equivalent for the stored procedure:
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_DEFINITION LIKE '%foo%'


This is exactly what I am looking for but this only shows procedures... I want to have a list of the views...

Thank you
0
yellow1234
Asked:
yellow1234
  • 2
  • 2
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
SELECT VIEW_NAME, VIEW_DEFINITION
    FROM INFORMATION_SCHEMA.VIEWS
    WHERE VIEW_DEFINITION LIKE '%foo%'
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, it is TABLE_NAME ...

SELECT TABLE_NAME, VIEW_DEFINITION
    FROM INFORMATION_SCHEMA.VIEWS
    WHERE VIEW_DEFINITION LIKE '%foo%'
0
 
imitchieCommented:
You can query Views and Procedures at the same time

select distinct object_name(o.id) as [Name], case o.xtype
 when 'P' then 'Procedure'
 when 'FN' then 'Scalar function'
 when 'IF' then 'Inline function'
 when 'TF' then 'Table valued function'
 when 'V' then 'View'
 end as [Object Type]
from syscomments i
 inner join sysobjects o on o.id = i.id
where o.xtype in ('P','FN','IF','TF','V')
 and text like '%foo%'
0
 
yellow1234Author Commented:
Thanks guys.
Angelll - that was spot on - imitchi - that was really useful, therefore gave you some points as well.
Thanks again.
0
 
yellow1234Author Commented:
just
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now