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

最新推荐文章于 2024-12-25 17:08:27 发布

原创最新推荐文章于 2024-12-25 17:08:27 发布 · 置顶 · 790 阅读

CC 4.0 BY-SA版权

本文介绍了一个用于查询SQL Server中特定类型对象的存储过程。该过程可根据输入参数搜索指定数据库或所有数据库中的对象，并返回对象名称、类型及所在数据库等信息。

 
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) = '' ,  
      @typeInput varchar(8) = 'U',
	  @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 + '%' and type like @typeInput +'%'
    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 type like ''' +  @typeInput +'%'''
                        +' and   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  ,
					case when type = 'U' then 'select top 10 * from '+db_name+'..['+name+']' else '' end  as _sql
            FROM    #sp_get_object    
            DROP TABLE #sp_get_object         
        END    
GO    
EXEC sp_MS_marksystemobject 'sp_get_object'    
GO