sp results into a variable

Question

Post reply

sp results into a variable

eagoss

Old Hand

Points: 307
More actions
September 8, 2003 at 2:35 pm

#82843

I'm attempting to write a T-SQL procedure that will take the results from a system stored procedure and use them to generate ddl statements - primarily primary keys. The easiest way to get the primary keys for a table is to use sp_pkeys. I want to return this result set into a cursor or some other type of data structure to loop through the results.
For example, I have TableA defined as
create table tableA (
col1char(5) not null,
col2int not null,
col3smallint not null,
col4char(80),
col5char(20),
constraint pk_tableA primary key nonclustered (col1, col2, col3));
If I do an sp_pkeys against this table, I get 3 rows returned.
How do I return those three rows into a cursor inside a SP? Is it even possible?
Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

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

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 1

Try something like this:

set nocount on

go

create table tableA (

col1char(5) not null,

col2int not null,

col3smallint not null,

col4char(80),

col5char(20),

constraint pk_tableA primary key nonclustered (col1, col2, col3))

go

create table sp_out (

TABLE_QUALIFIER varchar(128),

TABLE_OWNER varchar(128),

TABLE_NAME varchar(128),

COLUMN_NAME varchar(128),

KEY_SEQ int,

PK_NAME varchar(128))

insert into sp_out exec sp_pkeys tablea

select * from sp_out

drop table sp_out

drop table tablea

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

eagoss Old Hand Points: 307 More actions · Answer 2

I'd considered that, but I'd really rather not have to create another table or a temporary table to do this. Is there any other way?

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 3

Can use DMO to generate the script

Some interesting info in the Information Schema Views

Select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

Where TABLE_NAME='MyTable' And 

CONSTRAINT_NAME=

(Select CONSTRAINT_NAME From INFORMATION_SCHEMA.TABLE_CONSTRAINTS

Where TABLE_NAME='MyTable' And CONSTRAINT_TYPE='PRIMARY KEY')

eagoss Old Hand Points: 307 More actions · Answer 4

That works great! I used key_column_usage instead to pull in the ordinal position.

However.. Is there no way to do it using the stored procedures??

I'm just curious now..