分享:sp_get_object 实例下查找指定对象名-优快云博客

本文介绍了一个SQL Server的存储过程，用于查询指定名称的数据库对象。该过程支持跨数据库搜索，并可根据需求限制搜索范围至当前数据库。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

IF OBJECT_ID('sp_get_object', 'P') IS NOT NULL 
    DROP PROCEDURE sp_get_object  
go   
CREATE PROCEDURE sp_get_object
    ( @objectname NVARCHAR(128) = '' ,
      @is_current_db CHAR(1) = 'N'  --是否当前数据库
	)
AS 
--  作者:陈恩辉-弘恩   
    SET NOCOUNT ON ;  
    IF @is_current_db = 'Y'   
        SELECT  DB_NAME() AS db_name ,
                type ,
                name
        FROM    sys.objects
        WHERE   name LIKE @objectname + '%' 
    ELSE 
        BEGIN   
            SELECT TOP 0
                    DB_NAME() AS db_name ,
                    type ,
                    name
            INTO    #sp_get_object
            FROM    sys.objects  
            DECLARE @sql NVARCHAR(MAX) ,@sql_exec NVARCHAR(1000) ,@new_line CHAR(2) 
            SELECT  @sql = '' ,@new_line = CHAR(13) + CHAR(10),
             @sql_exec =  @new_line+ 'USE [@dbname] ' +@new_line  
                        +'INSERT INTO #sp_get_object ( db_name,type ,name ) ' +@new_line    
                        +'SELECT DB_NAME() AS db_name, type ,name ' + @new_line
                        +'FROM    sys.objects ' +@new_line
                        +'where name like '''   
            SELECT  @sql = @sql + REPLACE( @sql_exec + @objectname + '%'' ', '@dbname',name)
            FROM    sys.databases   
            PRINT @sql  
            EXEC sp_executesql @sql   
            SELECT  db_name ,
                    type ,
                    name
            FROM    #sp_get_object  
            DROP TABLE #sp_get_object       
        END  
GO  
EXEC sp_MS_marksystemobject 'sp_get_object'  
GO