Finding OBJECTS in DB

Finding OBJECTS in DB

Sometime we have confusion that the object presents in the database or not. To find the objects present we have to query the sys.sysobjects.
I am trying to make it easy by a stored procedure. 
The limitation of the stored procedure is it helps to find such common objects like Scalar function, Inlined table-func, Stored procedure, Table function, Trigger, User define Table and Views only.
It also displays the definition or text of the supplied objects if it is not encrypted.
Please note that this is a prototype only. You can enrich the functionality for betterment.
IF EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID(‘find_Obj’))
   BEGIN
       DROP PROCEDURE find_Obj
   END
GO
CREATE PROCEDURE find_Obj
       (
          @p_ObjName VARCHAR(MAX)
       )
AS
  DECLARE @v_TYPE Varchar(MAX)
  BEGIN
          IF EXISTS(SELECT * FROM sys.sysobjects WHERE id=OBJECT_ID(@p_ObjName))
             BEGIN
                   SELECT OBJECT_NAME(id), case type when ‘FN’ then ‘Scalar function’
                                                     when ‘IF’ then ‘Inlined table-function’
                                                     when ‘P’  then ‘Stored procedure’
                                                     when ‘TF’ then ‘Table function’
                                                     when ‘TR’ then ‘Trigger’
                                                     when ‘U’  then ‘User define Table’
                                                     when ‘V’  then ‘View’ end [Type]
                   FROM   sys.sysobjects
                   WHERE  id=OBJECT_ID(@p_ObjName)
                  
                   SELECT @v_TYPE=type
                   FROM   sys.sysobjects
                   WHERE  id=OBJECT_ID(@p_ObjName)
                  
                   — Worked if not Encrypted
                   IF @v_TYPE=‘U’
                      BEGIN
                           EXEC(‘sp_columns ‘+ @p_ObjName)
                      END
                   ELSE IF  @v_TYPE=‘FN’
                            OR  @v_TYPE=‘FN’
                            OR @v_TYPE=‘IF’
                            OR @v_TYPE=‘TR’
                            OR @v_TYPE=‘V’
                      BEGIN
                           EXEC(‘sp_helptext ‘+ @p_ObjName)
                      END
                  ELSE
                      BEGIN
                         SELECT ‘ Defination Unknown’
                      END       
                  
             END
  END 
GO    
 
 
Hope you like it.
Posted by: MR. JOYDEEP DAS.

Related Posts