SSWUGtv – Personal and Professional Office Relationships
With Stephen Wynkoop
In this edition of SSWUGtv Steve asks the question, "How do you separate personal friendships and professional relationships in the office without offending anyone?" You may be surprised with the results.
Watch the Show
sys_depends has been optimized
In a server environment where multiple databases work together as a whole, it becomes more difficult to maintain the dependencies of objects to one another. Previously we used the stored procedure sys_depends or the system view sys.sql_dependencies to find objects dependent on each other. If you created a calling object prior to creating the called object, the dependency is not tracked.
With the release of SQL Server 2008 a newer technique was created for identifying object dependencies. The new view sys.sql_expression_dependencies , and functions sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities use a different technique to identify the relationships. This allows the new view and functions to identify dependencies regardless of the order in which they are created, or even if the dependency is in another database, as long as the external object is fully qualified with a four part name (Instance.Database.Schema.Object).
Check them out sometime when you are looking for dependencies on your database objects. It would be interesting to compare the results with those from sys_depends or sys.sql_dependencies.
RE: Finding object usage in a Database
Steve:
Thanks for your tip on using SP_MSFOREACHDB. In that article, you suggested maintaining a complete script of the database in version control software. This is a great suggestion. What tool(s) would you recommend to create the complete script of the database?
Editor:
SQL Server Management Studio and Enterprise Manager have had a decent tool for scripting out an entire database for years. Every database diagramming tool I have used also has had reasonable scripting capabilities. ErWin, ErStudio, PowerDesignor, TOAD, ModelRight and even Visio have some sort of extract capability. Some are more user configurable than others.
RE: Testing Stored Procedures
Ian:
I hesitate to suggest this because it will produce false positives. With all recent editions of visual studio there is the option to use the linq to db library. This provides a pane where you can drop any stored procedure from a connected database, and a class for the results set will be automatically generated. It will fail if there is an error in the query. It will also fail under four other circumstances (IIRC): 1. If the procedure contains a temp table (not table variable, table variable can be used within), 2. If the procedure returns the output from a table variable (solution union with a single row with hard coded data types as first part of union and then exclude that row from results set. 3. If the procedure returns more than one data set. 4. If the procedure return only a small int or bit column. However it will produce a list for you….
Thanks for all the feedback sent to btaylor@sswug.org. It really helps when you participate in the conversation.
Cheers,
Ben
$$SWYNK$$
Featured White Paper(s)
How to Use SQL Server’s Extended Events and Notifications to Proactively Resolve Performance Issues
read more)