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
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
Red gate has recently released a tool called SQL Dependency Viewer. it is free tool and it displays the referred tables in a diagramatically. sql diagnostic manager of IDERA also has this option ---------------------------------------- Cast your vote http://www.geocities.com/dineshasanka/sqlserver05.html http://spaces.msn.com/members/dineshasanka
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
quote:Originally posted by SQL2000DBA 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. Did you try the red gate new tool ---------------------------------------- Cast your vote http://www.geocities.com/dineshasanka/sqlserver05.html http://spaces.msn.com/members/dineshasanka
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
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 />
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
