List out SP's Columns into a Table

Question

List out SP's Columns into a Table

SQLIndia

Ten Centuries

Points: 1073
More actions
August 12, 2008 at 4:15 am

#93700

Hi All
I want to list out columns name of SP into a table. They can be dynamic.
Thank in advance
sonny
SqlIndia

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

sqlindia (8/12/2008)
Hi All
I want to list out columns name of SP into a table. They can be dynamic.
Thank in advance
sonny

Could you give an example of what you mean?

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 2

sqlindia (8/12/2008)
Hi All
I want to list out columns name of SP into a table. They can be dynamic.
Thank in advance
sonny

I'm pretty sure this can't be done reliably. Sure, it can be done, but if you have a stored procedure with the column name "Test" in it, how will you find that without also finding the "Test"column from other tables? Maybe using "dependencies" but still no guarantee if two tables appear in the same sproc and both have a column called "Test".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Abhijit More SSCertifiable Points: 6897 More actions · Answer 3

As per my understanding the below script will give you the desired result set.

DECLARE @SQLColumns VARCHAR(4000), @sql VARCHAR(8000)

SET @SQLColumns = 'AddressID, AddressLine1, AddressLine2, City'

SET @sql = 'SELECT '+ @SQLColumns +' FROM AdventureWorks.Person.Address'

--Print @sql

EXECUTE( @sql )

Abhijit - http://abhijitmore.wordpress.com

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 4

Heh... that's 3 different opinions as to what the OP actually wants...

Hey! SQLIndia! Would you mind clarifying what you want, please?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

arjun.tewari Ten Centuries Points: 1013 More actions · Answer 5

Hi,

The following script will give you the name of the SP, the name of the tables it references, and the name of the columns inside those tables used by the SP.

select routine_name, x.table_name, z.column_name

from information_schema.tables x

cross join information_schema.routines y join

information_schema.columns z on x.table_name = z.table_name

where routine_definition like '%' + x.table_name + '%' and

routine_definition like '%' + column_name + '%'

arjun.tewari Ten Centuries Points: 1013 More actions · Answer 6

arjun.tewari (8/20/2008)
Hi,
The following script will give you the name of the SP, the name of the tables it references, and the name of the columns inside those tables used by the SP.
select routine_name, x.table_name, z.column_name
from information_schema.tables x
cross join information_schema.routines y join
information_schema.columns z on x.table_name = z.table_name
where routine_definition like '%' + x.table_name + '%' and
routine_definition like '%' + column_name + '%'

Further, you can dump the list in to a table.

sgmunson SSC Guru Points: 110640 More actions · Answer 7

Isn't the OP looking for the result columns without an actual execution of the SP? Isn't there some kind of option that returns that information without executing the SQL code? Something about metadata, perhaps?

I could swear I've run across that somewhere, but I don't remember any of the details. The older I get, the worse this kind of problem becomes...:w00t:

EDIT: Now that I think about it, maybe it was something one could do from an ODBC connection?

Steve

(aka sgmunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 8

Is thi problem solved..?

I am in need of same functionality...Want list out records of an SP into temp table....columns will be dynamic as well.

pls help..

sgmunson SSC Guru Points: 110640 More actions · Answer 9

That's not quite the same as what the OP is looking for, at least based on my reading of it. What you appear to be looking for is the functional equivalent of the following non-working code:

EXEC spMY_PROCEDURE

INTO #temp_table

The only way I can think of to achieve that is to create a global temp table from within the SP or to use the OUTPUT clause, which you'll have to look up in BOL and see if that can be made to work.

nairsanthu1977 (11/18/2008)
Is thi problem solved..?
I am in need of same functionality...Want list out records of an SP into temp table....columns will be dynamic as well.
pls help..

As to the OP's original requirement, I just saw something the other day that reminded me of a setting that might help:

SET FMTONLY ON;

GO

This appears to be session-based, so you can turn it on, then SELECT something, and then turn it off. BOL indicates in it's sample code the usage of GO, so apparently, it's not limited to just the current "batch". I don't have time to test it against an SP right now, but I'd be interested in anyone's results, especially if the SP executes dynamic SQL. I would expect that if there are any parms for the SP, that they'll still need to be supplied. I'm also fairly certain that to get the column list into a table, you'd have to be issuing the query from within at least a VBScript, or VBA within an MS Office product, or any other scenario where you can use ADO functionality, such as any of the .NET languages.

Steve

(aka smunson)

:):):)

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

winash SSChampion Points: 11446 More actions · Answer 10

Using a loopback linked server with SET FMTONLY can help in getting the information...

Loopback linked server: http://geekswithblogs.net/urig/archive/2006/03/23/73132.aspx

I've attached some sample code - the original idea was something I found in some forum so credit for this idea goes to whoever came up with it first 😀