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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.
The free ssmsboost plugin does that (F12, I think) and more
(I'm not affiliated with them, I have used it for a long time though)
As long as the objects you're interested scripting out are amoung the following:
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.
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.