生成多个和过滤的液滴 + 创建存储过程

发布于 2024-07-07 04:41:10 字数 543 浏览 8 评论 0原文

我有这个脚本：

select name,create_date,modify_date from sys.procedures order by modify_date desc

我可以看到最近修改了哪些程序。我将添加一个“其中modify_date > =” 我想使用一些系统存储过程，它将生成我： drop + 为（比方说 5 个匹配的）存储过程创建脚本

我可以以某种方式执行此操作吗？

谢谢

，好的。我有最终版本：

http://swooshcode。 blogspot.com/2008/10/generate-stored-procedures-scripts-for.html

你们帮了很多

忙，谢谢

原文

I have this script:

select name,create_date,modify_date from sys.procedures order by modify_date desc

I can see what procedures were modified lately.
I will add a "where modify_date >= "
And I'd like to use some system stored procedure, that will generate me :
drop + create scripts for the (let's say 5 matching) stored procedures

Can i do this somehow?

thanks

ok. i have the final version:

http://swooshcode.blogspot.com/2008/10/generate-stored-procedures-scripts-for.html

you guys helped a lot

thanks

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

触ぅ动初心 2024-07-14 04:41:11

这不太漂亮，但很有效。手动运行其输出或使用 sp_executesql 执行它。

SELECT OBJECT_DEFINITION(object_id), 'drop procedure [' + name + ']'
FROM   sys.procedures
WHERE modify_date >= @date

您还必须担心失去权利。

This ain't pretty, but it works. Run the output from it manually or execute it with sp_executesql.

SELECT OBJECT_DEFINITION(object_id), 'drop procedure [' + name + ']'
FROM   sys.procedures
WHERE modify_date >= @date

You will have to worry about lost rights as well.

回复收藏 0 原文

关于从前 2024-07-14 04:41:11

不需要游标（根据模式等需要进行修改）：

DECLARE @dt AS datetime
SET @dt = '10/1/2008'

DECLARE @sql AS varchar(max)

SELECT @sql = COALESCE(@sql, '')
    + '-- ' + o.name + CHAR(13) + CHAR(10)
    + 'DROP PROCEDURE ' + o.name + CHAR(13) + CHAR(10)
    + 'GO' + CHAR(13) + CHAR(10)
    + m.definition + CHAR(13) + CHAR(10)
    + 'GO' + CHAR(13) + CHAR(10)
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
    ON m.object_id = o.object_id
INNER JOIN sys.procedures AS p
    ON m.object_id = p.object_id
WHERE p.modify_date >= @dt

PRINT @sql -- or EXEC (@sql)

No cursor necessary (modify as desired for schemas, etc):

DECLARE @dt AS datetime
SET @dt = '10/1/2008'

DECLARE @sql AS varchar(max)

SELECT @sql = COALESCE(@sql, '')
    + '-- ' + o.name + CHAR(13) + CHAR(10)
    + 'DROP PROCEDURE ' + o.name + CHAR(13) + CHAR(10)
    + 'GO' + CHAR(13) + CHAR(10)
    + m.definition + CHAR(13) + CHAR(10)
    + 'GO' + CHAR(13) + CHAR(10)
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
    ON m.object_id = o.object_id
INNER JOIN sys.procedures AS p
    ON m.object_id = p.object_id
WHERE p.modify_date >= @dt

PRINT @sql -- or EXEC (@sql)

回复收藏 0 原文

就像说晚安 2024-07-14 04:41:11

您可以使用游标来迭代每条记录：

DECLARE @spName NVARCHAR(128)
DECLARE myCursor CURSOR FOR SELECT name FROM sys.procedures ORDER BY modify_date DESC
OPEN myCursor
FETCH NEXT FROM myCursor INTO @spName
WHILE @@fetch_status = 0
BEGIN
    -- Process each stored procedure with a dynamic query
    PRINT @spName
FETCH NEXT FROM myCursor INTO @spName
END
CLOSE myCursor
DEALLOCATE myCursor

You could use a cursor to iterate through each record:

DECLARE @spName NVARCHAR(128)
DECLARE myCursor CURSOR FOR SELECT name FROM sys.procedures ORDER BY modify_date DESC
OPEN myCursor
FETCH NEXT FROM myCursor INTO @spName
WHILE @@fetch_status = 0
BEGIN
    -- Process each stored procedure with a dynamic query
    PRINT @spName
FETCH NEXT FROM myCursor INTO @spName
END
CLOSE myCursor
DEALLOCATE myCursor

回复收藏 0 原文