CODEWITHSUNDEEP
stored-proceduressybasesap-ase
Grunter
Grunter

Reputation: 187

SQL Query to get Stored Procedure parameters and datatypes in SyBase

I have a requirement to query SyBase and return the parameters and datatypes for a stored procedure. I'm looking for a SQL query to do that. In SQL Server I can use

select parameters.name as fieldname, types.name as field type
from sys.parameters
inner join sys.type on parameters.system_type_id = types.system_type_id
where object_id = <id> 
and types.name <> 'sys name' 
order by filename

So I'm looking to do something similar in SyBase. I know the id of the stored procedure, I just need to extract that parameter information.

Upvotes: 0

Views: 2396

Answers (1)

Grunter
Grunter

Reputation: 187

I managed to work this one out myself. SQL to get the parameter and datatype of a stored procedure.

SELECT col.name AS fieldname, ty.name AS fieldtype
FROM sysobjects t 
JOIN syscolumns col ON t.id=col.id 
JOIN systypes ty ON ty.usertype=col.usertype
WHERE t.id={key}

To get the same for a table.

SELECT col.name AS fieldname, ty.name AS fieldtype,
CASE WHEN col.name IN (
index_col(object_name(si.id), indid, 1),
index_col(object_name(si.id), indid, 2),
index_col(object_name(si.id), indid, 3),
index_col(object_name(si.id), indid, 4),
index_col(object_name(si.id), indid, 5),
index_col(object_name(si.id), indid, 6),
index_col(object_name(si.id), indid, 7),
index_col(object_name(si.id), indid, 8)
) THEN 1 ELSE 0 END AS isPK
FROM sysobjects t 
JOIN syscolumns col ON t.id=col.id 
JOIN systypes ty ON ty.usertype=col.usertype
LEFT JOIN sysindexes si ON si.id=t.id AND si.name LIKE '%pk%'
WHERE t.id={key}

Upvotes: 1

Related Questions