Suppose, if your SQL Server having more than 10000 databases and you got a challenge to find a particular object (proc/function/table/view..etc) is in which database?
Either we have to login into each Database and check the sysobjects
or we can run a single query in master db which can query all the DB's, search what you want and bring that you...later one is the better option because it`s save the timing also...)
Use below query to find the result
Exec sp_MSforeachdb 'if EXISTS (select * from ?..sysobjects where name like ''Prods'')
Begin
Select name from sysdatabases where name like ''?''
END'
---You just need to replace "ProdsTable" with your required object name and you can very well add more conditions to that query...
Either we have to login into each Database and check the sysobjects
or we can run a single query in master db which can query all the DB's, search what you want and bring that you...later one is the better option because it`s save the timing also...)
Use below query to find the result
Exec sp_MSforeachdb 'if EXISTS (select * from ?..sysobjects where name like ''Prods'')
Begin
Select name from sysdatabases where name like ''?''
END'
---You just need to replace "ProdsTable" with your required object name and you can very well add more conditions to that query...
No comments:
Post a Comment