Featured Article(s)
Molecular MDX: Change Across Periods and Periods-to-Date Aggregations, Part 1
Part 1: Understand the basics of comparing periods, and accomplish year-to-date and other time-based aggregations, across the Time dimension. Join author Bill Pearson in a practical introduction to the mechanics of time and the analysis of change from period to period, as well as period-to-date aggregation at multiple hierarchical levels.
(Only a few days left to register for the class this month)
SSIS Class – Online, 12 Hours/classes
Eric Johnson’s class starts May 15 and you can register now to attend! Brought straight to your desktop, this 12-session class includes weekly business hours with Eric, LOTS of information about SSIS and how you can put it to use in your own shop. There is SO MUCH information packed into this course, it’s unbelievable. Take a look at the class sessions here. If you’re working with SSIS, or considering it, you can’t go wrong.
[Find out more] here at the class site.
Cross Database Dependency
by Ben Taylor
Here is a nut that is not easy to crack. A manageable design does not have cross database dependency. For example, you don’t have stored procedures in database A that reference objects in database B. So much for the perfect world. You can’t easily replace database objects at run time…at least not without using dynamic SQL.
For any number of good reasons we end up with multiple databases, and database code that interacts with two or more of those databases. The work of the database administrator becomes more complex with each cross database interaction. The DBA must be aware of dependencies that are not easily known. SQL Server 2008 introduced a number of catalog view and system functions that allow you to recognize cross database dependencies.
The system view sys.sql_expression_dependencies provides a great deal of information regarding cross database dependencies. It handles a number of database objects that may rely on each other. The neat part is that it tells you what object is dependent on another object, not just by ID, but by name, type (i.e. view, procedure, table, column…), database, schema, and name. How cool is that?
The system function sys.dm_sql_referencing_entities and its corollary sys.dm_sql_referenced_entities allow you to return a list of objects that use a specific entity in your database. While this function does not allow cross databases, it is useful for finding where something is used in your current database. It works similar to the old system stored procedure sp_depends.
sys.dm_sql_referencing_entities (
‘ schema_name.referenced_entity_name ‘ , ‘ <referenced_class> ‘ )
Microsoft provides more information at Reporting SQL Dependencies.
Do you want to share a tip with our community regarding how you handle cross database dependency? Drop a note to btaylor@sswug.org.