Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Query to find table?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chriskhan2000
Aged Yak Warrior

USA
544 Posts

Posted - 01/13/2005 :  11:06:18  Show Profile  Reply with Quote
Anyone might have an idea or know how to find a specific table from various databases? I have about 20 databases that I want to find a specific table, but I don't want to have to go through every database and search for it. I know the table name, just not where it is located.

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 01/13/2005 :  11:16:21  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
Select * from <dbName>.dbo.sysobjects where name = '<tablename>'



Corey
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 01/13/2005 :  11:45:14  Show Profile  Reply with Quote
Yes, but they'd have to do one at a time...


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




Brett

8-)
Go to Top of Page

Chester
Starting Member

USA
27 Posts

Posted - 01/13/2005 :  13:52:26  Show Profile  Reply with Quote
Something like this:

Exec sp_MSforeachdb
'Select ''?'' as Dbname, * From ?..sysobjects where name like ''Table_name%'''
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000