Embarrassingly simple question about using stored proceedures

The stored procedure sp_columns returns several fields but i want just the
COLUMN_NAME
if the stored procedure was a table I would write the query
Select COLUMN_NAME from sp_columns
Whats the simplest way of doing this for a stored proceedure
many thanks
David HEither re-write the procedure or use below technique:
CREATE TABLE #tmp...
INSERT #tmp
EXEC sp_columns
SELECT ... FROM #tmp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"David Hills" <dhills@.pcfe.ac.uk> wrote in message
news:BEEA10DA-869E-4A9B-B884-56B6070B6725@.microsoft.com...
> The stored procedure sp_columns returns several fields but i want just the
COLUMN_NAME
> if the stored procedure was a table I would write the query
> Select COLUMN_NAME from sp_columns
> Whats the simplest way of doing this for a stored proceedure
> many thanks
> David H
>|||An even simpler approach would be to ditch sp_columns and use the
INFORMATION_SCHEMA views instead. For example, the get the columns of the
Authors table in the pubs database use:
USE pubs
GO
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_CATALOG = 'pubs'
AND TABLE_NAME = 'authors'
or
SELECT COLUMN_NAME
FROM pubs.INFORMATION_SCHEMA.Columns
WHERE TABLE_CATALOG = 'pubs'
AND TABLE_NAME = 'authors'
You can get more information INFORMATION_SCHEMA views in BOL:
http://msdn.microsoft.com/library/d...br />
4pbn.asp
Cheers,
Stefan
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OmygI4YDEHA.628@.TK2MSFTNGP10.phx.gbl...
> Either re-write the procedure or use below technique:
> CREATE TABLE #tmp...
> INSERT #tmp
> EXEC sp_columns
> SELECT ... FROM #tmp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "David Hills" <dhills@.pcfe.ac.uk> wrote in message
> news:BEEA10DA-869E-4A9B-B884-56B6070B6725@.microsoft.com...
the
> COLUMN_NAME
>|||That's a good point, Stefan!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Stefan Delmarco [MSFT]" <StefanDe@.online.microsoft.com> wrote in messag
e
news:eAuYCTZDEHA.3240@.TK2MSFTNGP10.phx.gbl...
> An even simpler approach would be to ditch sp_columns and use the
> INFORMATION_SCHEMA views instead. For example, the get the columns of the
> Authors table in the pubs database use:
> USE pubs
> GO
> SELECT COLUMN_NAME
> FROM INFORMATION_SCHEMA.Columns
> WHERE TABLE_CATALOG = 'pubs'
> AND TABLE_NAME = 'authors'
> or
> SELECT COLUMN_NAME
> FROM pubs.INFORMATION_SCHEMA.Columns
> WHERE TABLE_CATALOG = 'pubs'
> AND TABLE_NAME = 'authors'
> You can get more information INFORMATION_SCHEMA views in BOL:
>
http://msdn.microsoft.com/library/d..._ia-iz_4pbn.asp[
color=darkred]
> Cheers,
> Stefan
> --
> This posting is provided "AS IS" with no warranties, and confers no[/color]
rights.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:OmygI4YDEHA.628@.TK2MSFTNGP10.phx.gbl...
> the
>|||Many thanks, that's just what I needed to know to do this
SELECT TABLE_NAME,COLUMN_NAME
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_CATALOG = 'sipr'
AND COLUMN_NAME LIKE '%rlg%'
TABLE_NAME COLUMN_NAME
--
d21_eed44 rlg_code
D04_RLG rlg_code
D04_RLG rlg_name
D04_RLG rlg_snam
SRS_RLG rlg_code
SRS_RLG rlg_name
SRS_RLG rlg_snam
D04_STU stu_rlgc
D04_CONTCT stu_rlg
D51_STU_CTS stu_rlgc
D51_STU_V26 stu_rlgc
INS_STU stu_rlgc