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 =@type
order by name
GO