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

 use master

goIF 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