x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

Search SQL Server for a table in any databse

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

Print | posted on Thursday, January 13, 2005 12:03 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# 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.
1/13/2005 2:21 PM | Justin Pitts
Gravatar

# 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.
1/13/2005 2:38 PM | Brett
Gravatar

# 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.
1/13/2005 5:28 PM | Justin Pitts
Gravatar

# 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.
1/13/2005 8:13 PM | Tara
Gravatar

# 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.
1/18/2005 4:31 PM | Adam Machanic
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET