SQL Server Performance

Tool for Identifiying SP

Discussion in 'Third Party Tools' started by SQL2000DBA, Sep 26, 2005.

  1. SQL2000DBA New Member

    Hi,

    I am looking for a tool through which i can identify whether any table is refered inside the sp is from which db. Actually i have around 200 SP's and they refered different DB(approx 10 DB). Now as per customer requirement we have to move toward decentralise setup.For the same i have to identity which all sp has reference table which refer another DB.

    Please let me know

    Thanks and Regards

    Ravi K
  2. Madhivanan Moderator

    You can use query to do this

    Select routine_Definition from Information_Schema.routines
    where routine_Definition like '% tt %' and routine_type='Procedure'
    To find in all DBs, run this

    Select
    'Select routine_Definition from '+Name+'.Information_Schema.routines where routine_Definition like ''% tt %''
    and routine_type=''Procedure'''
    from Master..SysDatabases
    Copy the result back to Query Analyser and run one by one

    Madhivanan

    Failing to plan is Planning to fail
  3. dineshasanka Moderator

  4. SQL2000DBA New Member

    Hi Madhivanan,

    Using above query i will get the stored procedure scripts and then i have to search for the refer table from the output,which is very tedious task. I am looking for tool which can provide me sp name, db & table refered inside sp.

    Thanks and Regards

    Ravi K
  5. Madhivanan Moderator

    Then try what Dinesh suggested

    Madhivanan

    Failing to plan is Planning to fail
  6. dineshasanka Moderator

  7. SQL2000DBA New Member

    Hi dineshasanka,

    I have tried Red Gate 'SQL dependency viewer'. Tool provides dependency table dependancy of stored procedure but if table existing in another DB it does not provide me table details.

    Thanks and Regards
    Ravi K
  8. FrankKalis Moderator

    I might be wrong, but I don't think there is a tool that is capable of analysing dependencies in two db's. <br /><br />...but I wouldn't mind to be wrong on this. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  9. Madhivanan Moderator

    Run this in each database and copy the result back to query Analyser and run them one by one

    Select Table_Catalog +'..sp_depends '''+table_name+'''' from information_Schema.tables


    Madhivanan

    Failing to plan is Planning to fail

Share This Page