Search SQL Server for a table in any databse

Thu Jan 13, 2005 by Brett Kaiser (x002548) in sql-server

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

Legacy Comments


Justin Pitts 2005-01-13	re: Search SQL Server for a table in any databse I'm confused. You don't have lots of databases per server instance? I don't have a single SQL Server that has just 1 database.

Brett 2005-01-13	re: Search SQL Server for a table in any databse We usually have 1 Application database per Business Group. They may be many applications, but it's one database. I have several servers I've inherited that have several, and I guess one has 20 or more, but they aren't heavy OLTP. It's just a bad design, resource wise.

Justin Pitts 2005-01-13	re: Search SQL Server for a table in any databse >>"It's just a bad design, resource wise" Why? The database engine doesn't have problems with large numbers of databases. Its actually kinda made to handle that case rather well. I'm not getting into the logical design question here. The only thing I am questioning is the assertion that it is detrimental to the engine to co-locate multiple databases in it's care.

Tara 2005-01-13	re: Search SQL Server for a table in any databse The main reason to split databases off into their instances is that when you need to restart the service, you won't be impacting the databases of the other instances. So let's say you have 20 databases. Maybe you logically split them up into 4 instances due to business units. So you've got 5 databases on 4 instances. Now you've found a problem that requires a restart of the MSSQL$InstanceName service. You only need to notify the 5 databases' users rather than the 20 for the server. We don't split our databases like this except on the clustered environments. Where we do use multiple instances on our non-clustered environments is that we can run multiple versions of a database on one server and not need to change the database names inside one instance.

Adam Machanic 2005-01-18	re: Search SQL Server for a table in any databse My favorite question I see asked all too often is, "how do I search all of my databases for a certain string"? (usually phrased in slightly more broken English than that :D ) -- gotta love these guys who have NO CLUE what's in their database.