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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy