Search in Object Explorer Details Window - Bill Ramos on SQL Server - Site Home

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:

MSDN - Using Object Explorer Details and SQL Server Object Search
Buck Woody’s Carpe Datum - SQL Server 2008 Improvements Practical's - Object Explorer Details
Brad McGehee - Management Studio Improvements in SQL Server 2008
Dan English - SQL Server 2008 RC0 Object Explorer Details Search. Dan also includes some great suggestions.

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