dcsimg

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.

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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers