Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

Use of assignment to $[ is deprecated at /usr/local/sbin/apxs line 86. , wwwxxx vim, mysql closing table and opening table, 800c5000, setgid operation not permitted, pciehp: acpi_pciehprm on IBM, WWWXXX.DBF, 078274121, info0a ip, should prodicers of software_based services be held liable or not liable for economic injuries

Links

XODOX
Impressum

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

Posted on 2008-04-17 09:06:49 by Gabe Garza

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

Report this message

#2: Re: How to get the sql statement of a view? (by running a query or

Posted on 2008-04-17 11:51:17 by Philipp Post

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

Report this message

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

Posted on 2008-04-17 15:59:22 by Tom van Stiphout

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

-Tom.

Report this message

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

Posted on 2008-04-18 04:24:17 by Gabe Garza

"Tom van Stiphout" <no.spam.tom7744@cox.net> wrote in message
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

Report this message

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

Posted on 2008-04-18 04:26:38 by Gabe Garza

"Philipp Post" <Post.Philipp@googlemail.com> wrote in message
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

Report this message

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

Posted on 2008-04-18 04:59:14 by Tom van Stiphout

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

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


>
>"Tom van Stiphout" <no.spam.tom7744@cox.net> wrote in message
>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
>

Report this message

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

Posted on 2008-04-18 08:07:46 by Gabe Garza

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" <no.spam.tom7744@cox.net> wrote in message
news:3j3g04hh6p5a7a2bkj9k274paqktrbvo49@4ax.com...
> On Fri, 18 Apr 2008 12:24:17 +1000, "John Sheppard" <spam@nospam.com>
> wrote:
>
> Worked for me on a SQLServer 2005 database.
> -Tom.
>
>
>>
>>"Tom van Stiphout" <no.spam.tom7744@cox.net> wrote in message
>>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
>>

Report this message

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

Posted on 2008-04-18 15:36:40 by Tom van Stiphout

On Fri, 18 Apr 2008 16:07:46 +1000, "John Sheppard" <spam@nospam.com>
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" <no.spam.tom7744@cox.net> wrote in message
>news:3j3g04hh6p5a7a2bkj9k274paqktrbvo49@4ax.com...
>> On Fri, 18 Apr 2008 12:24:17 +1000, "John Sheppard" <spam@nospam.com>
>> wrote:
>>
>> Worked for me on a SQLServer 2005 database.
>> -Tom.
>>
>>
>>>
>>>"Tom van Stiphout" <no.spam.tom7744@cox.net> wrote in message
>>>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
>>>
>

Report this message

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

Posted on 2008-04-21 03:21:30 by Gabe Garza

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.tom7744@cox.net> wrote in message
news:ut8h04tttv9stqc260j2q2te4medfp574u@4ax.com...
> On Fri, 18 Apr 2008 16:07:46 +1000, "John Sheppard" <spam@nospam.com>
> 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" <no.spam.tom7744@cox.net> wrote in message
>>news:3j3g04hh6p5a7a2bkj9k274paqktrbvo49@4ax.com...
>>> On Fri, 18 Apr 2008 12:24:17 +1000, "John Sheppard" <spam@nospam.com>
>>> wrote:
>>>
>>> Worked for me on a SQLServer 2005 database.
>>> -Tom.
>>>
>>>
>>>>
>>>>"Tom van Stiphout" <no.spam.tom7744@cox.net> wrote in message
>>>>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
>>>>
>>

Report this message

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

Posted on 2008-04-21 04:24:49 by Gabe Garza

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.tom7744@cox.net> wrote in message
news:ut8h04tttv9stqc260j2q2te4medfp574u@4ax.com...
> On Fri, 18 Apr 2008 16:07:46 +1000, "John Sheppard" <spam@nospam.com>
> 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" <no.spam.tom7744@cox.net> wrote in message
>>news:3j3g04hh6p5a7a2bkj9k274paqktrbvo49@4ax.com...
>>> On Fri, 18 Apr 2008 12:24:17 +1000, "John Sheppard" <spam@nospam.com>
>>> wrote:
>>>
>>> Worked for me on a SQLServer 2005 database.
>>> -Tom.
>>>
>>>
>>>>
>>>>"Tom van Stiphout" <no.spam.tom7744@cox.net> wrote in message
>>>>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
>>>>
>>

Report this message