Usp_GetObjByColumName

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Usp_GetObjByColumName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Usp_GetObjByColumName]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE PROCEDURE Usp_GetObjByColumName
(
@inColText Varchar(255) = ' ' ,
@inObjType Varchar(1) = 'T'-- [  T : Table ; P:Proecdure' ]
)
AS

Select @inColText = '%' + @inColText + '%'
Select @inObjType = Upper(@inObjType)

Select   @inObjType=Case @inObjType
When 'T' then 'u'
When 'P' then 'p'
Else 'u' End


SELECT  Distinct(Object_Name(Id))As Object ,Name As Column_Name
FROM SysColumns 
WHERE Name Like @inColText 
And Id in(
    Select  Distinct Id 
From SysObjects 
Where Type =@inObjType
 )

IF @@rowcount = 0 
Begin
Print '*Report*' Select  Case @inObjType
When 'T' then 'Search Result Gives No Tables  '
When 'P' then 'Search Result Gives No Procedures '
Else  'Search Result Gives No Tables ' End
Print '************************************************ Done ************************************************************'
End
Else
Begin

Print '************************************************ Done ************************************************************'
End
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO