Fully qualified name of a table

Hi all,
How can I find the fully qualified name of a table, if all I have is the
table name
I realise that this might be a bit ambigious, but I was wondering.
For example, if I had a table name like employees. It could be in northwind,
or in corporate or both
is there a way to find this out
Thanks
RobertNo easy way, since the object names are not contained in one single table. T
he object names are
contained in a system table or a view inside each database. But you could cr
eate a stored procedure
in which you use a cursor to loop each database and construct dynamic SQL to
pass such a query
against that system table or view. Or you could cheat and use an undocumente
d stored procedure that
dopes just that:
EXEC sp_MSForeachdb 'SELECT * FROM ?.INFORMATION_SCHEMA.TABLES WHERE TABLE_N
AME = ''authors'''
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert Bravery" <me@.u.com> wrote in message news:OmHh35UmGHA.1404@.TK2MSFTNGP05.phx.gbl...[
color=darkred]
> Hi all,
> How can I find the fully qualified name of a table, if all I have is the
> table name
> I realise that this might be a bit ambigious, but I was wondering.
> For example, if I had a table name like employees. It could be in northwin
d,
> or in corporate or both
> is there a way to find this out
> Thanks
> Robert
>[/color]|||> How can I find the fully qualified name of a table, if all I have is the
> table name
If all you have is a table name, then you could get 1 or 20 answers (even
within the same database, because you could have dbo.Employees and
someOtherUser.Employees).
Now you are talking about going across databases, that's even more potential
answers. Here is how to find all the tables named 'Employees' in the
databases Northwind and Corporate:
SELECT '[' + TABLE_CATALOG + '].[ + TABLE_SCHEMA + '].[' + TABLE_NAME
FROM NorthWind.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Employees'
AND TABLE_TYPE = 'BASE_TABLE'
UNION
SELECT '[' + TABLE_CATALOG + '].[ + TABLE_SCHEMA + '].[' + TABLE_NAME
FROM Corporate.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Employees'
AND TABLE_TYPE = 'BASE_TABLE';|||You might try something like:
sp_msforeachdb 'select ''?.'' + rtrim(table_schema) + ''.'' +
rtrim(table_name)
from ?.information_schema.tables where table_name = ''employees'''
--
If you are looking for SQL Server examples check out my Website at
http://www.geocities.com/sqlserverexamples
"Robert Bravery" wrote:

> Hi all,
> How can I find the fully qualified name of a table, if all I have is the
> table name
> I realise that this might be a bit ambigious, but I was wondering.
> For example, if I had a table name like employees. It could be in northwin
d,
> or in corporate or both
> is there a way to find this out
> Thanks
> Robert
>
>|||Try,
create table #t (
tc sysname,
ts sysname,
tn sysname
)
declare @.sql nvarchar(4000)
declare @.tc sysname
declare my_cursor cursor
local
fast_forward
for
select [name]
from master.dbo.sysdatabases
where [name] not in ('master', 'tempdb', 'msdb', 'model')
open my_cursor
while 1 = 1
begin
fetch next from my_cursor into @.tc
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'use [' + @.tc + ']; select TABLE_CATALOG, TABLE_SCHEMA,
TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = ''BASE TABLE'''
insert into #t
exec sp_executesql @.sql
end
close my_cursor
deallocate my_cursor
select
*
from
#t as a
where
exists (
select
*
from
#t as b
where
b.ts = a.ts
and b.tn = a.tn
and b.tc != a.tc
)
order by
tn,
tc,
ts
drop table #t
go
AMB
"Robert Bravery" wrote:

> Hi all,
> How can I find the fully qualified name of a table, if all I have is the
> table name
> I realise that this might be a bit ambigious, but I was wondering.
> For example, if I had a table name like employees. It could be in northwin
d,
> or in corporate or both
> is there a way to find this out
> Thanks
> Robert
>
>