Geeks With Blogs
John Workman The Workdog's weblog.... a brain dump, of sorts.

The other day someone saw me hit F4 and bring up the Object search in a SQLServer 2000 Query Analyzer.  They only had SQLServer 2005 installed and wanted to know how to use it there.  After some research, I realized that it no longer exists.  I'm not sure why they took this away.  I found it quite useful, although I 'fat fingered' the f4 key more times than I hit it on purpose. 

Although the UI made it easy, this information is still available in SQLServer 2005 through these two tables: sysobjects and syscolumns.  Here's a query that may help.

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 '%bu_id%'
UNION ALL
SELECT name, 'column' FROM syscolumns  WHERE name LIKE '%bu_id%'

Posted on Thursday, September 14, 2006 7:14 PM SQL | Back to top


Comments on this post: Objects Search in SQLServer 2005 - work around

# re: Objects Search in SQLServer 2005 - work around
Requesting Gravatar...
Make sure you check out my post,
http://geekswithblogs.net/workdog/archive/2006/09/19/91661.aspx
which has an improved query
Left by John Workman on Oct 09, 2006 5:17 AM

Your comment:
 (will show your gravatar)


Copyright © John Workman | Powered by: GeeksWithBlogs.net