Find the Database where specific Procedure/table exists in a given SQL instance

This script is used to find the database in which a specific procedure or a table name exists. Few of my developers share just a procedure name or table name for analysis but it used to be hard to find the database in which the procedure/table exists. Created simple script to save my time and sharing the same!

DECLARE @tableorproc varchar(4)
DECLARE @tableorprocname VARCHAR(80)
DECLARE @query nvarchar(2048)

set @tableorproc = 'p' -- change it to 'u' for table
set @tableorprocname  = 'Investigate_User_Cart_Issues' -- Chagne 
 
set @tableorprocname  = '%'+@tableorprocname  +'%'

set @query = 'use [?]; IF exists (select 1 from sys.objects where type = ''' + @tableorproc
+ ''' and name like ''' + @tableorprocname + ''') 
    select db_name() as DatabaseName, * from sys.objects where type = ''' + @tableorproc
+ ''' and name like ''' + @tableorprocname + ''''
 
EXEC sp_msforeachdb @query
--
--

NOTE: (2015/04/27) Updated the query to search for partial Table or Procedure Name! Make sure to include % at the beginning and at the end of search string. You may exclude it if you are searching for a specific table/proc.

Related: https://vijredblog.wordpress.com/2014/02/06/run-query-on-multiple-databases-or-tables-sp_msforeachdb-or-sp_msforeachtable/

Advertisements
This entry was posted in Productivity, SQL, SQL Query and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s