SQL SERVER 2008 :: Quick search / Shortcut to search Objects in the database (Tables, Views, Stored Procedures Etc.)

Sometimes we have so many objects in the database (views, tables, stored procs, functions etc) and it gets difficult to look for the required objects when we have to scroll through a drop down showing 1000s of objects.

I've written this simple stored procedure to help make the search easier.

 

CREATE PROCEDURE [dbo].[usp_Search]     

(     

@spname varchar(50)     

)     

AS     

BEGIN     

     

      /************************************************************

      AUTHOR: Viral Patel

      DESC: Quick search for all objects with specified string in

                  their names

      USAGE: Exec usp_Search 'string to search'

      ************************************************************/

 

 

      declare @QUERY varchar(500);   

      -- views 

      SET @QUERY = 'select * from sys.all_objects where type = ''v'' and name like ''%' + @spname +'%''';     

      print @QUERY     

      EXEC SYS.SP_SQLEXEC @QUERY ;     

      -- user tables

      SET @QUERY = 'select * from sys.all_objects where type = ''u'' and name like ''%' + @spname +'%''';     

      print @QUERY     

      EXEC SYS.SP_SQLEXEC @QUERY ;   

      -- stored procedures

      SET @QUERY = 'select * from sys.all_objects where type = ''p'' and name like ''%' + @spname +'%''';     

      print @QUERY     

      EXEC SYS.SP_SQLEXEC @QUERY ;   

     

      -- you can also add other objects type as per your requirement in the stored proc.

      -- Following are the types for various objects

     

      --FN  SQL_SCALAR_FUNCTION

      --IF  SQL_INLINE_TABLE_VALUED_FUNCTION

      --F   FOREIGN_KEY_CONSTRAINT

      --U   USER_TABLE

      --FS  CLR_SCALAR_FUNCTION

      --UQ  UNIQUE_CONSTRAINT

      --SQ  SERVICE_QUEUE

      --D   DEFAULT_CONSTRAINT

      --S   SYSTEM_TABLE

      --AF  AGGREGATE_FUNCTION

      --P   SQL_STORED_PROCEDURE

      --PK  PRIMARY_KEY_CONSTRAINT

      --V   VIEW

      --IT  INTERNAL_TABLE

      --X   EXTENDED_STORED_PROCEDURE

      --PC  CLR_STORED_PROCEDURE

      --TF  SQL_TABLE_VALUED_FUNCTION

END  

You can also create a keyboard shortcut to do the search... I prefer "Ctrl + 3"

Steps to create a short cut in SSMS:

Goto Tools->options->Environment->Keyboard

Scroll down to the desired shortcut you'd like to use and enter the stored proc name there. Few which i have created are here:

All you need to do after creating a shortcut is to select/highlight the parameter to pass in the query window and press the shortcut. Thats it!