分享:sp_get_object 实例下查找指定对象名

最新推荐文章于 2025-12-04 14:41:53 发布

最新推荐文章于 2025-12-04 14:41:53 发布 · 98 阅读

文章标签：

USE master 
go 
IF OBJECT_ID( 'sp_get_object' ,'P') IS NOT NULL 
DROP PROCEDURE sp_get_object
go 
CREATE PROCEDURE sp_get_object
    (
      @objectname NVARCHAR(128) = '',
      @db TINYINT = 0
    )
AS 
--  作者:陈恩辉-弘恩 
	SET NOCOUNT ON ;

    IF @db = 1 
        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) 
            SET @sql = ''
            SELECT  @sql = @sql 
						+ REPLACE('
						USE @dbname 
						INSERT INTO #sp_get_object (  db_name,type ,name )   
						SELECT DB_NAME() AS db_name, type ,name 
						FROM    sys.objects 
						where name like ''' + @objectname + ''' ', '@dbname', name)
            FROM    sys.databases
            --WHERE   database_id >= 5 ; -- 非系统数据库
            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