Share via

Search in Object Explorer Details Window

  • Article

There have been quite a few blog postings about the new Search feature in SQL Server 2008 Management Studio. Here are some examples of what you can find:

This posting shares with you what Object Search really works against. First, Object Search only works against Database Engine instances of SQL Server 2005 and 2008. In theory, Object Search should work against SQL Server 2000, but the result comes back with an error “Search failed: Failed to retrieve data for this request”. You’ll get a similar message against SQL Server 2005 instances, but you do get back results.

Object Search is found within the Object Explorer Details window. You’ll need to first make a connection to instance using the Object Explorer. Then press F8 to bring up the Object Explorer Details window. Search will work against either all databases or just the database that you are in within the Object Explorer Details window. Look at the path in the Yellow bar underneath the toolbar for the window to know what you will search against.

Object Search will look against the Name property for the following objects;

System Table Type Shown in Results
sys.database_principals DatabaseRole
sys.asymmetric_keys AsymmetricKey
sys.certificates Certificate
sys.objects Default Rule
sys,schemas Schema
sys.assemblies SQLAssembly
sys.all_objects StoredProcedure includes: TSQL stored procedure CLR stored procedure Replication-filter-procedure
sys.symmetric_keys SymmetricKey
sys.synonyms Synonym
sys.tables Table
sys.all_objects Trigger includes: Assembly (CLR) DML trigger SQL DML trigger
sys.indexes Index
sys.assembly_modules Module
sys.database_principals User includes SQL user Windows user Windows group Certificate user Asymmetric key user
sys.all_objects UserDefineFunctions includes: SQL table-valued-function SQL scalar function SQL inline table-valued function Assembly (CLR) scalar-function Assembly (CLR) table-valued function
sys.table_types UserDefinedTableType
sys.assembly_types AssemblyTypes
sys.all_views View
sys.xml_schema_collections

XmlSchemaCollection

If you search across all databases, you’ll need rights for viewing definitions for each of the databases on the server instance. If you have limited rights to only a specific set of databases, make sure you are in the database you have rights to before you begin your search.

You should also be aware that Object Search doesn’t honor the Tools | Options settings to not show system objects.

Despite the limitations of Object Search, it is certainly easier to use than making your own queries against system tables. Give it a spin and be more productive!

Technorati Tags: SQL Server 2008,Object Search

Comments

  • Anonymous
    February 08, 2009
    PingBack from http://www.clickandsolve.com/?p=4966
  • Anonymous
    February 09, 2009
    In my previous blog entry I talked about how Search works within the Object Explorer Details window in