select all databases in sysobjects that have a table named 'mytable' regardless of schema?

Question

For a given sql 2000 - 2008 server I want to find any table named dbo.[MyTable] on that server. In addition, how do I find all databases that have a table named [dbo].[MyTable] and [AnySchemaName].[MyTable]. Is there a simple sp_ command like spTables MyTable? Or 'sp_AllDatabaseTable [MyTable]'?

I want to print it out like:

ServerName  Database  SchemaName  MyTable   Date Created
----------- --------- ----------- --------- -------------

Thx

Answer 1

Of course, you can use sp_msforeachdb for this purpose but you need to remember that fhis function is neither documented nor officially supported. Also, sometimes it breaks. More about it here Making a more reliable and flexible sp_MSforeachdb

You can use this script to search table by name in all databases. I took it from Find table in every database of SQL server

DECLARE @TableName VARCHAR(256)
SET @TableName='YOUR_TABLE_NAME'
DECLARE @DBName VARCHAR(256)
DECLARE @varSQL VARCHAR(512)
DECLARE @getDBName CURSOR
SET @getDBName = CURSOR FOR
SELECT name
FROM sys.databases
CREATE TABLE #TmpTable (DBName VARCHAR(256),
SchemaName VARCHAR(256),
TableName VARCHAR(256),
create_date date, modify_date date)
OPEN @getDBName
FETCH NEXT
FROM @getDBName INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @varSQL = 'USE ' + @DBName + ';
INSERT INTO #TmpTable
SELECT '''+ @DBName + ''' AS DBName,
SCHEMA_NAME(schema_id) AS SchemaName,
name AS TableName,
create_date, modify_date
FROM sys.tables
WHERE name LIKE ''%' + @TableName + '%''' --WHERE name = '' + @TableName + ''' /* if you want to search by exact table name*/ 
EXEC (@varSQL)
FETCH NEXT
FROM @getDBName INTO @DBName
END
CLOSE @getDBName
DEALLOCATE @getDBName
SELECT *
FROM #TmpTable
DROP TABLE #TmpTable

Also, you may want to read this Find table name in all objects of all databases