SQL Server Forums
Profile | Register | 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?
 New Topic  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  
 New 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.11 seconds. Powered By: Snitz Forums 2000