Retrieving the query from the view

• THIRUMALARAJKUMAR

  SSC-Addicted

  Points: 488

  More actions

  January 24, 2005 at 6:54 am

  #183947

  Hi,

  I want to have the contents of the view, means query of the view.

  Actually i want to read all the view names and the query of the view from the database and then, i want to store them in another table. To see the contents of the view we can give sp_helptext <viewname>, but the contents i want to store in a variable so that we can store them in the table.

  Thanks in advance.

  Thiru 

• noeld

  SSC Guru

  Points: 96590

  More actions

  January 24, 2005 at 8:42 am

  #538766

  Assuming that the view definition (text) is less that nvarchar(4000) in length you can use

  SELECT TABLE_NAME AS Name, VIEW_DEFINITON  AS DEFINITION

  FROM INFORMATION_SCHEMA.VIEWS

   

  HTH

   

  
  * Noel

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  January 24, 2005 at 12:47 pm

  #538805

  INSERT INTO ... EXEC sp_helptext would be another option.

  but the contents i want to store in a variable so that we can store them in the table.

  What do you mean here? Sounds to me like you're trying to reinvent the wheel.

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]