is table used by an views

At some point, when I first started working with SQL Server, I got the idea that it might be beneficial to take several Access databases and migrate the data to SQL Server.  I had a set of tables which were common to several Access applications, so I figured I would put them into their own SQL Server database, but after a while I realized that separating the data from the Access applications was more trouble than it was worth, so I copied all of those tables into one database.

So, before I delete that common tables database, I would like to know if there is an easy way to identify object relationships within SQL Server.  Specifically, I need to know whether any of the tables in that database are being used in any of the views, stored procedures, or UDFs in either that database, or any of the other databases on that server.
LVL 50
Dale FyeAsked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Correct.  SQL databases don't track outside-database dependancies.  That's kind of like saying 'List all the people in the world that have my phone number', there's no way of knowing unless you track it manually.

Pragmatic Works Doc xPress does this, but you have to load all of the db's / SSIS / SSRS / SSAS into 'the world' for it to tell you were the dependancies lie.

Of course, you can always rename the database and see who screams.

Good luck.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
In SSMS, click on table, then Right-click > View Dependancies > Objects that depend on [your table name]

Guessing there are a number of third-party tools, probably Redgate and Pragmatic Works DOCxPress, that have this automated in such a way that it'll generate for all objects.
0
 
Scott PletcherSenior DBACommented:
Starting with SQL 2008, you can use this view:

sys.sql_expression_dependencies
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Dale FyeAuthor Commented:
Thanks, Jim.  That seems to work within a given database, but not across databases on the same server.

Scott,  can you provide an example of how to use that?  I've tried a couple of things, but nothing seems to work.

Dale
0
 
Dale FyeAuthor Commented:
Thanks for that recommendation, JIm.  I'm going to use one of the several 3rd party apps.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
If you go with Pragmatic Works mention that I went bull riding at Cowboy Jack's in Minneapolis with Brian Knight and Graham Bless, and referred you to them.  Guessing it won't get you anything other than a couple of good yuks, although it'll keep me on their good side.

Thanks for the grade.  Good luck with your Easter Egg hunt.  -Jim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.