Find a table when we dont know in which DB it is

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

All Forums

General SQL Server Forums

New to SQL Server Programming

Find a table when we dont know in which DB it is

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2006-11-14 : 00:00:00

Hi Friends,
is it possible to find a table in which database it is?
ex: i have one table name rider. i've created it in one database, but i dont know in which database it is.but i know the server name.

is it possible to find like this?

thank you very much.

Vinod

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-14 : 00:01:47

run this for each DB

select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'rider'

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2006-11-14 : 00:15:57

Thank you Khtan.i've this idea but is there any other go instead of searching like this? is anywhere in master or msdb the name will be? or that is the only way to find?

Vinod

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-14 : 00:25:51

This is one more way to save the trouble of looping through the databases (careful, it uses undocumented sp)

sp_msforeachdb 'select * from ?.information_schema.tables where table_name = ''rider'''

Harsh Athalye
India.
"Nothing is Impossible"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-14 : 00:51:26

"is anywhere in master or msdb the name will be?"
table information is not stored in master or msdb. It is in sysobjects of individual databases.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-14 : 05:35:47

[code]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FindTables]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[FindTables]
GO
CREATE Proc FindTables
(
@pTableName VarChar(800) -- Variable used for finding the table name
)
As

Declare @QryString nVarchar(4000) -- Variable will be used for the dynamic query
Declare @DbName Varchar(800) -- Variable for storing the database Name

create table #TblDataBase
(DbName varchar(1000) )

select @DbName = ''
while @DbName < (select max(name) from master..sysdatabases)
begin
select @DbName = min(name) from master..sysdatabases where [Name] > @DbName
select @QryString = 'select ''' + @DbName + ''' where exists (select * From [' + @DbName + ']..SysObjects Where [Name] Like ''%'
+ @pTableName + '%'' and xtype = ''U'')'
insert #TblDataBase exec (@QryString)
End
Select Dbname As 'DataBase Name ' From #TblDataBase

GO
FindTables 'Employee'
[/code]

Chirag

http://chirikworld.blogspot.com/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-14 : 06:14:13

Good work, Chirag!!

Just a couple of enhancements!

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FindTables]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[FindTables]
GO
CREATE Proc dbo.FindTables
(
	@pTableName VarChar(800) -- Variable used for finding the table name
)
As

	Declare @QryString nVarchar(4000) -- Variable will be used for the dynamic query
	Declare @DbName Varchar(800) -- Variable for storing the database Name

	create table #TblDataBase
	(DbName varchar(1000) )

	select @DbName = ''
	while @DbName < (select max(name) from master..sysdatabases)
	begin
	select @DbName = min(name) from master..sysdatabases where [Name] > @DbName
	     if has_dbaccess(@DbName) = 1	-- to ensure only those dbs which current user has access to will be checked
	     begin
		     select @QryString = 'select ''' + @DbName + ''' where exists (select * From [' + @DbName  + ']..SysObjects Where [Name] Like ''%' 
		     + @pTableName + '%'' and xtype = ''U'')'
		     insert #TblDataBase exec (@QryString)
	     end
	End
	Select Dbname As 'DataBase Name ' From #TblDataBase

GO
FindTables 'Employees'

Harsh Athalye
India.
"Nothing is Impossible"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-14 : 06:17:33

Thanks for the Feedback.. :-)

Chirag

http://chirikworld.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-14 : 09:54:38

or run this in QA and copy the result back to QA and run

Select 'select * from '+name+'.INFORMATION_SCHEMA.TABLES where TABLE_NAME = ''rider''' from master..sysdatabases

(Not tested)

Madhivanan

Failing to plan is Planning to fail

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2006-11-15 : 01:57:14

Great Knowledge
and great support friends.

thanks a lot.

Vinod

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources