I've seen recently a lot of people with quite a proliferation of databases on a single server. I'm not sure why this is, but it may be a “personalized” database per client, so each client has their own replica of a database for an application. If that's the case, then I would say those are bad designs. If it's not, then I don't know. It would just be a maint. nightmare. In any event, someone finally asked:
“Anyone might have an idea or know how to find a specific table from various databases? I have about 20 database”
In the following Post:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=44682
EDIT: Well Chester (http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=10121) has a simpler solution. It just comes out as many result sets, and you can't interogate it...but it is elegant
Exec sp_MSforeachdb
'Select ''?'' as Dbname, * From ?.INFORMATION_SCHEMA.Tables where TABLE_NAME like ''Orders%'''
Well...here you go:
USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE myTable99(TABLE_CATALOG sysname, TABLE_SCHEMA sysname, TABLE_NAME sysname, TABLE_TYPE varchar(50))
GO
CREATE PROC usp_FindMyTable (@TABLE_NAME sysname = null)
AS
SET NOCOUNT ON
TRUNCATE TABLE myTable99
IF @TABLE_NAME IS NULL
BEGIN
PRINT 'No Table to look for. Please supply a tabke name. Like: ' + CHAR(13)
+ ' EXEC usp_FindMyTable Orders'
GOTO usp_FindMyTable_Exit
END
DECLARE @MAX_dbname sysname, @dbname sysname, @sql varchar(8000)
SELECT @MAX_dbname = MAX([name]), @dbname = MIN([name]) FROM master..sysdatabases
WHILE @dbname < = @MAX_dbname
BEGIN
SELECT @sql = 'SET NOCOUNT ON ' + CHAR(13)
+ 'INSERT INTO myTable99(TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE)' + CHAR(13)
+ 'SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE ' + CHAR(13)
+ 'FROM ' + @dbname + '.INFORMATION_SCHEMA.Tables' + CHAR(13)
+ 'WHERE TABLE_NAME LIKE ''' + @TABLE_NAME + '%' + '''' + CHAR(13)
-- SELECT @sql
EXEC(@sql)
SELECT @dbname = MIN([name]) FROM master..sysdatabases WHERE [name] > @dbname
END
SELECT * FROM myTable99
usp_FindMyTable_Exit:
SET NOCOUNT OFF
RETURN
GO
EXEC usp_FindMyTable
EXEC usp_FindMyTable Orders
GO
SET NOCOUNT OFF
DROP PROC usp_FindMyTable
DROP TABLE myTable99
GO