SQL query stored procedure information

1. View all stored procedures and functions

   Exec sp_stored_procedures -- or select * from dbo. sysobjects where OBJECTPROPERTY (id, N 'isprocedure ') = 1 order by name

2. View Stored Procedure content

   Select text from syscomments where id = object_id ('stored procedure name') --- or sp_helptext stored procedure name

3. View stored procedure parameters

   Select 'parameter name' = name, 'type' = type_name (xusertype), 'length' = length, 'parameter sequence '= colid, 'sorting method '= collation from syscolumns where id = object_id ('stored procedure name') -- or -- view the Stored Procedure Parameter information: -- if the returned value is> 1, there is a parameter. Otherwise, no create proc sp_PROC_Params @ procedure_name sysname, -- stored procedure or user-defined function name @ group_number int = 1, -- the Group Number of the stored procedure must be between 0 and 32767, 0 indicates displaying all parameters of the stored procedure group @ operator nchar (2) = n' = '-- the search object operator as set nocount on declare @ SQL nvarchar (4000) SET @ SQL = n' SELECT PorcedureName = CASE WHEN o. xtype IN (''p', ''x') then quotename (o. name) + n''; ''+ CAST (c. number as varchar) WHEN USER_NAME (o. uid) = ''system _ function_schema ''AND o. xtype = ''F N'then o. name WHEN USER_NAME (o. uid) = ''system _ function_schema ''then'': ''+ o. name WHEN o. xtype = ''fn ''then quotename (USER_NAME (o. uid) + N ''. ''+ QUOTENAME (o. name) else quotename (o. name) END, Owner = USER_NAME (o. uid), GroupNumber = c. number, ParamId = c. colid, ParamName = case when o. xtype = ''fn ''AND c. colid = 0 then'' <Returns> ''ELSE c. name END, Type = QUOTENAME (t. name) + case when t. name IN (''decimal '', ''nume Ric '') THEN n'' ('' + CAST (c. prec as varchar) + n'', ''+ CAST (c. scale as varchar) + n'') ''WHEN t. name = n''float'' OR t. name like ''% char'' OR t. name like ''% binary ''then n'' ('' + CAST (c. prec as varchar) + N') '''else' ''' END, Orientation = case when o. xtype = ''fn ''AND c. colid = 0 then'' <Returns> ''else n'' input' + case when c. isoutparam = 1 then''/Output ''else ''' end from sysobjects o, syscolumns c, partition ype S t WHERE o. id = c. id AND c. xusertype = t. xusertype AND o. name' + case when @ operator IN ('=', '>', '> =', '!> ',' <',' <= ','! <',' <> ','! = ') THEN @ operator + QUOTENAME (@ procedure_name, ''') WHEN @ operator = 'in' THEN @ operator + N' IN (' + QUOTENAME (@ procedure_name, '''') + ') 'When @ operator IN ('like',' % ') THEN 'like' + QUOTENAME (@ procedure_name ,'''') ELSE '=' + QUOTENAME (@ procedure_name, ''') END + N' AND ('+ case when @ group_number BETWEEN 1 AND 32767 then n' c. number = '+ CAST (@ group_number as varchar) WHEN @ group_number = 0 THEN n'1 = 1' ELSE n'c. number = 1 'end + N' AND o. xtype IN (''p', ''x') OR (c. number = 0 AND o. xtype = ''fn '') OR (c. number = 1 AND o. xtype IN (''if'', ''tf'') 'exec sp_executesql @ SQL

4. View all stored procedure content

   select   b.name   ,a.text   from   syscomments   a,sysobjects   b   where   object_id(b.name)=a.id   and   b.xtype   in('P','TR') 

5. View the Stored Procedure containing string content

   Select B. name,. text from syscomments a, sysobjects B where charindex ('string content',. text)> 0 and object_id (B. name) =. id and B. xtype in ('P', 'tr ')