Video Screencast Help
Endpoint Management Community Blog

Object Search in SQL 2005

Created: 27 Sep 2007
SK's picture
0 0 Votes
Login to vote

SQL 2000 had a feature called Object Search, which allowed you to search database objects for the presence of a specific word.

Microsoft decided not to include this feature in SQL 2005. In order to carry out a similar operation in SQL 2005, please use the following query:

SELECT name,
CASE xtype
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default or DEFAULT constraint'
WHEN 'F' THEN 'FOREIGN KEY constraint'
WHEN 'L' THEN 'Log'
WHEN 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'Inlined table-function'
WHEN 'P' THEN 'Stored procedure'
WHEN 'PK' THEN 'PRIMARY KEY constraint (type is K)'
WHEN 'RF' THEN 'Replication filter stored procedure'
WHEN 'S' THEN 'System table'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'Trigger'
WHEN 'U' THEN 'User table'
WHEN 'UQ' THEN 'UNIQUE constraint (type is K)'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
END as type
FROM sysobjects WHERE name LIKE '%search%'
UNION ALL
SELECT name, 'column' FROM syscolumns WHERE name LIKE '%search%'

Replace the two search sections with the word you are looking for.