How to get a view table query (code) in SQL Server 2008 Management Studio

16

I have a view in SQL Server 2008 and would like to view it in Management Studio.

Example:

--is the underlying query for the view Example_1
select * 
from table_aView 

View name: Example_1

How to get the query of the corresponding view table (query used to create the view)?

Thanks in advance

This question is tagged with sql sql-server view

~ Asked on 2013-11-25 09:58:16

The Best Answer is


44

In Management Studio, open the Object Explorer.

  • Go to your database
  • There's a subnode Views
  • Find your view
  • Choose Script view as > Create To > New query window

and you're done!

enter image description here

If you want to retrieve the SQL statement that defines the view from T-SQL code, use this:

SELECT  
    m.definition    
FROM sys.views v
INNER JOIN sys.sql_modules m ON m.object_id = v.object_id
WHERE name = 'Example_1'

~ Answered on 2013-11-25 10:01:21


11

Use sp_helptext before the view_name. Example:

sp_helptext Example_1

Hence you will get the query:

CREATE VIEW dbo.Example_1
AS
SELECT       a, b, c
FROM         dbo.table_name JOIN blah blah blah
WHERE        blah blah blah

sp_helptext will give stored procedures.

~ Answered on 2017-09-20 18:40:40


Most Viewed Questions: