查看某个对象在哪些库中存在

USE Master
GO

/**********************************************************
**ServerName:         N/A
**DatabaseNAME:   Master
**ObjectName:         up_SearchObject
**Function:                查找某个对象所在的数据库和表,对象为除了索引之外的任何对象，包括表，视图，存储过程                                等,若不存在，返回无结果
**Author:                    SQLServer_2005
**Date:                       2006.9.7
**Version:                  V1.0
*********************************************************/

ALTER PROCEDURE dbo.up_SearchObject
@ObjectName sysname
AS
EXEC('CREATE TABLE Temp..#(
                            ID int identity(1,1),
                            ObjectName sysname,
       ObjectType varchar(30),
       CreateTime datetime,
       DatabaseName sysname,
       TableName varchar(256)
                           )
 INSERT INTO Temp..#(ObjectName,ObjectType,CreateTime,DatabaseName,TableName) EXEC SP_MSFOREACHDB
@COMMAND1=''IF EXISTS(
                      SELECT 1
                      FROM ?..sysobjects
                      WHERE name = '''''+@ObjectName+'''''
                    )
           BEGIN       
            SELECT name AS ObjectName,
                            CASE WHEN xtype = ''''C'''' THEN ''''Check''''
                                 WHEN xtype = ''''D'''' THEN ''''Default''''
                                 WHEN xtype = ''''F'''' THEN ''''Foreign Key''''
                                 WHEN xtype = ''''L'''' THEN ''''Log''''
                                 WHEN xtype = ''''FN'''' THEN '''' Function''''
                                 WHEN xtype = ''''IN'''' THEN '''' Inner Talbe Function''''
                                 WHEN xtype = ''''P'''' THEN '''' Procedure''''
                                 WHEN xtype = ''''PK'''' THEN '''' Primary Key''''
                                 WHEN xtype = ''''RF'''' THEN '''' Replication Procedure''''
                                 WHEN xtype = ''''S'''' THEN '''' System Table''''
                                 WHEN xtype = ''''TF'''' THEN '''' Table Function''''
                                 WHEN xtype = ''''TR'''' THEN '''' Trigger''''
                                 WHEN xtype = ''''U'''' THEN '''' User Table''''
                                 WHEN xtype = ''''UQ'''' THEN '''' Unique''''
                                 WHEN xtype = ''''V'''' THEN '''' View''''
                                 WHEN xtype = ''''X'''' THEN '''' Extend Procedure''''
                                 ELSE ''''Unknown''''
                            END AS ObjectType,
                            CONVERT(VARCHAR(20),crdate,120) AS CreateTime,
                            ''''?'''' AS DatabaseName,
                            TableName=ISNULL((SELECT TOP 1 name FROM ?..sysobjects WHERE id = obj.parent_obj),''''N/A'''')
                FROM ?..sysobjects obj
                WHERE NAME = '''''+@ObjectName+'''''
           END''

    SELECT ID,ObjectName,ObjectType,CreateTime,DatabaseName,TableName
    FROM Temp..#

           TRUNCATE TABLE Temp..#
           DROP TABLE Temp..#
')

/**调用
EXEC up_SearchObject 'T1'
**/