In Sql Server Management Studio, there is strange situation that made me lose a full day work. It is the problem of opening database objects multiple times. When I try to edit a table by right clicking from the object explorer, it is opening the edit screen for the first time, at second time it is re-opening the previously opened but not in a new edit screen. Despite that, if I try to select from that table by right clicking from the object explorer, it is opening the new query with the select statement multiple times while I retry this process. It is also doing exactly the same thing for the stored procedures and functions. I lost my changes in a stored procedure because of opening multiple times.In Visual Studio it is not possible to re-open a file in new editor multiple times by default. Are there any option to reconfigure sql server management studio to not open an object which is already opened in editor?
Note: I know this is my problem to remember which objects I have already opened, but I am asking for help, if there is an option that I am not aware.
Important EDIT:
I need to clarify that I can understand the SQL programming and I am not a newbie. I just don't used to SSMS and I don't know the all features of that tool. In my question's Note Section, I marked that it is my responsibility to keep records of scripts. I am only asking for if there is an option to prevent opening same script multiple times. If your answer is not more than suggesting to using third-party tool etc. please keep it for yourself.
Best Answer
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.