SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stored Procedure Metadata


Stored Procedure Metadata

Author
Message
richard.smith 47066
richard.smith 47066
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 21
I want to write some ADO code automatically from the definition of my (many) stored procedures.
This depends on being able to query the metadata tables so that I can generate the column definitions returned from the stored procedure.

So far I have managed the following:

select col_name(dep.depid, dep.depnumber) as ColName,
col_length(oj.[name], col_name(dep.depid, dep.depnumber)) as ColLength,
max(dep.depnumber) as MaxDepNo
from sysobjects oj, sysobjects depoj, sysdepends dep
where depoj.name = 'usp_rs_read_ll'
and dep.depid = oj.id
and dep.id = depoj.id
group by col_name(dep.depid, dep.depnumber), col_length(oj.[name],
col_name(dep.depid, dep.depnumber))
order by max(dep.depnumber)

This gets me 20 columns, with their names and lengths, but there should only be 19. The SP is as follows:

select LL.CustomerID, isnull(LL.InvoiceID,0) as InvoiceID, LL.SpecialDelivery, LL.PrintedFlag,
LL.LaundryListDate, LL.DeliveryPattern, LL.Route, isnull(LL.SackCount,0) as SackCount,
isnull(LL.HottCount,0) as HottCount, LL.HeavySoilCharge, LL.MinFixIndicator, LL.InvoiceType,
C.[Name], C.SaturdayRoute, X.ExtraSacks, X.DeliveryLocation, X.ShowTLSizes, X.PackingStation,
isnull(LL.ExtraSacks,0) as LLExtraSacks
from LaundryList LL
inner join Customer C on C.CustomerID = LL.CustomerID
inner join CustomerExtra X on X.CustomerID = LL.CustomerID
where LL.LaundryListID = @llist

The extra field returned from the metadata is LaundryListID which is from the WHERE clause.
I can't find anything that filters this out.
Also, I can't get it in the same sequence as the SP. There doesn't seem to be anything in the metadata tables to sequence it by.

All suggestions gratefully accepted.

Richard Smith
Matt Whitfield
Matt Whitfield
SSC Eights!
SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)

Group: General Forum Members
Points: 853 Visits: 719
Sorry to be the bearer of bad news, but you can't do that easily at all. Not generically for any stored procedure, anyhow.

SysDepends / sys.sql_dependencies / sys.sql_expression_dependencies all just show object usage between objects. They show what objects are used, not how they are used.

One possible route may be for you to create views, and select from the views in your SPs, that would be an OK route for the example you have posted, but might not apply to other SPs that you wish to do the same with. If it does apply, then you can query the meta data of the view, and look in sys.objects and sys.columns to find the relevant information, with the correct ordering.

If it does not apply - then you might have to actually execute each SP in turn, and use the meta data that comes with the result set(s) to generate the code. Which again isn't ideal.

Anyhow, hope that gives you some food for thought.

Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
richard.smith 47066
richard.smith 47066
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 21
Thanks for your reply, Matt.
It is disappointing, but I'm a bit mystified because I have a piece of 3rd party software that does exactly what I want. The only trouble is, it will not output in a form I can use, as I just want a text output that I can process.

The software is ADO.Net Express.

I'm not sure how to embed an image of the output in here.

But I can say that it will show the outputs of any my stored procedures, with data types, 100% accurate and quite effortlessly. It is fast enough for me to believe it isn't doing anything too fancy.
So it must be possible!
Matt Whitfield
Matt Whitfield
SSC Eights!
SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)

Group: General Forum Members
Points: 853 Visits: 719
I tried to download it and have a look, but the download link was broken :/

My guess is that it is executing the procedures in order to look at the result sets and get the meta data that way...

Try it on this proc:


CREATE PROCEDURE stproc_Test
AS
WAITFOR DELAY '00:00:20'
SELECT 'hello'



If it takes about 20 seconds to get the result sets, then it's executing them. If not, then it's probably doing something really quite complicated under the hood.

The other way you can find out what it is doing is to run a profiler trace, and see what it executes. Let me know what you find :-)

Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
richard.smith 47066
richard.smith 47066
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 21
Matt, thanks again for your thoughts.
I tried the ADO.Net Expess system with this stored procedure. It has no delay in reporting the output column so it is not executing the procedure.
Matt Whitfield
Matt Whitfield
SSC Eights!
SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)

Group: General Forum Members
Points: 853 Visits: 719
Very interesting...

Did you run a profiler trace? I'm intrigued now... :-)

Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
Matt Whitfield
Matt Whitfield
SSC Eights!
SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)

Group: General Forum Members
Points: 853 Visits: 719
Actually - having just thought about it - I am wondering if it executes it using SET FMTONLY ON.

If I use the following:


SET FMTONLY ON
GO
EXEC stproc_test



Then it immediately returns with the result set, in the correct shape. So I am guessing that's what you would want to do. Hope that helps. :-)

Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
richard.smith 47066
richard.smith 47066
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 21
Hi Matt,

OK I see what you mean. The only thing is that for every procedure it would have to pass its input parameters otherwise you just get an error. And if it happened to pass the wrong ones to a SP that updated .....

So call me a sceptic if you like, but I'm not entirely convinced it is executing the procedure.
Matt Whitfield
Matt Whitfield
SSC Eights!
SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)

Group: General Forum Members
Points: 853 Visits: 719
with SET FMTONLY, it doesn't do any data changes, just gives you the schema of the result. So passing in any old default value for the parameters will be fine.

Run a profiler trace - willing to bet that's what it does :-D

Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
Garadin
Garadin
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2705 Visits: 4107
Matt Whitfield (9/29/2009)
with SET FMTONLY, it doesn't do any data changes, just gives you the schema of the result. So passing in any old default value for the parameters will be fine.

Run a profiler trace - willing to bet that's what it does :-D


I answered a similar question a year or so ago with the concept of using SET FMTONLY ON. You are correct that it doesn't actually change any data and it seemed like an ideal solution.

The problem we kept running into is how you take that "empty" result set that you get with FMTONLY and do anything with it. That's the point I was stuck at with actually using this for anything. How do you turn that information into a physical list of columns that you can do something with?

Seth Phelabaum
Consistency is only a virtue if you're not a screwup. ;-)

Links: How to Post Sample Data :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search