How to search a database object?

A database is made out of database objects. Tables, Views, Indexes, Synonyms, Jobs, Functions, Triggers, Constraints are some of the popular database objects which DBA and developers use day in day out. Smaller companies may have small database meaning less database objects. When you have less db objects or you are owner or creator of these objects, you will know exactly where to find for the database objects you are looking for. You might need them for modification or for research purpose. Sometime you need to find them as soon as possible during emergencies. You do not have all day to search through object browser on your database IDE. Within few minutes, you should be able to confirm the database object does not belong to your db server or yes, you found it! This articles talks about oh how to quickly locate the database objects from your database that houses thousands of db objects.

Questions to Ask: 

If you are a DBA working with a developer there are few questions that you must ask before you start your "Google" search within your database.  If your developer or your requester knows all the answer to your questions you are golden, but over 80% of the time, they don't know the answer to all your questions. Relax, dba republic is here to help when you don't have everything you need to begin. Any answers or clue can help you lead to the direction you want to go. 

SQL Server:

1. What is the database Instance Name?
2. What is the name of the database?
3. What is the name of the database objects?
4. What type of object name? view, table,functions, stored procedures, job etc?

Oracle DB:

1. What is the database Instance name?
2. What is the name of schema?
3. What is the object name?
4. What is the object type?

After interviewing the requester or developer, you may get answer to all the questions or part of the questions. We will go over on how to locate the objects with all the answers or partial answers to your questions. Let's begin.

ORACLE:

Detail obtained from the requester

1. Object  Name: Order
2. Object Type: Table
3. Owner/Schema: Sales_Admin

SQL Syntax:

SELECT * 
FROM   dba_objects 
WHERE  object_name = 'YourTableName' 
       AND object_type = 'TABLE' 
       AND owner = 'SALES_ADMIN'; 

Output:

The query output will give you some generic detail regarding the searched database object. If you need to drill down the detail, you will need to be looking at a system view which stores the detail

information. For our object types in our example we will do something like.

Table Detail SQL:

SELECT * 
FROM   dba_tables 
WHERE  table_name LIKE 'YourTableName'; 

If the database objects you are trying to find is Trigger, then you will use the following Query.

SQL Syntax:

SELECT * 
FROM   dba_objects 
WHERE  object_name = 'YourTriggerName' 
       AND object_type = 'TRIGGER' 
       AND owner = 'SALES_ADMIN'; 

Trigger Detail SQL:

SELECT * 
FROM   dba_triggers 
WHERE  trigger_name LIKE 'YourTriggerName'; 

Similarly, you can use the table below if you are dealing with other database types. The table below shows the type of database object, object type and the system view associated to that db object. It has the most frequently used db objects not all, you will need to go over Oracle document to get the full list of object types and other details.

Database Object	Object Type	System View
Database Link	DATABASE LINK	dba_dba_links
Function	FUNCTION	dba_functions
Index	INDEX	dba_indexes
Materialized View	MATERIALIZED VIEW	dba_mviews
Sequence	SEQUENCE	dba_sequences
Stored Procedures	PROCEDURE	dba_procedures
Synonym	SYNONYM	dba_synonyms
Table	TABLE	dba_tables
Trigger	TRIGGER	dba_triggers
View	VIEW	dba_views

Note:

1. Only DBA has access to DBA_%  system views, if you are developer or don't have access to dba system views replace DBA with 'USER' or 'ALL'.
2. The above example has everything the dba needs to find the object. What if the requester does not have answers to all the questions? In such case, you will need to use SQL wild card on object name to get the rest detail.

SQL Server:

Detail obtained from the requester

1. Object  Name: Order
2. Object Type: Table
3. Database Name: Sales_Admin

SQL Syntax:

USE sales_admin]; 
SELECT * 
FROM   sys.objects 
WHERE  name = 'database_growth' 
       AND type = 'U'; 

sys.objects view is similar to that of oracle dba_object views which stores the generic information on database object. This does not give you the greater detail on the object. To get the detail you will need to query a sys.tables views.

Table Detail SQL

USE sales_admin]; 
select * from sys.tables where name = 'Order'; 

Here, the database name is know therefore it easy to find the db object. Lets say, the requester only know the database object name and the type. Now, we don't have the database name.  Your Instance has around 250 databases. This is impossible to go over each databases and check for the table name. How are you going to search a db objects when you dont know the database name?

SQL Syntax:

EXEC Sp_msforeachdb
'USE [?]; select ''[?]'' dbname, name from sys.objects where name = ''employee''' ;

This query runs on all databases in your SQL Instance. The output of this query will give you the name of database where the table resides.  You can then use the sys.tables tables to get the detail on this db object.

Similarly, you can use the table below if you are dealing with other database types. The table below shows the type of database object, object type . It has the most frequently used db objects not all, you will need to go over SQL document to get the full list of object types and other details.

Database Object	Object Type
Primary Key	PK
Function	FS/ FT
Foreign Key	FK
Sequence Object	SO
Constraints	C
Stored Procedures	P
Synonym	SN
Table	U
Trigger	TR/TA
View	V

Note:

1. Only DBA has access to  system tables and all the database, if you are developer or don't have access to sys tables or other database, you will need to work  with your DBA.
2. The above example has everything the dba needs to find the object. What if the requester does not have answers to all the questions? In such case, you will need to use SQL wild card on object name to get the rest detail.