-- ============================================= -- Author: <Author,,FWM> -- Create date: <Create Date,,20190830> -- Description: <Description,,根据最后修改时间查找存储过程或函数> -- ============================================= ALTER PROCEDURE [dbo].[SearchProcOrFuncByModifyDateProc] @minDate DATETIME, @maxDate DATETIME, @dbs NVARCHAR(MAX), @orders NVARCHAR(MAX) AS BEGIN SET NOCOUNT ON; IF @minDate IS NULL AND @maxDate IS NULL BEGIN PRINT N'@minDate 跟 @maxDate 参数至少需要提供一个'; RETURN; END; DECLARE @tempXML XML; DECLARE @dbTbl TABLE (dbname NVARCHAR(MAX)); IF ISNULL(@dbs, N'') = N'' BEGIN INSERT @dbTbl (dbname) SELECT name FROM sys.sysdatabases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb', 'OldCustomerBak'); END; ELSE BEGIN SET @tempXML = N'<v>' + REPLACE(@dbs, N' ', N'</v><v>') + N'</v>'; INSERT @dbTbl (dbname) SELECT t.val FROM (SELECT t.c.value(N'.', N'NVARCHAR(MAX)') val FROM @tempXML.nodes(N'/v') t(c) ) t WHERE t.val <> N''; IF NOT EXISTS (SELECT 1 FROM @dbTbl) BEGIN PRINT N'请输入需要查找的数据库'; RETURN; END; END; DECLARE @sql NVARCHAR(MAX), @condition NVARCHAR(MAX); SET @condition = CASE WHEN @minDate IS NOT NULL THEN N' AND o.modify_date >= ''' + CONVERT(NCHAR(19), @minDate, 120) + N'''' ELSE N'' END + CASE WHEN @maxDate IS NOT NULL THEN N' AND o.modify_date <= ''' + CONVERT(NCHAR(19), @maxDate, 120) + N'''' ELSE N'' END; SET @condition = RIGHT(@condition, LEN(@condition) - LEN(N' AND')); SET @sql = (SELECT N'SELECT DISTINCT ''' + t.dbname + N''' dbname, so.name, so.xtype, o.modify_date modifyDate FROM ' + t.dbname + N'.sys.sysobjects so INNER JOIN ' + t.dbname + N'.sys.objects o ON o.object_id = so.id WHERE' + @condition + N' UNION ALL ' FROM @dbTbl t FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)'); SET @sql = LEFT(@sql, LEN(@sql) - LEN(N' UNION ALL ')) + N' ORDER BY ' + CASE WHEN ISNULL(@orders, N'') = N'' THEN N'modifyDate DESC' ELSE @orders END; EXEC (@sql); END;