分享:sp_get_object_definition查找所有数据库下面哪些对象定义语句中包括想要查找的字符串...

最新推荐文章于 2024-11-06 11:17:40 发布

iteye_14608

最新推荐文章于 2024-11-06 11:17:40 发布

阅读量101

点赞数

文章标签：数据库

USE master 
go 
IF OBJECT_ID( 'sp_get_object_definition','P') IS NOT NULL 
DROP PROCEDURE sp_get_object_definition
go 
CREATE PROC sp_get_object_definition ( @char NVARCHAR(max) , @db NVARCHAR(128) = '' )
AS
-- 作者:陈恩辉-弘恩 
-- 查找所有数据库下面对象定义中包括想要查找的字符串
SET NOCOUNT ON ;
SELECT TOP 0 DB_NAME() AS DB_NAME , o.name,o.type ,s.definition  
INTO #sp_get_object_definition
FROM sys.objects o ,sys.sql_modules s

DECLARE @sql NVARCHAR(max) 
SET @sql = ''
SELECT @sql = @sql + REPLACE (
'
USE @dbname 
insert into #sp_get_object_definition

SELECT DB_NAME() AS DB_NAME , o.name,o.type ,s.definition  
FROM sys.objects o ,sys.sql_modules s
WHERE o.object_id = s.object_id AND s.definition LIKE ''%'+ @char + '%''

' ,'@dbname',name)  FROM sys.databases 
WHERE name LIKE  @db + '%'
PRINT @sql
EXEC sp_executesql @sql
SELECT  DB_NAME ,
        name ,
        type ,
        definition
FROM    #sp_get_object_definition ;
DROP TABLE #sp_get_object_definition ;
go 

EXEC sp_MS_marksystemobject 'sp_get_object_definition'
go