News

Copyright © 2008-2018 Paula DiTallo

Tag Cloud



How do I read the SQL for a View in T-SQL?

 The easiest way in MS SQL 2005 and up is to use the SQL Management Studio,  go to Views, highlight the name of the view you are interested in, right click, select Script View  as/Create to/Clipboard.

 

open up notepad or your prefered editor and paste the contents of your clipboard. The SQL used to create the view should show up in your editor.

If this doesn't work, or if you are working with older  versions of SQL Server, look at the system objects as follows:

use [YouDatabseInstanceName]
go

select * from sys.objects
where type = 'V'
and name = 'YourViewName'

-- capture the object id and schema_id to examine later
-- object_id = 436921992
-- schema_id = 1

-- this should show you whether or not there is anything set in the definition coloumn

select * from sys.sql_modules
where object_id = 436921992


-- use to examine which schema name the view is in


select * from sys.schemas
where schema_id = 1

 

-- Use this to capture all the views by the schema name


select o.name
      ,o.object_id
  into #views
  from sys.objects o
  join sys.schemas s
    on s.schema_id = o.schema_id
 where o.type in  ('V')
   and s.name = 'dbo'

select * from #views

-- use this to examine the definition of all views

select definition + char(10) + 'go' + char(10)
  from sys.sql_modules c
  join sys.objects o
    on c.object_id = o.object_id
  join #views o2
    on o.object_id = o2.object_id

Wednesday, May 4, 2011 12:41 PM

Feedback

# re: How do I read the SQL for a View in T-SQL?

BTW, if the value under the Definition column shows up as NULL, you are likely NOT to have the required permissions set up to view the text of the view. 5/18/2011 12:50 PM | plditallo

Post A Comment
Title:
Name:
Email:
Comment:
Verification: