Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 7,2000 » T-SQL » Stored Procedure Metadata
Add to briefcase
30 posts, Page 1 of 3
123»»»

Stored Procedure Metadata Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
richard.smith 47066
Posted Friday, September 25, 2009 10:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 08, 2013 9:28 AM
Points: 7, 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
Post #793918
Matt Whitfield
Posted Sunday, September 27, 2009 4:36 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, February 15, 2013 7:29 AM
Points: 509, Visits: 718
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
Post #794264
richard.smith 47066
Posted Tuesday, September 29, 2009 3:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 08, 2013 9:28 AM
Points: 7, 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!
Post #794984
Matt Whitfield
Posted Tuesday, September 29, 2009 3:59 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, February 15, 2013 7:29 AM
Points: 509, Visits: 718
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
Post #794993
richard.smith 47066
Posted Tuesday, September 29, 2009 5:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 08, 2013 9:28 AM
Points: 7, 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.
Post #795026
Matt Whitfield
Posted Tuesday, September 29, 2009 5:20 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, February 15, 2013 7:29 AM
Points: 509, Visits: 718
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
Post #795028
Matt Whitfield
Posted Tuesday, September 29, 2009 5:30 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, February 15, 2013 7:29 AM
Points: 509, Visits: 718
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
Post #795034
richard.smith 47066
Posted Tuesday, September 29, 2009 10:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 08, 2013 9:28 AM
Points: 7, 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.
Post #795302
Matt Whitfield
Posted Tuesday, September 29, 2009 10:52 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, February 15, 2013 7:29 AM
Points: 509, Visits: 718
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

Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
Post #795304
Garadin
Posted Tuesday, September 29, 2009 3:02 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, November 07, 2012 4:08 PM
Points: 1,525, Visits: 4,047
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


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
Post #795456
« Prev Topic | Next Topic »

Add to briefcase
30 posts, Page 1 of 3
123»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.