Searching a table in several databases
How can I figure out a t开发者_运维知识库able (say tbl_mytable) lies in which database?
If you're using SQL Server 2005 or above and this is an adhoc thing, rather than a programmatic thing, you can try SQL Search by Red Gate. It's a free Management Studio plugin.
Edit: Programmatically, you can do it via sp_MSForEachDB which is an undocumented system procedure - so, functionality is not guaranteed to behave the same or even exist between releases (i.e. don't use in production code).
EXECUTE sp_msforeachDB 'IF EXISTS(
Select 1 From [?].INFORMATION_SCHEMA.Tables where TABLE_NAME = ''tbl_mytable'')
PRINT ''?'''
This will print a list of database names which contain the table.
I have got partial solution like this:
Select Table_Name From DatabaseName.INFORMATION_SCHEMA.Tables where TABLE_NAME like 'tbl_Mytable'
But the above query will search the table in only one database.
精彩评论