Search the Database

Searching for an object in a SQL 2000 Database is easier by using this undocumented stored procedure sp_MSobjsearch. This can be used to search any SQL objects such as User Table, System Table, View, SP, triggers, columns, etc...


EXEC sp_MSobjsearch
=============================================
--PARAMETERS
=============================================
@searchkey default NULL
@dbname default current db = db_name(), valid DB name or * (ALL)
@objecttype default 1 (user table), can be valid objtype or 4096 (ALL), see remarks @hitlimit default 100 rows, 0 is all results
@casesensitive default 0, only valid when server is case sensitive
@status default 0 = no status, 1 = send percentage progress status back based
database/step
@extpropname default NULL
@extpropvalue default NULL

=============================================
-- REMARKS
=============================================
@objecttype
user table = 1 from @dbname..sysobjects
system table = 2 from @dbname..sysobjects
view = 4 from @dbname..sysobjects
sp = 8 from @dbname..sysobjects
rf(repl sp) = 16 from @dbname..sysobjects
xp = 32 from @dbname..sysobjects
trigger = 64 from @dbname..sysobjects
UDF = 128 from @dbname..sysobjects
DRI Constraints = 256 from @dbname..sysobjects
log = 512 from @dbname..sysobjects
column = 1024 from @dbname..syscolumns
index = 2048 from @dbname..sysindexes
all = 4096
=============================================