Video Screencast Help

Object Search in SQL 2005

Created: 27 Sep 2007 | Updated: 13 Nov 2013
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 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'Inlined table-function'
WHEN 'P' THEN 'Stored procedure'
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 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
END as type
FROM sysobjects WHERE name LIKE '%search%'
SELECT name, 'column' FROM syscolumns WHERE name LIKE '%search%'

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