Meta-Information about Stored-Procedures

Hi ,

I am looking for meta-information about the return recordset of a
stored-procedure. The procedure returns a resultset that contains columns of
more tables joined together. In all tables, I use, there is a
Record-Creation-Timestamp-Attribute. When joining two or more tables these
attribute-names appear in ther resultset but
i found no way to distinguish them.

I there a way to retrieve meta-information about the result-recordset of
such a stored-procedure?

here some details:

the tables
=======
CREATE TABLE [dbo].[Table1] (
[Table1ID] [int] IDENTITY (1, 1) NOT NULL ,
[FK_Tab2ID] [int] NULL ,
[CreatedAt] [datetime] NULL )

CREATE TABLE [dbo].[Table2] (
[Table2ID] [int] IDENTITY (1, 1) NOT NULL ,
[Description] [varchar] (35) NULL ,
[CreatedAt] [datetime] NULL)

the stored-procedure:
===============

CREATE PROCEDURE dbo.sp_Test_RetrieveData
@.ID int
AS
SET NOCOUNT ON

select * from table1 inner join table2 on (FK_Tab2ID = Table2ID)
where table1.ID = @.ID
GO

the resultset:
==========
Table1ID,FK_Tab2ID,CreatedAt,Table2ID,Description, CreatedAt
(the attribute CreatedAt appears twice.)

--= Posted via Newsfeeds.Com, Uncensored Usenet News =--
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
--== Over 100,000 Newsgroups - 19 Different Servers! =--KG wrote:
> CREATE PROCEDURE dbo.sp_Test_RetrieveData
> @.ID int
> AS
> SET NOCOUNT ON
> select * from table1 inner join table2 on (FK_Tab2ID = Table2ID)
> where table1.ID = @.ID
> GO
> the resultset:
> ==========
> Table1ID,FK_Tab2ID,CreatedAt,Table2ID,Description, CreatedAt
> (the attribute CreatedAt appears twice.)

Try this instead:

CREATE PROCEDURE dbo.sp_Test_RetrieveData
@.ID int
AS
SET NOCOUNT ON

select Table1ID, FK_Tab2ID, Table1.CreatedAt as Table1CreatedAt, Table2ID,
Description, Table2.CreatedAt as Table2CreatedAt
from table1 inner join table2 on (FK_Tab2ID = Table2ID)
where table1.ID = @.ID
GO

you will get resultset:

Table1ID, FK_Tab2ID, Table1CreatedAt, Table2ID, Description, Table2CreatedAt

--
Steve Troxell
Krell Software - Database Tools for MS SQL Server
http://www.krell-software.com|||KG (kg@.greenmail.ch) writes:
> I am looking for meta-information about the return recordset of a
> stored-procedure. The procedure returns a resultset that contains
> columns of more tables joined together. In all tables, I use, there is a
> Record-Creation-Timestamp-Attribute. When joining two or more tables
> these attribute-names appear in ther resultset but i found no way to
> distinguish them.
> I there a way to retrieve meta-information about the result-recordset of
> such a stored-procedure?

It would have helped if you have told in which environment you are working.
Are you using ADO?

In ADO, there are some properties on the Fields on object which may have
this information.

But it is kind of obscure programming to access this data. Better is to
use column aliases. Generally, SELECT * should not be used in production
code.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp