how to query a stored procedure

Hi,
A very simple question:
How can I query a stored procedure..?
For example:
SELECT * FROM sp_tables WHERE table_type = 'VIEW'
Thanks!!IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
IF OBJECT_ID('tempdb..#pr_tmp') IS NOT NULL DROP PROCEDURE #pr_tmp
GO
CREATE PROCEDURE #pr_tmp AS
SELECT TABLE_SCHEMA , TABLE_NAME , TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY( OBJECT_ID( QUOTENAME( TABLE_SCHEMA ) + '.' +
QUOTENAME( TABLE_NAME ) ) , 'IsMSShipped' ) = 0
GO
CREATE TABLE #tmp ( TABLE_SCHEMA SYSNAME , TABLE_NAME SYSNAME , TABLE_TYPE
SYSNAME )
INSERT INTO #tmp
EXEC #pr_tmp
SELECT * FROM #tmp WHERE TABLE_TYPE = 'VIEW'
Alternatively, for your gvien example you can do...
SELECT TABLE_SCHEMA , TABLE_NAME , TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY( OBJECT_ID( QUOTENAME( TABLE_SCHEMA ) + '.' +
QUOTENAME( TABLE_NAME ) ) ,
AND TABLE_TYPE = 'VIEW'
"Jan" <Jan@.discussions.microsoft.com> wrote in message
news:394827CE-96E3-4D57-BFF1-F0F91844370E@.microsoft.com...
> Hi,
> A very simple question:
> How can I query a stored procedure..?
> For example:
> SELECT * FROM sp_tables WHERE table_type = 'VIEW'
> Thanks!!|||Alternatively, for your gvien example you can do...
SELECT TABLE_SCHEMA , TABLE_NAME , TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY( OBJECT_ID( QUOTENAME( TABLE_SCHEMA ) + '.' +
QUOTENAM( TABLE_NAME ) ) , 'IsMSShipped' ) = 0 AND TABLE_TYPE = 'VIEW'
Gotta hate copy/paste errors ;)

> "Jan" <Jan@.discussions.microsoft.com> wrote in message
> news:394827CE-96E3-4D57-BFF1-F0F91844370E@.microsoft.com...
>|||Rebecca,
Thanks for your quick post!
But the query was just an example!
What I want to know is the technique how to do this..
"Rebecca York" wrote:

> Alternatively, for your gvien example you can do...
> SELECT TABLE_SCHEMA , TABLE_NAME , TABLE_TYPE FROM INFORMATION_SCHEMA.TABL
ES
> WHERE OBJECTPROPERTY( OBJECT_ID( QUOTENAME( TABLE_SCHEMA ) + '.' +
> QUOTENAM( TABLE_NAME ) ) , 'IsMSShipped' ) = 0 AND TABLE_TYPE = 'VIEW'
> Gotta hate copy/paste errors ;)
>
>
>|||If you look at my original post:-
CREATE TABLE #tmp ( TABLE_SCHEMA SYSNAME , TABLE_NAME SYSNAME , TABLE_TYPE
SYSNAME )
INSERT INTO #tmp
EXEC #pr_tmp
SELECT * FROM #tmp WHERE TABLE_TYPE = 'VIEW'
You need to create a table with the schema of the proc output, then use
INSERT INTO <table> EXEC <sp>
then filter the results from #tmp.
Alternatively, you can use OPENQUERY but this is a pain as the parameters of
openquery can't be a variable.
See the thread "t-sql question" started by ALe "2005-11-14 09-51"
"Jan" <Jan@.discussions.microsoft.com> wrote in message
news:E2E3AF56-A65B-4476-B503-AF7852A73429@.microsoft.com...
> Rebecca,
> Thanks for your quick post!
> But the query was just an example!
> What I want to know is the technique how to do this..
> "Rebecca York" wrote:
>
INFORMATION_SCHEMA.TABLES