MSSQL sql to get Stored procedure source
Posted: 11.02.2008, 08:17
Is it ok to use the following sql to get the Stored procedure metadata for MSSQL?
I have used the code from sp_stored_procedures which is used to get stored procedure info in Zeos.
Regards
Sandeep
Code: Select all
select
convert(sysname,db_name()) as PROCEDURE_QUALIFIER,
convert(sysname,user_name(o.uid)) as PROCEDURE_OWNER,
convert(nvarchar(134),o.name +';'+ ltrim(str(c.number,5))) as PROCEDURE_NAME,
-1 as NUM_INPUT_PARAMS,
-1 as NUM_OUTPUT_PARAMS,
-1 as NUM_RESULT_SETS,
convert(varchar(254),null) as REMARKS,
2 as PROCEDURE_TYPE,
convert(text, c.text)
from dbo.syscomments c, dbo.sysobjects o
where o.id = c.id
and c.colid = 1
and o.type in ('P', 'FN', 'TF', 'IF') /* Object type of Procedure, scalar UDF, table UDF */
and permissions (o.id)&32 <> 0
order by 1, 2, 3
Regards
Sandeep