0

I usually right click on a view or a stored procedure and then choose Script View -> Alter -> In New Editor Window.

Is there a way to shortcut this - either on Keyboard or on direct menu entry?

  • 2
    Let me suggest to use scripts for all your DLL and DML statements instead to directly edit on SSMS. – McNets Dec 29 '18 at 20:50
2

If you have a Menu key on your keyboard you can use it to navigate the context menus. So [Menu],s,a,n will Script as Alter to New Query Editor Window.

  • This works very well (after getting used to it) Volker – Volker Dec 30 '18 at 12:57
1

The free ssmsboost plugin does that (F12, I think) and more

https://www.ssmsboost.com

(I'm not affiliated with them, I have used it for a long time though)

  • Thanks for that one - looks like a "must have" for those who do SQL all day! Volker – Volker Dec 30 '18 at 12:56
1

As long as the objects you're interested scripting out are amoung the following:

  • C = Check constraint
  • D = Default (constraint or stand-alone)
  • P = SQL stored procedure
  • FN = SQL scalar function
  • R = Rule
  • RF = Replication filter procedure
  • TR = SQL trigger (schema-scoped DML trigger, or DDL trigger at either the database or server scope)
  • IF = SQL inline table-valued function
  • TF = SQL table-valued function
  • V = View

You can query the definition from sys.sql_modules or the OBJECT_DEFINITION() function... Since you want a "short cut" you can actually create your own... Here's how...

Start by creating the following stored procedure (or something similar) in the database(s) that you want your shortcut to work in (this is all db context dependent).

CREATE PROCEDURE dbo.ObjectDefinition
/* ==============================================================================
2018-12-30 JL,  Prints the sys.sql_module.definition for a ginen object.
============================================================================== */
/*
EXEC dbo.ObjectDefinition N'dbo.ObjectDefinition';
*/
    @Object_name sysname 
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @object_definition VARCHAR(MAX) = OBJECT_DEFINITION(OBJECT_ID(@Object_name));

    IF @object_definition IS NULL
    BEGIN
        DECLARE @error NVARCHAR(255);

        SELECT 
            @error = CASE 
                        WHEN  OBJECT_ID(@Object_name) > 0 THEN (SELECT CONCAT(N'[', SCHEMA_NAME(o.schema_id), N'].[', o.name, N'] is a ', o.type_desc, N'. ', o.type_desc, N's do not have do not have scripted definitions in sys.sql_modules.') FROM sys.objects o WHERE o.object_id = OBJECT_ID(@Object_name))
                        ELSE CONCAT(@Object_name, N' does not appear to be an object in ', DB_NAME(DB_ID()), N'.sys.objects.')
                    END

            RAISERROR(@error, 0, 1) WITH NOWAIT;
            RETURN;
    END;

    IF LEN(@object_definition) <= 4000
    BEGIN
        PRINT(@object_definition);
    END
    ELSE
    BEGIN
        WHILE 1 = 1
        BEGIN
            DECLARE 
                @segment_string NVARCHAR(4000),
                @segment_len INT;

            SELECT 
                @segment_string = SUBSTRING(SUBSTRING(@object_definition, 1, 4000), 1, cl.CrLf),
                @segment_len = LEN(@segment_string),
                @object_definition = STUFF(@object_definition, 1, @segment_len, N'')
            FROM
                ( VALUES (CHARINDEX(CHAR(10) + CHAR(13), REVERSE(SUBSTRING(@object_definition, 1, 4000)))) ) cl (CrLf);

            IF @segment_len > 0
            BEGIN
                PRINT(@segment_string);
            END;
            ELSE
            BEGIN
                BREAK;
            END;
        END;
    END;
END;

Now all you have to do is go to Tools > Options... > Environment > Keyboard > Query Shortcuts and add dbo.ObjectDefinition to one of the open slots and hit OK.

Assuming you've chosen the open Alt_F1 slot, all you need to do is highlight an object name in your t-sql and hit Alt+F1. The object definition will print in the messages tab.

If you need object types like tables and/indexes, you'll either need to use 3rd party software ( Red Gate SQL Prompt gets my vote) or roll your own SQL CLR function that uses the SQL Management Objects (SMO).

Hope this helps.

1

Another one is adding a shortcut in the SSMS by adding sp_helptext.

Tools / Options / Environment / Keyboard / Ctrl-F1 == sp_helptext

Just double click on your proc or view name,so it will selected and press ctrl +F1

Original answer from here

Don't forget to restart your SSMS after this setting.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.