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
John,
if you are using C# you might want to look at this artice:
http://weblogs.sqlteam.com/billg/archive/2005/12/24/8613.asp x
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
A quick-n-dirty way is to execute sp_helptext on that view.
-Tom.
"Tom van Stiphout"
news:ksle041h2edhc7bgfquv5696advi785ou7@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
"Philipp Post"
news:d4abd6b4-af00-455d-ae1d-7f2978416cac@a1g2000hsb.googleg roups.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.asp x
>
> 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
On Fri, 18 Apr 2008 12:24:17 +1000, "John Sheppard"
wrote:
Worked for me on a SQLServer 2005 database.
-Tom.
>
>"Tom van Stiphout"
>news:ksle041h2edhc7bgfquv5696advi785ou7@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
>
Weird!
Im using SQL Server 2005 Developer Edition...perhaps 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"
news:3j3g04hh6p5a7a2bkj9k274paqktrbvo49@4ax.com...
> On Fri, 18 Apr 2008 12:24:17 +1000, "John Sheppard"
> wrote:
>
> Worked for me on a SQLServer 2005 database.
> -Tom.
>
>
>>
>>"Tom van Stiphout"
>>news:ksle041h2edhc7bgfquv5696advi785ou7@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
>>
On Fri, 18 Apr 2008 16:07:46 +1000, "John Sheppard"
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 [StateProvinceName]
,a.[PostalCode]
,cr.[Name] AS [CountryRegionName]
,c.[AdditionalContactInfo]
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.[CountryRegionCode] = sp.[CountryRegionCode];
>Weird!
>
>Im using SQL Server 2005 Developer Edition...perhaps 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"
>news:3j3g04hh6p5a7a2bkj9k274paqktrbvo49@4ax.com...
>> On Fri, 18 Apr 2008 12:24:17 +1000, "John Sheppard"
>> wrote:
>>
>> Worked for me on a SQLServer 2005 database.
>> -Tom.
>>
>>
>>>
>>>"Tom van Stiphout"
>>>news:ksle041h2edhc7bgfquv5696advi785ou7@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
>>>
>
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"
news:ut8h04tttv9stqc260j2q2te4medfp574u@4ax.com...
> On Fri, 18 Apr 2008 16:07:46 +1000, "John Sheppard"
> 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 [StateProvinceName]
> ,a.[PostalCode]
> ,cr.[Name] AS [CountryRegionName]
> ,c.[AdditionalContactInfo]
> 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.[CountryRegionCode] = sp.[CountryRegionCode];
>
>
>
>
>>Weird!
>>
>>Im using SQL Server 2005 Developer Edition...perhaps 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"
>>news:3j3g04hh6p5a7a2bkj9k274paqktrbvo49@4ax.com...
>>> On Fri, 18 Apr 2008 12:24:17 +1000, "John Sheppard"
>>> wrote:
>>>
>>> Worked for me on a SQLServer 2005 database.
>>> -Tom.
>>>
>>>
>>>>
>>>>"Tom van Stiphout"
>>>>news:ksle041h2edhc7bgfquv5696advi785ou7@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
>>>>
>>
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"
news:ut8h04tttv9stqc260j2q2te4medfp574u@4ax.com...
> On Fri, 18 Apr 2008 16:07:46 +1000, "John Sheppard"
> 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 [StateProvinceName]
> ,a.[PostalCode]
> ,cr.[Name] AS [CountryRegionName]
> ,c.[AdditionalContactInfo]
> 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.[CountryRegionCode] = sp.[CountryRegionCode];
>
>
>
>
>>Weird!
>>
>>Im using SQL Server 2005 Developer Edition...perhaps 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"
>>news:3j3g04hh6p5a7a2bkj9k274paqktrbvo49@4ax.com...
>>> On Fri, 18 Apr 2008 12:24:17 +1000, "John Sheppard"
>>> wrote:
>>>
>>> Worked for me on a SQLServer 2005 database.
>>> -Tom.
>>>
>>>
>>>>
>>>>"Tom van Stiphout"
>>>>news:ksle041h2edhc7bgfquv5696advi785ou7@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
>>>>
>>