Search in Object Explorer Details Window
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