Find that Forgotten Object Name

October 1, 2000

Forget what that table or stored procedure was called ?
Want to produce a repetitive command with different object names ?

Sp_findobj will give you the name of all the objects containing a
given string.

You can also supply TSQL to go before and after the object name.


Exec sp_findobj 'stock', 'dump table', 'to stock_backup'

Will give
dump table stock_control to stock_backup
dump table stock_items to stock_backup
dump table stock_status to stock_backup
dump table stock_status_history to stock_backup
dump table stock_status_rules to stock_backup
dump table stock_transfer_rules to stock_backup

You can cut & past this into the query window and execute it

Use master
if exists (select * from sysobjects where id =
object_id('dbo.sp_findobj') and sysstat & 0xf = 4)
drop procedure dbo.sp_findobj

/****** Object: Stored Procedure dbo.sp_findobj Script Date:
02/09/96 13:16:05 ******/
create proc sp_findobj
@with varchar(30) ,
@head varchar(132) ='Select * from' ,
@tail varchar(132) ='' ,
@type char(255)='u'

select @with = '%' + @with + '%'
select @head + ' ' + name + ' ' + @tail
from sysobjects
where upper(name) like upper(@with)
and type =@type
order by name