February 1, 2012 at 6:47 am
Hi all,
i have alot of store procedures ...i have written discription etc for each while creating sp.
now i want to create a document for that.
i need a script which give me these informations.
Storeprocedure name,passing params,return params and description of each sp
February 1, 2012 at 6:58 am
Where is the description of the procedure stored? Part of the proc's definition?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 1, 2012 at 10:03 am
engrshafiq4 (2/1/2012)
Hi all,i have alot of store procedures ...i have written discription etc for each while creating sp.
now i want to create a document for that.
i need a script which give me these informations.
Storeprocedure name,passing params,return params and description of each sp
How you did your descriptions will make a difference. If you stuck it between /*... */ and assuming it's the first such comment in the proc you could do something like this, which is similar to what I use to verify my most recent updates are being used.
DECLARE @description VARCHAR(MAX)
SELECT @description = SUBSTRING([definition], CHARINDEX('/*',[definition])+2, CHARINDEX('*/',[definition])-CHARINDEX('/*',[definition])-2) FROM sys.sql_modules WHERE [object_id] = object_id('info_dbping')
SELECT @description AS Description
The rest of the information can be taken from sys.procedures, sys.parameters, and sys.types
SELECT spr.name AS ProcedureName, spa.name AS ParameterName, st.name AS ParameterType, spa.max_length, spa.is_output
FROM sys.procedures spr
JOIN sys.parameters spa ON spr.object_id = spa.object_id
JOIN sys.types st ON spa.system_type_id = st.system_type_id
February 1, 2012 at 10:08 pm
i am adding description on SHIFT+CONTROL+M
here is sample
-- =============================================
-- Author:Myname
-- Create date: 18 oct
-- Description:Display city list
-- =============================================
ALTER PROCEDURE procName
@Error INT OUTPUT
AS
BEGIN
--select query
END
February 2, 2012 at 2:18 am
Ok...
Name from sys.procedures. Parameters from sys.columns (join on object_id), definition from sys.sql_modules, you'll have to do some string parsing to get it out.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 5, 2012 at 4:29 am
this worked for me
SELECT spr.name AS ProcedureName,SUBSTRING(sd.definition,
CHARINDEX('n:',sd.definition)+2, 45)AS Definition, spa.name AS
ParameterName, st.name AS ParameterType, spa.max_length, spa.is_output
FROM sys.procedures spr
JOIN sys.parameters spa ON spr.object_id = spa.object_id
JOIN sys.types st ON spa.system_type_id = st.system_type_id
JOIN sys.sql_modules sd ON sd.object_id=spr.object_id
and spr.name like 'usp_be_%'
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply