Post reply

sp_MSForEachTable with sp_Depends

  • February 27, 2012 at 8:55 am

    #391663

    Hello Everyone

    Today I am working with some new databases, and I am trying to determine what tables are actually being used, and of course by what objects. So I am using sp_msforeachtable 'sp_depends ''?'' ' . I have no problem executing that, but I would like to see the table names along with the results. Currently I can see only the ObjectName and Type of Object.

    If anyone has a better suggestion I am open to hearing about that as well.

    Thank You in advance for all your help

    Andrew SQLDBA

  • February 27, 2012 at 10:05 am

    #1452176

    andrew do any of these DMV's help you get the iinfo you are looking for?

    select * from sys.sql_expression_dependencies --(2008 +)

    select object_name(id) as MyObject,object_name(depid) as ReferencedObject,* from sys.sysdepends --(2005 +)

    select object_name(fkeyid) as MyObject,object_name(rkeyid) as ReferencedObject ,* from sys.sysforeignkeys

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 27, 2012 at 10:13 am

    #1452179

    Hi Lowell

    Thank You, the query in the middle came closest. It will return the table name and the object name that the the table is depended upon.

    This is a good start.

    Thanks again

    Andrew SQLDBA

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply