[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 509
  • Last Modified:

Search all databases for a specific table

Does anyone know how to search all databases on a server for a specific table name?
0
khooc
Asked:
khooc
  • 4
  • 3
  • 2
  • +2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
EXEC sp_MSForEachDb "IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'putUrTableNameHere') SELECT '?' AS DBName "
0
 
HuyBDCommented:
exec sp_tables  @table_name = 'tablename'
0
 
khoocAuthor Commented:
Hi aneeshattingal, I get this error

Invalid object name '#SQLOLEDbUserProfile'.

Is there a way around this?

I should also mention that we're working in a SQL Server 6.5 environment.  Do you think that might be the reason why?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
QPRCommented:
I tried aneeshattingal suggestion and QA just said The command(s) completed successfully without returning anything.
I also tried.....
exec sp_tables  @table_name = 'pubs' and got an empty result set
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
If you are searching for some systemtables My first query wont work, you can use the following

EXEC sp_MSForEachDb "IF EXISTS (SELECT * FROM sysobjects WHERE ID = OBJECT_ID('syscolumns')) SELECT '?' AS DBName "

If you search for usertables both the Queries will work, you just need to put  the table name


0
 
QPRCommented:
oops I entered a DB name :>
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
khooc,
> I should also mention that we're working in a SQL Server 6.5 environment
use this , replace syscolumns with your table name

EXEC sp_MSForEachDb "IF EXISTS (SELECT * FROM sysobjects WHERE ID = OBJECT_ID('syscolumns')) SELECT '?' AS DBName "
0
 
LowfatspreadCommented:
>i didn't the ForEach stored procedures where available in releases prior to SQL 2K

you can do this (i'm hoping sp_tables was in 6.5)

set nocount on
drop table #temp
drop table ##t
declare @r int
declare @max int
declare @db sysname
declare @SQL varchar(8000)
declare @TBNAME sysname
declare @TBOWNER SYSNAME
Set @TBNAME='YourTableName'
Set @TBOWNER='dbo'

Create table ##T (Dbname sysname,owner sysname,TBName sysname,TbType Varchar(32),Remarks varchar(254),primary key(dbname,owner,tbname))

select name,identity(int,1,1) as r into #temp from master.dbo.sysdatabases
select @max=@@ROWCOUNT,@r=1
while @r <= @max
begin
 select @DB=Name ,@r=r+1 from #temp where r = @r
 Set @sql='Use '+@db+' Insert into ##T EXEC sp_tables '+@tbname+','+@tbowner+', NULL ,"''SYSTEM TABLE''"'
 print @sql
 exec(@SQL)
end
Select * from ##T

0
 
khoocAuthor Commented:
Still getting this error

Msg 208, Level 16, State 1
Invalid object name '#SQLOLEDbUserProfile'.

Anyone know what's this about?
0
 
LowfatspreadCommented:
what are you using to connect?
how are you testing the code?

can you post the code and show where the error occurs?
0
 
khoocAuthor Commented:
Apologies, I'm using this code:

EXEC sp_MSForEachDb "IF EXISTS (SELECT * FROM sysobjects WHERE ID = OBJECT_ID('syscolumns')) SELECT '?' AS DBName "

The one you provided doesn't work at all.  For example, the 'set' command doesn't work in 6.5, I have to replace it with 'select'.  I spent some time trying to fix it, but plenty of errors keep coming up... and I don't know SQL syntax that well.
0
 
khoocAuthor Commented:
Hmmm, since anyone isn't too keen :-(

I had a look into it myself and this is the query that worked with me (at home, I'm using 2005 Express).

EXEC sp_MSForEachDb "IF EXISTS(SELECT * FROM ?..sysobjects WHERE name='tablename' AND xtype='U') SELECT '?' AS DBName"

... but thanks for the help aneeshattingal - unfortunately, it doesn't work with SQL Server 6.5.

Change xtype according to what you're searching for.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now