我可以从 MSMS(来自 TSQL 而不是来自 UI)自动打开或保存存储过程和函数的主体吗? [英] Can I open or save storedprocedure's and function's body from MSMS (from TSQL not from UI) automatically?

问题描述

我必须更改列的长度.我找到了使用它的过程和函数的名称.可以自动打开或保存它.有300多个对象.我必须手动分析它，但我想先打开它

I have to change a column's length. I have found names of procedures and function where it is used. It is possible to open or to save it automatically. There are above 300 object. I'll have to analize it manually, but I want to open it first

推荐答案

尝试 sp_helptext 'func or proc name' - 它会返回代码.

Try sp_helptext 'func or proc name' - it will return you the code.

如果您一次需要多个过程和函数，并在其代码中按某些条件进行过滤，请使用以下脚本:

If you need several procs and functions at once, filtered by some condition in their code, use the following script:

DECLARE @name VARCHAR(100)
DECLARE @getNames CURSOR

SET @getNames = CURSOR FOR 
    SELECT o.name 
    FROM sysobjects o 
    WHERE 
        type = 'P' AND 
        o.name IN (
            SELECT ROUTINE_NAME 
            FROM INFORMATION_SCHEMA.ROUTINES 
            WHERE ROUTINE_DEFINITION LIKE '%your condition here%'
        )

OPEN @getNames
    FETCH NEXT
    FROM @getNames INTO @name
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC sp_helptext @name
        FETCH NEXT
        FROM @getNames INTO @name
    END
    CLOSE @getNames
    DEALLOCATE @getNames
GO

这将为您提供所有过程和函数的代码 - 您可以将其保存到文件中，或在新窗口中打开等.

This will give you code for all the procs and funcitons - you can save this to file, or open in the new window, etc.

_{如果在 Management Studio 中，您可以在运行查询之前按 Ctrl+T - 它将以纯文本形式显示结果.要返回网格结果，请使用 Ctrl+D}

_{If in Management Studio, you may hit Ctrl+T before running the query - it will bring the results as plaintext. To get back to grid results, use Ctrl+D}

这篇关于我可以从 MSMS(来自 TSQL 而不是来自 UI)自动打开或保存存储过程和函数的主体吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！