SQL Server: objects

Fully qualified object names

A fully qualified object name consists of four parts: server, database, schema and object. These are separated by a dot:

select * from [hst\inst].db.sch.obj;

The indivdual parts of the fully qualified object name might be omitted from the right side:

select * from [hst\inst].db.sch.obj;
select * from            db.sch.obj;
select * from               sch.obj;
select * from                   obj;

The schema part might be omitted (but still needs two dots):

select * from  db..obj;

An object name without schema information is first searched in the user's default schema. If not found there, it is searched in the dbo schema. If still not found, an error is thrown.

parsename()

parsename(qualifed_name, elem_from_right) is a handy function to get a part of a fully or partially qualified object name:

select parsename('inst.db.sch.obj', 1); -- obj
select parsename('inst.db.sch.obj', 2); -- sch
select parsename('inst.db.sch.obj', 3); -- db
select parsename('inst.db.sch.obj', 4); -- inst
select parsename(        'sch.obj', 1); -- obj
select parsename(        'sch.obj', 2); -- sch
select parsename(        'sch.obj', 3); -- NULL

Showing definitions of objects

Some objects (notably views or stored procedures) have a definition.

There are a few possibilites to show an object's definition:

object_definition(@obj_id) works hand in hand with object_id():

select object_definition(object_id('sys.all_objects'));
select object_definition(object_id('sp_msForEachDB'));

Object ID

Each object is identified by an numerical ID. This ID can be queried with object_id('sch.object_name'), for example

select object_id('sys.all_objects')

Object dependencies

Dependencies among objects (such as which views depend on a table they select from) can be shown with te stored procedure sp_depends.

sys.all_objects

sys.all_objects shows all schema scoped user defined objects and system objects.