SQLTeam.com | Weblogs | Forums

Find Database and Server name using a table name


#1

Hello all,

Does anyone know a query I can use to find what database and server by using a table name?

Thanks all


#2

Dunno if these will help?

SELECT  [Server Property] = 'MachineName', [Value] = SERVERPROPERTY('MachineName')
UNION SELECT 'InstanceName', SERVERPROPERTY( 'InstanceName' )
UNION SELECT 'ServerName', SERVERPROPERTY( 'ServerName' )
UNION SELECT '@@SERVERNAME', @@SERVERNAME
UNION SELECT 'DB_Name()', DB_Name()
UNION SELECT 'host_name()', host_name()

-- Available databases
SELECT name
FROM master.sys.databases
ORDER BY name

#3

@Mel,
In SSMS under Regisered Servers add a Local Server Group, Register the Servers you want to query under the server group. Right click the server group and select New Query and type:

DECLARE @tbl_name nvarchar(128)='''tblWareHouse''';
DECLARE @sql nvarchar(2000) =  N'USE ?
SELECT db_name() from sys.tables where is_ms_shipped=0 and name = '+@tbl_name;
EXEC sys.sp_MSforeachdb @command1 = N'USE ?
SELECT db_name() from sys.tables where is_ms_shipped=0 and name = ''tblWarehouse''', -- nvarchar(2000)
    @replacechar = N'?';

Replace tblWareHouse with your table name.
Note, this requires SQL Server 2008 or greater.