Cross server cross db object dependencies in SQL 2008

Finding cross server cross database object dependencies has become easier with SQL 2008. The new catalog view sys.sql_expression_dependencies it is now possible to find the entities that are dependent on other entity across server or across databases in same server. Some of limitations this have is that it wont track the dependency while used inside OPENROWSET,OPENQUERY etc. For getting cross server and cross database dependencies the four part name(server.db.schema.entity) has to be passed. For getting column-level dependencies the entity has to be schema bound.

More details here

http://msdn.microsoft.com/en-us/library/bb677315.aspx

Another two useful catalog views we have in SQL 2008 are

sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities

sys.dm_sql_referenced_entities gives list of user-defined entity referenced by name in the definition of the specified referencing entity. For example, in case of stored procedure, it will give entire list of entities like table,views,etc which are used inside the procedure. It also lists cross server and cross db entities as well.

http://msdn.microsoft.com/en-us/library/bb677185.aspx

sys.dm_sql_referencing_entities does just the reverse. Given an user defined entity, it lists all entities where it refers given entity. i.e for a table it returns info on views, procedure,triggers etc which reference this across db and across server. more details can be found in below link

http://msdn.microsoft.com/en-us/library/bb630351.aspx