Is there any other place to go looking for database metadata in MySQL 5.0 other than the INFORMATION_SCHEMA views?

In particular, there seems to be no PARAMETERS or REFERENTIAL_CONSTRAINTS views as this page states:
http://dev.mysql.com/doc/refman/5.0/...ma-tables.html

are there system tables somewhere (perhaps undocumented, i don't care so much about that) that I could look in for this information? Perhaps some use of the SHOW command?

Thanks!

use the SHOW command

thanks!

however, I don't see a SHOW command that will list the parameters of a procedure or function in a result set (sorry I didn't make my wish clear in the OP ). it looks like the best I can get is just to show the DDL for the proc using SHOW CREATE PROCEDURE. Or am I missing something obvious?

I was hoping for something analogous to SHOW COLUMNS for tables - something that would list out all params, with their datatypes, etc, in a result set.

There must be some tables, because e.g. the JDBC driver does list this information. You could download the source code for the JDBC driver and have a look at the class DatabaseMetaData.java. Search for getProcedureColumns. You should see the used SQL statement somewhere in there.

Try select param_list from mysql.proc. Is that what you were looking for ?
I also am in desperate need of the INFORMATION_SCHEMA.PARAMETERS table. IN the meanwhile I created my own (unfortunately not in the INFORMATION_SCHMEA 'database' as that is not permitted) and created a procedure that will fill that table for me for the procedure I feed it ... if interested, let me know.

Gr,
Yveau

That's better, but not great. It's all the parameters for a proc in a single column, hardly a normalized design. I was hoping for a way to get each param in its own row, with a column for name, datatype, length, default value, etc.

I would love to see your code (I assume you are parsing out the param_list value into a result set?) Anyway, can you post it here? Thanks.

OK, here goes:

First create the table, I used the test database:
----------
use test;

drop table if exists test.PARAMETERS;

create table test.PARAMETERS
( SPECIFIC_CATALOG varchar(64)
, SPECIFIC_NAME varchar(64)
, ORDINAL_POSITION bigint
, PARAMETER_MODE varchar(3)
, PARAMETER_NAME varchar(64)
, DATA_TYPE varchar(64)
, CHARACTER_MAXIMUM_LENGTH bigint
, NUMERIC_PRECISION bigint
, NUMERIC_SCALE bigint);

----------

Next, create a function to generate a sort of normalized output from the param_list, again I used the test database:
----------
drop function if exists test.fnSplitString;

delimiter //

create function test.fnSplitString(pText varchar(200), pSeparator char(1), pNumber int)
returns varchar(200)
begin
declare TextString varchar(200);
declare Positie int;
declare Lengte int;
declare Current int;

set TextString = concat(pText, pSeparator);
set Positie = 1;
set Lengte = locate(pSeparator, TextString) - 1;
set Current = 1;

-- select pText, TextString, Positie, Lengte;

while (Lengte > 0) and (Current < pNumber) do
-- select pText, TextString, Positie, Lengte, replace(substring(TextString,Positie,Lengte), pSeparator, '');
-- select replace(substring(TextString,Positie,Lengte), pSeparator, '') as Parameter;

set Positie = Positie + Lengte + char_length(pSeparator);
set Lengte = locate(pSeparator, TextString, Positie) - Positie;
set Current = Current + 1;
end while;
return replace(substring(TextString,Positie,Lengte), pSeparator, '');
end;
//

delimiter ;

----------

Finally, create the stored procedure that will fill up the test.PARAMETERS table, using the database and procedure names as input:
----------
drop procedure if exists test.csProcParm;

delimiter //

create procedure test.csProcParm(IN pDb varchar(64), IN pProcedure varchar(64))
begin
declare lCur int;
declare lParam varchar(64);

delete
from PARAMETERS
where SPECIFIC_NAME = pProcedure
and SPECIFIC_CATALOG = pDb;

set lCur = 1;
set lParam = (select fnSplitString(trim(fnSplitString(param_list, ', ', lCur)), ' ',2)
from mysql.proc
where type = 'PROCEDURE'
and db = pDb
and name = pProcedure);

while (char_length(lParam) > 0) do
insert
into PARAMETERS
( SPECIFIC_CATALOG
, SPECIFIC_NAME
, ORDINAL_POSITION
, PARAMETER_MODE
, PARAMETER_NAME
, DATA_TYPE
, CHARACTER_MAXIMUM_LENGTH
, NUMERIC_PRECISION
, NUMERIC_SCALE)
select pDb
, pProcedure
, lCur
, fnSplitString(trim(fnSplitString(pr.param_list, ', ', lCur)), ' ',1)
, lParam
, co.DATA_TYPE
, co.CHARACTER_MAXIMUM_LENGTH
, co.NUMERIC_PRECISION
, co.NUMERIC_SCALE
from mysql.proc pr
inner join INFORMATION_SCHEMA.COLUMNS co
on co.table_schema = pr.db
and co.table_name = substring(pr.name, 3, char_length(pr.name)-5)
and co.column_name = substring(lParam, 2)
where pr.type = 'PROCEDURE'
and pr.db = pDb
and pr.name = pProcedure;

set lCur = lCur + 1;
set lParam = (select fnSplitString(trim(fnSplitString(param_list, ', ', lCur)), ' ',2)
from mysql.proc
where type = 'PROCEDURE'
and db = pDb
and name = pProcedure);
end while;

end;
//

delimiter ;

----------

I needed this for a set of 'elementary stored procedures' (insert, update, delete) so in the procedure I make a join with the INFORMATION_SCHEMA.COLUMNS table, because my parameters are the same as the column names of the table it is designed for. (the procedures have a 2 character prefix and a 3 character postfix, that explains the substring(x,2,char_length(x)-5. and the parameters have a 1 character prefix, that explains the substring(x,2))

Hope this gives you a starting point for creating your own sollution. Let me know if you need some more help.

Gr,
Yveau

cool, thanks - I'll try it out.

fyi, one thing I just found is that this scheme doesn't work if there are any comments mixed in with the proc definition where the parameters are defined, because the comments become part of the param_list column. The problem is that the mysql devs were a bit lazy and they just dump everything between the parens into the param_list column. They don't actually parse it using a sql parser.

Here's an example of what I'm talking about:

Pfew, now there's a challenge to get the comments out ...
Hope I'll find some time to break my brain on this one ... Thanks for noticing !