Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
niranjankumark
Posting Yak Master
164 Posts |
 Posted - 2009-04-26 : 09:30:40
|
| My table is used in one SP of pubs database, by using SP_DEPENDS I can find the dependencies , but same table is refererred in Nortwind database SP( but not created just called as pubs.table ) .. so if i give table name , it has to find across all database for dependencies. |
|
|
svicky9
Posting Yak Master
232 Posts |
Posted - 2009-04-27 : 08:29:10
|
| I generally use Sycomments to get the object Dependencies..In your scenarion if the column name is called Col1..You can use something like below and then execute the statements. you can also expand it in such a way to looping use Cursors(Which I dont recommend) and then insert the results into a table.SELECT 'USE ' + name + ' select ''' + name + ''' as DatabaseName,object_name(sysobjects.id) as Objectname ,sysobjects.Xtype as type from syscomments join syscomments on on syscomments.id =sysobjects.id where syscomments.text like ''%Col1%'''FROM sys.databaseshttp://www.sqlserver007.com |
 |
|
|
niranjankumark
Posting Yak Master
164 Posts |
Posted - 2009-04-27 : 11:09:24
|
| It is displaying script .. for test i try to execute that one script it throws errorThe column prefix 'sysobjects' does not match with a table name or alias name used in the query. |
|
|
|
svicky9
Posting Yak Master
232 Posts |
Posted - 2009-04-27 : 12:46:27
|
| SELECT 'USE ' + name + ' select ''' + name+ ''' as DatabaseName,object_name(sysobjects.id) as Objectname ,sysobjects.Xtype as type from syscomments join sysobjects on syscomments.id =sysobjects.id where syscomments.text like ''%Col1%'''FROM sys.databaseshttp://www.sqlserver007.com |
|
|
|
|
|
|
Tables across database