How do I get the def of a view

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  May 11, 2016 at 10:53 am

  #316449

  The following gets the def of all the stored procs.

  Similarly can i get the SQL for finding the def of all views ?

  SELECT p.name, m.definition

  --, charindex('FRGClaims', m.definition, 1 )

  FROM sys.procedures p

  JOIN sys.sql_modules m ON p.object_id = m.object_id

  --where

  --charindex('FRGClaims', m.definition, 1 ) > 0

  --ORDER BY 3 desc

• Jacob Wilkins

  One Orange Chip

  Points: 27976

  More actions

  May 11, 2016 at 10:58 am

  #1878321

  sys.sql_modules will also have view definitions.

  You'd just need to join to sys.views instead.

  I'd actually prefer just joining to sys.objects. You can then just filter to whatever object type or types you are looking for.

  Cheers!

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  May 11, 2016 at 11:05 am

  #1878324

  Thx,

  i found it on google

  SELECT TABLE_NAME as ViewName,

  charindex('fnGetProviderHoursByProviderNo', VIEW_DEFINITION, 1 ),

  VIEW_DEFINITION as ViewDefinition

  FROM INFORMATION_SCHEMA.Views

  where

  charindex('fnGetProviderHoursByProviderNo', VIEW_DEFINITION, 1 ) > 0