在MicrosoftSQLServerManagementStudio 2005中,如何查找表所在的数据库,以及您知道其名称(例如dbo.mytable1)?

您可以使用sp_MSforeacheachdb。

EXEC sp_MSForEachDB 'USE [?] IF OBJECT_ID(''dbo.mytable'') IS NOT NULL PRINT ''?'''

使用主机

声明@db_name varchar(128) 声明@TableName varchar(30)

选择@TableName=''

[DbName][varchar](128)不为空, [TableName][varchar](128)非空)

SELECT  name, DbID 
FROM    master..sysdatabases
WHERE   name NOT IN ('northwind', 'pubs')
AND     (status & 32) <> 32        --loading.
AND (status & 64) <> 64        --pre recovery.
AND (status & 128) <> 128      --recovering.
AND (status & 256) <> 256      --not recovered.
AND (status & 512) <> 512      --Offline
AND (status & 32768) <> 32768  --emergency mode.
AND     DbID > 4

打开db_光标

而@@FETCH\u STATUS=0

set @sql_string = ''
+'  Insert into #tblDatabaseName '
+'  select  ''' + @db_name + ''' as ''DbName'',  '
+'          o.name as ''TableName''  '
+'  from               [' + @db_name + ']..sysobjects   o with(nolock)  '
+'  where   o.name like ''' + @TableName + '''  ' 

execute sp_executesql @sql_string

fetch next from db_cursor into @db_name, @DbID

取消分配db_游标

从#tblDatabaseName中选择*

删除表#tblDatabaseName

EXEC sp_MSForEachDB 'USE [?] IF EXISTS(SELECT * FROM Sys.Objects WHERE Type = ''U'' AND Name = ''Product'') PRINT ''?'''