SSMS 2017 – Open SQL Files in Existing App with Raised Admin Permissions

You could import the database definition in SSDT using the "Schema Compare" tool.

Simply create an empty database project, right click and select "Schema compare". Select the project as target and the live database as source. Click "Compare" and then "Update": SSDT will script all objects and add them to your project.

SSDT by default creates a folder for each schema and in each folder creates separate folders for tables, stored procedures, views and so on.

No, there no option for this, at least not as of SSMS 2014 (I haven't yet tried 2016). The problem is that when you right-click in Object Explorer and choose either Modify or Script Stored Procedure as   ▶, you are not "opening" anything: you are merely scripting it. When you are editing a table, that is a specialized UI that knows the table name that is being edited, and that name (and possibly object_id) can be checked against other instances of the "table editor". But when scripting out other objects as either "Modify", "ALTER To", or "DROP And CREATE To", then you aren't in a specialized editor, you are placed into a file editor. And being a file editor, it only guards against multiple instances of the same file path being edited concurrently. When you have a query tab open, you can even see the file name on the left side of the tab itself (the connection info is on the right). When scripting out an object, by default it opens a new query tab with a file name of SQLQuery{N}.sql where the {N} is an incrementing number. If you right-click on the tab itself and select Copy Full Path and paste that somewhere, you will see that the real file name is similar to C:\Users\{windows_login}\AppData\Local\Temp\~vsEFF9.sql. Whether you go by the proposed name shown in the tab or the temp file name, it is different for each new tab so there is no way for SSMS to know that you have scripted the same object already.

This does not work any differently than Visual Studio / SSDT (with one notable exception). The reason why Visual Studio goes to the same tab when clicking on a file in the project, or even opening the file from File Explorer (assuming that Visual Studio is the app associated with that file extension), is that it knows the full path name of the file being edited in each tab and the file being requested to be opened. It has nothing to do with source control. But when scripting a database object via the SQL Server Object Explorer (in Visual Studio, and using Script As ▶), it also creates a new tab each time you script the same object, since it has a unique file name each time.

The "notable exception" I mentioned regarding Visual Studio is when using the View Code option in SQL Server Object Explorer. This particular function does (somehow) generate the exact same file name when it scripts an object, so it will go to the same tab each time and hence you will not get multiple tabs! The file name that you see in the tab when using the View Code option is in the format of {schema_name}.{procedure_name}.sql. If you right-click on the tab (the tab opened by View Code) and select Copy Full Path, and then paste that somewhere, you will see something along the lines of: MSSQL::/{server_or_instance_name}/{database_name}/True/SqlProcedure/{schema_name}.{procedure_name}.sql. Since this format has the connection info embedded in it, that should protect against editing the same object across two different instances and losing track of which one was which :-). This approach needs to be mimicked over in SSMS in order for you to accomplish what you are trying to do.

I'm not sure if using Visual Studio is an option (technically it's not 3rd party ;-), but even if it is, the ideal solution is to never, ever edit Stored Procedures, Functions, etc by scripting them out of the database. Objects should (again, ideally) only be edited from a singular source file that is (ideally) linked to some sort of version / source control (Git, Subversion/SVN, TFS, etc). The database, where the object exists in its natural state in system tables, is not source control: it provides no history and provides no safe-guards against other people making changes at the same time. Even if you don't use version control (you really should :), you should at least have a common location for object scripts that everyone uses. And if you did at least that much, then you wouldn't be able to open up the same script into multiple tabs because the file editors (both SSMS and Visual Studio) only allow a file to be edited in a single tab.