How to get the sql statement of a view? (by running a query or from ado.net)

[John Sheppard]

Hello there,

I was wondering if anyone knew how to run a query that supplies the sql
statement behind a view? or is there a way to do that with ado.net?

Thank you kindly for anyones ideas
John Sheppard

[Philipp Post]

Re: How to get the sql statement of a view? (by running a query orfrom ado.net)

John,

if you are using C# you might want to look at this artice:

http://weblogs.sqlteam.com/billg/archive/2005/12/24/8613.aspx

All source code is supplied for this solution. It supports scripting
out all database objects on SQL Server, what also can be done with the
SQL Server Management Studio or the Database Publishing Wizard from
Microsoft.

Brgds

Philipp Post

[Tom van Stiphout]

Re: How to get the sql statement of a view? (by running a query or from ado.net)

A quick-n-dirty way is to execute sp_helptext on that view.

-Tom.

[John Sheppard]

Re: How to get the sql statement of a view? (by running a query or from ado.net)


"Tom van Stiphout" <no.spam.tom774 4@cox.netwrote in message
news:ksle041h2e dhc7bgfquv5696a dvi785ou7@4ax.c om...

>A quick-n-dirty way is to execute sp_helptext on that view.
>
-Tom.

Ahh close! Thanks Tom...

This works for tables but not for views...:( Google seems to think it works
on views tho....is that correct or do I have something weird happening?

this;
USE BMS;

GO

sp_helptext 'bms.dbo.myView ', myExpression

GO

Outputs the following;

Msg 15218, Level 16, State 1, Procedure sp_helptext, Line 64
Object 'bms.dbo.myView ' is not a table.


Thank you
John Sheppard

[John Sheppard]

Re: How to get the sql statement of a view? (by running a query or from ado.net)


"Philipp Post" <Post.Philipp@g ooglemail.comwr ote in message
news:d4abd6b4-af00-455d-ae1d-7f2978416cac@a1 g2000hsb.google groups.com...

John,
>
if you are using C# you might want to look at this artice:
>

http://weblogs.sqlteam.com/billg/archive/2005/12/24/8613.aspx

>
All source code is supplied for this solution. It supports scripting
out all database objects on SQL Server, what also can be done with the
SQL Server Management Studio or the Database Publishing Wizard from
Microsoft.
>
Brgds
>
Philipp Post

Thanks Philipp,

I like Toms solution best, well if i can get it to work with views...but I
have had a look through this and it looks like it might be useful...

Thank you
John Sheppard

[Tom van Stiphout]

Re: How to get the sql statement of a view? (by running a query or from ado.net)

On Fri, 18 Apr 2008 12:24:17 +1000, "John Sheppard" <spam@nospam.co m>
wrote:

Worked for me on a SQLServer 2005 database.
-Tom.

>
>"Tom van Stiphout" <no.spam.tom774 4@cox.netwrote in message
>news:ksle041h2 edhc7bgfquv5696 advi785ou7@4ax. com...

>>A quick-n-dirty way is to execute sp_helptext on that view.
>>
>-Tom.

>
>Ahh close! Thanks Tom...
>
>This works for tables but not for views...:( Google seems to think it works
>on views tho....is that correct or do I have something weird happening?
>
>this;
>USE BMS;
>
>GO
>
>sp_helptext 'bms.dbo.myView ', myExpression
>
>GO
>
>Outputs the following;
>
>Msg 15218, Level 16, State 1, Procedure sp_helptext, Line 64
>Object 'bms.dbo.myView ' is not a table.
>
>
>Thank you
>John Sheppard
>

[John Sheppard]

Re: How to get the sql statement of a view? (by running a query or from ado.net)

Weird!

Im using SQL Server 2005 Developer Edition...perha ps I have some kind of
security setting set that prevents this...:(

I'll keep mucking around...and report back with my findings..

Thanks Tom
John

"Tom van Stiphout" <no.spam.tom774 4@cox.netwrote in message
news:3j3g04hh6p 5a7a2bkj9k274pa qktrbvo49@4ax.c om...

On Fri, 18 Apr 2008 12:24:17 +1000, "John Sheppard" <spam@nospam.co m>
wrote:
>
Worked for me on a SQLServer 2005 database.
-Tom.
>
>

>>
>>"Tom van Stiphout" <no.spam.tom774 4@cox.netwrote in message
>>news:ksle041h 2edhc7bgfquv569 6advi785ou7@4ax .com...

>>>A quick-n-dirty way is to execute sp_helptext on that view.
>>>
>>-Tom.

>>
>>Ahh close! Thanks Tom...
>>
>>This works for tables but not for views...:( Google seems to think it
>>works
>>on views tho....is that correct or do I have something weird happening?
>>
>>this;
>>USE BMS;
>>
>>GO
>>
>>sp_helptext 'bms.dbo.myView ', myExpression
>>
>>GO
>>
>>Outputs the following;
>>
>>Msg 15218, Level 16, State 1, Procedure sp_helptext, Line 64
>>Object 'bms.dbo.myView ' is not a table.
>>
>>
>>Thank you
>>John Sheppard
>>

[Tom van Stiphout]

Re: How to get the sql statement of a view? (by running a query or from ado.net)

On Fri, 18 Apr 2008 16:07:46 +1000, "John Sheppard" <spam@nospam.co m>
wrote:

(AdventureWorks is a popular sample database)

use AdventureWorks
go
exec sp_helptext 'HumanResources .vEmployee'
go

==>

CREATE VIEW [HumanResources].[vEmployee]
AS
SELECT
e.[EmployeeID]
,c.[Title]
,c.[FirstName]
,c.[MiddleName]
,c.[LastName]
,c.[Suffix]
,e.[Title] AS [JobTitle]
,c.[Phone]
,c.[EmailAddress]
,c.[EmailPromotion]
,a.[AddressLine1]
,a.[AddressLine2]
,a.[City]
,sp.[Name] AS [StateProvinceNa me]
,a.[PostalCode]
,cr.[Name] AS [CountryRegionNa me]
,c.[AdditionalConta ctInfo]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]
INNER JOIN [HumanResources].[EmployeeAddress] ea
ON e.[EmployeeID] = ea.[EmployeeID]
INNER JOIN [Person].[Address] a
ON ea.[AddressID] = a.[AddressID]
INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] = a.[StateProvinceID]
INNER JOIN [Person].[CountryRegion] cr
ON cr.[CountryRegionCo de] = sp.[CountryRegionCo de];

>Weird!
>
>Im using SQL Server 2005 Developer Edition...perha ps I have some kind of
>security setting set that prevents this...:(
>
>I'll keep mucking around...and report back with my findings..
>
>Thanks Tom
>John
>
>"Tom van Stiphout" <no.spam.tom774 4@cox.netwrote in message
>news:3j3g04hh6 p5a7a2bkj9k274p aqktrbvo49@4ax. com...

>On Fri, 18 Apr 2008 12:24:17 +1000, "John Sheppard" <spam@nospam.co m>
>wrote:
>>
>Worked for me on a SQLServer 2005 database.
>-Tom.
>>
>>

>>>
>>>"Tom van Stiphout" <no.spam.tom774 4@cox.netwrote in message
>>>news:ksle041 h2edhc7bgfquv56 96advi785ou7@4a x.com...
>>>>A quick-n-dirty way is to execute sp_helptext on that view.
>>>>
>>>-Tom.
>>>
>>>Ahh close! Thanks Tom...
>>>
>>>This works for tables but not for views...:( Google seems to think it
>>>works
>>>on views tho....is that correct or do I have something weird happening?
>>>
>>>this;
>>>USE BMS;
>>>
>>>GO
>>>
>>>sp_helptex t 'bms.dbo.myView ', myExpression
>>>
>>>GO
>>>
>>>Outputs the following;
>>>
>>>Msg 15218, Level 16, State 1, Procedure sp_helptext, Line 64
>>>Object 'bms.dbo.myView ' is not a table.
>>>
>>>
>>>Thank you
>>>John Sheppard
>>>

>

[John Sheppard]

Re: How to get the sql statement of a view? (by running a query or from ado.net)

ahh ic!

Thanks Tom, I have it working now in SSMS...your sql statement works...

It doesnt seem to be working through ADO however....

Thanks heaps Tom, much appreciate your help
John

"Tom van Stiphout" <no.spam.tom774 4@cox.netwrote in message
news:ut8h04tttv 9stqc260j2q2te4 medfp574u@4ax.c om...

On Fri, 18 Apr 2008 16:07:46 +1000, "John Sheppard" <spam@nospam.co m>
wrote:
>
(AdventureWorks is a popular sample database)
>
use AdventureWorks
go
exec sp_helptext 'HumanResources .vEmployee'
go
>
==>
>
CREATE VIEW [HumanResources].[vEmployee]
AS
SELECT
e.[EmployeeID]
,c.[Title]
,c.[FirstName]
,c.[MiddleName]
,c.[LastName]
,c.[Suffix]
,e.[Title] AS [JobTitle]
,c.[Phone]
,c.[EmailAddress]
,c.[EmailPromotion]
,a.[AddressLine1]
,a.[AddressLine2]
,a.[City]
,sp.[Name] AS [StateProvinceNa me]
,a.[PostalCode]
,cr.[Name] AS [CountryRegionNa me]
,c.[AdditionalConta ctInfo]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]
INNER JOIN [HumanResources].[EmployeeAddress] ea
ON e.[EmployeeID] = ea.[EmployeeID]
INNER JOIN [Person].[Address] a
ON ea.[AddressID] = a.[AddressID]
INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] = a.[StateProvinceID]
INNER JOIN [Person].[CountryRegion] cr
ON cr.[CountryRegionCo de] = sp.[CountryRegionCo de];
>
>
>
>

>>Weird!
>>
>>Im using SQL Server 2005 Developer Edition...perha ps I have some kind of
>>security setting set that prevents this...:(
>>
>>I'll keep mucking around...and report back with my findings..
>>
>>Thanks Tom
>>John
>>
>>"Tom van Stiphout" <no.spam.tom774 4@cox.netwrote in message
>>news:3j3g04hh 6p5a7a2bkj9k274 paqktrbvo49@4ax .com...

>>On Fri, 18 Apr 2008 12:24:17 +1000, "John Sheppard" <spam@nospam.co m>
>>wrote:
>>>
>>Worked for me on a SQLServer 2005 database.
>>-Tom.
>>>
>>>
>>>>
>>>>"Tom van Stiphout" <no.spam.tom774 4@cox.netwrote in message
>>>>news:ksle04 1h2edhc7bgfquv5 696advi785ou7@4 ax.com...
>>>>>A quick-n-dirty way is to execute sp_helptext on that view.
>>>>>
>>>>-Tom.
>>>>
>>>>Ahh close! Thanks Tom...
>>>>
>>>>This works for tables but not for views...:( Google seems to think it
>>>>works
>>>>on views tho....is that correct or do I have something weird happening?
>>>>
>>>>this;
>>>>USE BMS;
>>>>
>>>>GO
>>>>
>>>>sp_helpte xt 'bms.dbo.myView ', myExpression
>>>>
>>>>GO
>>>>
>>>>Outputs the following;
>>>>
>>>>Msg 15218, Level 16, State 1, Procedure sp_helptext, Line 64
>>>>Object 'bms.dbo.myView ' is not a table.
>>>>
>>>>
>>>>Thank you
>>>>John Sheppard
>>>>

>>

[John Sheppard]

Re: How to get the sql statement of a view? (by running a query or from ado.net)

Ic the problem I have,

I was trying to use the following on a view....which.. .duh! of course its
not working because a 'computed column' does not exist in view...
exec sp_helptext 'myView', myExpression

Thanks Tom
John

"Tom van Stiphout" <no.spam.tom774 4@cox.netwrote in message
news:ut8h04tttv 9stqc260j2q2te4 medfp574u@4ax.c om...

On Fri, 18 Apr 2008 16:07:46 +1000, "John Sheppard" <spam@nospam.co m>
wrote:
>
(AdventureWorks is a popular sample database)
>
use AdventureWorks
go
exec sp_helptext 'HumanResources .vEmployee'
go
>
==>
>
CREATE VIEW [HumanResources].[vEmployee]
AS
SELECT
e.[EmployeeID]
,c.[Title]
,c.[FirstName]
,c.[MiddleName]
,c.[LastName]
,c.[Suffix]
,e.[Title] AS [JobTitle]
,c.[Phone]
,c.[EmailAddress]
,c.[EmailPromotion]
,a.[AddressLine1]
,a.[AddressLine2]
,a.[City]
,sp.[Name] AS [StateProvinceNa me]
,a.[PostalCode]
,cr.[Name] AS [CountryRegionNa me]
,c.[AdditionalConta ctInfo]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]
INNER JOIN [HumanResources].[EmployeeAddress] ea
ON e.[EmployeeID] = ea.[EmployeeID]
INNER JOIN [Person].[Address] a
ON ea.[AddressID] = a.[AddressID]
INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] = a.[StateProvinceID]
INNER JOIN [Person].[CountryRegion] cr
ON cr.[CountryRegionCo de] = sp.[CountryRegionCo de];
>
>
>
>

>>Weird!
>>
>>Im using SQL Server 2005 Developer Edition...perha ps I have some kind of
>>security setting set that prevents this...:(
>>
>>I'll keep mucking around...and report back with my findings..
>>
>>Thanks Tom
>>John
>>
>>"Tom van Stiphout" <no.spam.tom774 4@cox.netwrote in message
>>news:3j3g04hh 6p5a7a2bkj9k274 paqktrbvo49@4ax .com...

>>On Fri, 18 Apr 2008 12:24:17 +1000, "John Sheppard" <spam@nospam.co m>
>>wrote:
>>>
>>Worked for me on a SQLServer 2005 database.
>>-Tom.
>>>
>>>
>>>>
>>>>"Tom van Stiphout" <no.spam.tom774 4@cox.netwrote in message
>>>>news:ksle04 1h2edhc7bgfquv5 696advi785ou7@4 ax.com...
>>>>>A quick-n-dirty way is to execute sp_helptext on that view.
>>>>>
>>>>-Tom.
>>>>
>>>>Ahh close! Thanks Tom...
>>>>
>>>>This works for tables but not for views...:( Google seems to think it
>>>>works
>>>>on views tho....is that correct or do I have something weird happening?
>>>>
>>>>this;
>>>>USE BMS;
>>>>
>>>>GO
>>>>
>>>>sp_helpte xt 'bms.dbo.myView ', myExpression
>>>>
>>>>GO
>>>>
>>>>Outputs the following;
>>>>
>>>>Msg 15218, Level 16, State 1, Procedure sp_helptext, Line 64
>>>>Object 'bms.dbo.myView ' is not a table.
>>>>
>>>>
>>>>Thank you
>>>>John Sheppard
>>>>

>>