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