September 25, 2009 at 10:04 am
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
September 27, 2009 at 4:36 am
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[/url]
Why I wrote a sql query analyzer clone
September 29, 2009 at 3:32 am
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!
September 29, 2009 at 3:59 am
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[/url]
Why I wrote a sql query analyzer clone
September 29, 2009 at 5:19 am
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.
September 29, 2009 at 5:20 am
Very interesting...
Did you run a profiler trace? I'm intrigued now... 🙂
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 29, 2009 at 5:30 am
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[/url]
Why I wrote a sql query analyzer clone
September 29, 2009 at 10:49 am
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.
September 29, 2009 at 10:52 am
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[/url]
Why I wrote a sql query analyzer clone
September 29, 2009 at 3:02 pm
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?
September 29, 2009 at 3:09 pm
Here's a test script. You'll notice that it doesn't actually delete any rows when executed with FMTONLY ON.
USE tempdb
CREATE TABLE Test( TestIntint)
INSERT INTO Test(TestInt) SELECT 1
GO
CREATE PROCEDURE TestSP
AS
DELETE FROM Test
SELECT 'A' A, 'B' B, 'C' C, 'D' D
GO
SELECT COUNT(*) FROM Test
SET FMTONLY ON
EXEC TestSP
SET FMTONLY OFF
SELECT COUNT(*) FROM Test
DROP PROCEDURE TestSP
DROP TABLE Test
All well and good, but how do you extrapolate information from that pseudo-dataset?
September 29, 2009 at 3:20 pm
Also, what do you do with multiple result sets from the same SP?
USE tempdb
CREATE TABLE Test( TestIntint)
INSERT INTO Test(TestInt) SELECT 1
GO
CREATE PROCEDURE TestSP
AS
DELETE FROM Test
SELECT 'A' A, 'B' B, 'C' C, 'D' D
SELECT 'E' E, 'F' F, 'G' G, 'H' H
SELECT 'I' I, 'J' J, 'K' K, 'L' L
GO
SELECT COUNT(*) FROM Test
SET FMTONLY ON
EXEC TestSP
SET FMTONLY OFF
SELECT COUNT(*) FROM Test
DROP PROCEDURE TestSP
DROP TABLE Test
September 29, 2009 at 3:25 pm
richard.smith 47066 (9/29/2009)
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.
The parameters can be queried from information_schema.parameters and dummy data supplied via code.
That said, even if you do that, and FMTONLY works, it doesn't address situations where SP's change their output depending on input. (I'm blanking on the name for this coding technique). For instance, if you input @Type = 'A' it returns a dataset with 5 fields, but @Type = 'B' returns a dataset with 10 fields.
September 29, 2009 at 3:46 pm
Garadin
If you're using say SqlClient under .NET then that gives you meta-data irrespective of the presence of rows.
You can move from one result set to the next with NextResult().
Sorry for the short reply I'm not feeling 100%
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 29, 2009 at 3:58 pm
Matt Whitfield (9/29/2009)
GaradinIf you're using say SqlClient under .NET then that gives you meta-data irrespective of the presence of rows.
You can move from one result set to the next with NextResult().
Sorry for the short reply I'm not feeling 100%
Sorry to hear that Matt. I'd be interested to see confirmation of that from anyone who can write .NET(Learning .NET is on my list of things to do, it's just not very high on it 😉 ) I always believed that some application would be able to work with this meta data, but despite a very long thread on the subject last go round, nobody every came up with some working code to do it. That was also a year or so ago, so maybe it has all changed since then(or maybe the right people never saw the thread last time :hehe:).
I wonder if this could be taken one step further and turned into a CLR function that would allow you to just feed in a proc name and it'd do the rest.
If that was possible, maybe it could be taken one step further to create a table for you that you could use in an insert/exec.
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply