filter columns from result set of a system sp

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

filter columns from result set of a system sp

Author

Topic

AShehzad
Starting Member

8 Posts

Posted - 2008-11-11 : 04:56:21

is it possible to apply filter on result set produced by a system sp? For example i have used a system stored procedure sp_fkeys tablename
It produced several columns in result, and i am interested in just one or a couple of columns. Is there any way to get my required column out of that result set?
Regards

Atif Shehzad
DBA Pakistan Revenue Automation Limited

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-11-11 : 04:59:28

not really. you'll have to insert the data into a temp table and then select from there.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-11 : 05:12:20

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-11-11 : 05:14:09

acctually the openrowset doesn't work for all system sprocs. sp_who is one of those if i remember correctly.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-11 : 05:20:07

quote:
Originally posted by spirit1

acctually the openrowset doesn't work for all system sprocs. sp_who is one of those if i remember correctly.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

It works for me
May be not supported in 2000?

Madhivanan

Failing to plan is Planning to fail

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-11-11 : 05:31:13

CREATE TABLE #TMPtable (
PKTABLE_QUALIFIER VARCHAR(100) ,
PKTABLE_OWNER VARCHAR(100),
PKTABLE_NAME VARCHAR(100),
PKCOLUMN_NAME VARCHAR(100),
FKTABLE_QUALIFIER VARCHAR(100),
FKTABLE_OWNER VARCHAR(100),
FKTABLE_NAME VARCHAR(100),
FKCOLUMN_NAME VARCHAR(100),
KEY_SEQ VARCHAR(100),
UPDATE_RULE VARCHAR(100),
DELETE_RULE VARCHAR(100),
FK_NAME VARCHAR(100),
PK_NAME VARCHAR(100),
DEFERRABILITY VARCHAR(100) )

INSERT INTO #TMPtable
EXEC sp_fkeys'transactions.intiqal'

SELECT PKTABLE_NAME,FK_NAME,PK_NAME FROM #TMPtable

DROP TABLE #TMPtable

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-11-11 : 05:36:14

quote:
Originally posted by madhivanan

quote:
Originally posted by spirit1

acctually the openrowset doesn't work for all system sprocs. sp_who is one of those if i remember correctly.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

It works for me
May be not supported in 2000?

Madhivanan

Failing to plan is Planning to fail

ahh it was sp_who2

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-11 : 05:58:21

quote:
Originally posted by spirit1

quote:
Originally posted by madhivanan

quote:
Originally posted by spirit1

acctually the openrowset doesn't work for all system sprocs. sp_who is one of those if i remember correctly.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

It works for me
May be not supported in 2000?

Madhivanan

Failing to plan is Planning to fail

ahh it was sp_who2

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

sp_who2 returns two columns with same name (SPID) and thats why it doesnt work

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-11-11 : 06:05:53

yeah... kind of figured that now. thanx.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources