Prev: sp_who2 and system background processes
Next: Question on SET COMPATIBILITY_LEVEL
From: Pingx on 7 Apr 2010 21:41
Dear SQL Server Export,
How do I find a certain table in sql server assuming I do not know the name
of the database. sysobjects does not work. and INFORMATION_SCHEMA.TABLES does
not work either. You have to know the db name in both cases.


From: Tom Cooper on 7 Apr 2010 23:49
sp_msforeachdb 'Use [?]; If Exists(Select * From sys.tables Where name =
''MyTable'') Select ''table was found in the ? database''';

will show you every database where there is a table named MyTable. Please
note that sp_msforeachdb is an undocumented stored procedure and Microsoft
could change or remove it in future releases.

Tom

"Pingx" <Pingx(a)discussions.microsoft.com> wrote in message
news:0FC72149-89F1-4922-8B5B-534DBCF24919(a)microsoft.com...
> Dear SQL Server Export,
> How do I find a certain table in sql server assuming I do not know the
> name
> of the database. sysobjects does not work. and INFORMATION_SCHEMA.TABLES
> does
> not work either. You have to know the db name in both cases.
>
>

From: Uri Dimant on 8 Apr 2010 01:16
Hi
SELECT * FROM sys.tables

"Pingx" <Pingx(a)discussions.microsoft.com> wrote in message
news:0FC72149-89F1-4922-8B5B-534DBCF24919(a)microsoft.com...
> Dear SQL Server Export,
> How do I find a certain table in sql server assuming I do not know the
> name
> of the database. sysobjects does not work. and INFORMATION_SCHEMA.TABLES
> does
> not work either. You have to know the db name in both cases.
>
>


From: Pingx on 8 Apr 2010 13:18
sys.tables has the same data as the other two cases. you have to know the db
name first.

Pingx

"Uri Dimant" wrote:

> Hi
> SELECT * FROM sys.tables
>
> "Pingx" <Pingx(a)discussions.microsoft.com> wrote in message
> news:0FC72149-89F1-4922-8B5B-534DBCF24919(a)microsoft.com...
> > Dear SQL Server Export,
> > How do I find a certain table in sql server assuming I do not know the
> > name
> > of the database. sysobjects does not work. and INFORMATION_SCHEMA.TABLES
> > does
> > not work either. You have to know the db name in both cases.
> >
> >
>
>
> .
>
From: Pingx on 8 Apr 2010 13:19
Thanks Tom.
I'm surpprised that sql server does not have a table/view to list all the
tables instance wide.

Pingx

"Tom Cooper" wrote:

> sp_msforeachdb 'Use [?]; If Exists(Select * From sys.tables Where name =
> ''MyTable'') Select ''table was found in the ? database''';
>
> will show you every database where there is a table named MyTable. Please
> note that sp_msforeachdb is an undocumented stored procedure and Microsoft
> could change or remove it in future releases.
>
> Tom
>
> "Pingx" <Pingx(a)discussions.microsoft.com> wrote in message
> news:0FC72149-89F1-4922-8B5B-534DBCF24919(a)microsoft.com...
> > Dear SQL Server Export,
> > How do I find a certain table in sql server assuming I do not know the
> > name
> > of the database. sysobjects does not work. and INFORMATION_SCHEMA.TABLES
> > does
> > not work either. You have to know the db name in both cases.
> >
> >
>
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: sp_who2 and system background processes
Next: Question on SET COMPATIBILITY_LEVEL