HOW TO GET ALL STORED PROCEDURES DYNAMICALLY IN A SERVER

CREATE TABLE #x(db SYSNAME, s SYSNAME, p SYSNAME);

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'INSERT INTO #x SELECT ''' + name + ''',s.name, p.name
  FROM ' + QUOTENAME(name) + '.sys.schemas AS s
  INNER JOIN ' + QUOTENAME(name) + '.sys.procedures AS p
  ON p.schema_id = s.schema_id;
' FROM sys.databases WHERE database_id > 4:

EXEC sp_executesql @sql;

SELECT db,s,p FROM #x ORDER BY db,s,p;

DROP TABLE #x;

GET ALL TABLES IN A SERVER BY USING SYSTABLES:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'INSERT INTO #x SELECT ''' + name + ''',s.name, p.name
  FROM ' + QUOTENAME(name) + '.sys.schemas AS s
  INNER JOIN ' + QUOTENAME(name) + '.sys.tables AS p
  ON p.schema_id = s.schema_id;
' FROM sys.databases WHERE database_id > 4

 print @sql

impotant sys table to get sp's details:

SELECT [Routine_Name] 
FROM [INFORMATION_SCHEMA].[ROUTINES]
WHERE [ROUTINE_TYPE] = 'PROCEDURE'

 SELECT OBJECT_NAME(object_id) FROM sys.sql_modules 
  WHERE definition LIKE '%ES_PURGE%'

  SELECT OBJECT_NAME(ID) AS SP_NAME FROM syscomments 

 WHERE text LIKE '%ES_PURGE%' 

SELECT [Name] FROM [sys].[procedures]

SELECT [Name] FROM [sys].[objects] WHERE [type] = 'P'

SELECT [Name] FROM [sys].[all_objects] WHERE [Type] = 'P' AND [Is_MS_Shipped] = 0

SELECT [Name] FROM [dbo].[sysobjects] WHERE [XType] = 'P'