Cacher is the code snippet organizer for pro developers

DECLARE   @SPName VARCHAR(256)  
 SET   @SPName   =    'My_SP_Name'  

 DECLARE   @DBName VARCHAR(256)  
 DECLARE   @varSQL VARCHAR(512)  
 DECLARE   @getDBName   CURSOR  
  SET   @getDBName   =    CURSOR    FOR  

 SELECT    NAME  
 FROM   sys.databases  

 CREATE    TABLE    #   TmpTable (DBName VARCHAR(256),  
    SchemaName VARCHAR(256),  
    SPName VARCHAR(256))  

 OPEN   @getDBName  
 FETCH    NEXT  
       FROM   @getDBName   INTO   @DBName  

 WHILE @@FETCH_STATUS   =   0  
 BEGIN  
  SET   @varSQL   =    'USE ['    +   @DBName   +    '];  
  INSERT INTO    #   TmpTable  
  SELECT    '''+ @DBName + '''    AS   DBName,  
  SCHEMA_NAME   (schema_id)   AS   SchemaName,  
  NAME    AS   SPName  
  FROM   sys.all_objects  
  WHERE   [   TYPE   ]   =    ''   P   ''    AND    NAME    LIKE    ''   %   ' + @SPName + '   %   '''  
  EXEC   (@varSQL)  

 FETCH    NEXT  
       FROM   @getDBName   INTO   @DBName  
 END  

 CLOSE   @getDBName  
 DEALLOCATE   @getDBName  

 SELECT    *  
 FROM    #   TmpTable  
 DROP    TABLE    #   TmpTable