Get Object Names Based on Parameter

If we now the parameter name and want to know the Objects using that parameter then in that case we can use the below stored procedure to retrive the Object Names

/***********************************************************************
* Author : V.U.M.Sastry Sagi
* Date   : 11/16/2011
* Purpose: Returns all Objects including SPs and Functions based on
           given parameter
***********************************************************************/
CREATE PROCEDURE GetObjectNamesBasedOnParameter ( @prameter VARCHAR(100) )
AS
    BEGIN
        SELECT  dbo.sysobjects.name AS ObjName ,
                dbo.sysobjects.xtype AS ObjType ,
                dbo.syscolumns.name AS ColName ,
                dbo.syscolumns.colorder AS ColOrder ,
                dbo.syscolumns.length AS ColLen ,
                dbo.syscolumns.colstat AS ColKey ,
                dbo.systypes.xtype
        FROM    dbo.syscolumns
                INNER JOIN dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id
                INNER JOIN dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype
        WHERE   ( dbo.syscolumns.name = @prameter )
                AND ( dbo.systypes.status <> 1 )
        ORDER BY dbo.sysobjects.name ,
                dbo.syscolumns.colorder 
           
          
    END