CODEWITHSUNDEEP
sqlsql-serverview
Sudhan
Sudhan

Reputation: 397

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

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)?

Upvotes: 26

Views: 200261

Answers (7)

sachin varma
sachin varma

Reputation: 1

SELECT definition, uses_ansi_nulls, uses_quoted_identifier, is_schema_bound FROM sys.sql_modules WHERE object_id = object_id( 'your view name' );

Upvotes: 0

Pat
Pat

Reputation: 47

To get the information of a view, you use the system catalog sys.sql_module and the OBJECT_ID() function:

SELECT
    definition,
    uses_ansi_nulls,
    uses_quoted_identifier,
    is_schema_bound
FROM
    sys.sql_modules
WHERE
    object_id
    = object_id(
        'view_name'
    );

Another way to get the view information is to use the OBJECT_DEFINITION() and OBJECT_ID() functions as follows:

SELECT 
    OBJECT_DEFINITION(
        OBJECT_ID(
            'view_name'
        )
    ) view_info;

Upvotes: 1

Max Asinger
Max Asinger

Reputation: 21

Additionally, if you have restricted access to the database (IE: Can't use "Script Function as > CREATE To"), there is another option to get this query.

Find your View > right click > "Design".

This will give you the query you are looking for.

Upvotes: 2

Pradeep Kumar
Pradeep Kumar

Reputation: 4141

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.

Upvotes: 14

marc_s
marc_s

Reputation: 754508

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'

Upvotes: 62

Brett Schneider
Brett Schneider

Reputation: 4103

right-click the view in the object-explorer, select "script view as...", then "create to" and then "new query editor window"

Upvotes: 0

RobertKing
RobertKing

Reputation: 1921

if i understood you can do the following

Right Click on View Name in SQL Server Management Studio -> Script View As ->CREATE To ->New Query Window

Upvotes: 1

Related Questions