Professional SQL Server™ 2005 Programming

9.5. Auditing: Displaying Existing Code

What do you do when you have a view, but you're not sure what it does? The first option should be easy at this point — just go into the Management Studio as if you're going to edit the view. Go to the Views sub-node, select the view you want to edit, right-click, and choose Modify View. You'll see the code behind the view complete with color-coding.

Unfortunately, we don't always have the option of having the Management Studio around to hold our hand through this stuff (we may be using a lighter-weight tool of some sort). The bright side is that we have two ways of getting at the actual view definition:

• sp_helptext

• The syscomments system table

Using sp_helptext is highly preferable, because when new releases come out, it will automatically be updated for changes to the system tables.

Let's run sp_helptext against one of the supplied views in the AdventureWorks database — vStateProvinceCountryRegion:

EXEC sp_helptext 'Person.vStateProvinceCountryRegion'

Note the quotes. This is because this stored proc expects only one argument, and the period is a delimiter of sorts — if you pass Person.vStateProvinceCountryRegion in without the quotes, it sees the period and isn't sure what to do with it and therefore errors out.

SQL Server obliges us with the code for the view:

Text -------------------------------------------------------------------------------- CREATE VIEW [Person].[vStateProvinceCountryRegion] WITH SCHEMABINDING AS SELECT sp.[StateProvinceID] ...