Find that Forgotten Object Name

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.

Example

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
go
if exists (select * from sysobjects where id =

object_id(‘dbo.sp_findobj’) and sysstat & 0xf = 4)
drop procedure
dbo.sp_findobj
GO

/****** 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’
as

select @with = ‘%’ + @with + ‘%’
select
@head + ‘ ‘ + name + ‘ ‘ + @tail
from sysobjects
where upper(name) like
upper(@with)
and type [email protected]
order by name

GO

Latest Articles