Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
Database Tools
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
News Via RSS Feed

follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Oct 1, 2000

Find that Forgotten Object Name

By Alan Enderby

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


MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM

Thanks for your registration, follow us on our social networks to keep up-to-date