This is the 6th post out of my 10 post series of my 10th book – SQL Basics. Today will show the importance of data and information.
You can get that in Paperback (USA) and Kindle (Worldwide).
Almost everyone these days feels comfortable with using a “point and click” process to get things done with a computer. Only a small percentage of us have ventured into writing code or working with command line utilities to create the programs that perform these tasks. Whether we write code or use clicks, what really matters is getting the job done. Computers will respond to their instructions, regardless of how they receive them. Whether we copy a file with a drag and drop operation, or use the copy command in a console window, the end result is the same; the file has been copied from one place to another.
With the SQL Server Management Studio User Interface (SSMS UI), we can create a table by writing code in a query window, or use the “point and click” method. Point and click allows us to create a table without writing any code ourselves. It is much easier to send someone the code script to accomplish a task with SQL Server than it is to send step-by-step instructions on how to point and click their way to accomplish the same task. For this reason, code script is the most common method used to consistently and reliably deploy new databases and objects the exact same way on many systems.
It is much faster to run code that has already been written and tested by someone else than it is to accurately and consistently navigate the user interface. There are times when creating long constructions of code is faster with the SSMS UI than it is to write out each line of code by hand. In addition, some people simply prefer to point and click their way to creating objects in SQL Server.
In either case, it is possible to instruct SQL Server to convert all the point and click steps into T-SQL code scripts. There is a handy feature within SQL Server known as a “Code Generator” which automatically generates T-SQL scripts to perform specific actions for objects that already exist in the database, including the database itself.
Automatic Scripting
To demonstrate how code generators work in SQL Server Management Studio let’s start by expanding the dbBasics database folder in the Object Explorer and looking at some of the tables it contains.
There are two tables in the dbBasics database which appear to be duplicates.
There is a [Shopping List] table (with a space between the words), as well as a [ShoppingList] table (without a space between the words). When we look at the two queries above our suspicions are confirmed. In addition to having similar names, these two tables share the exact same data and we don’t need both of them in the database at the same time.
One way to solve this dilemma is by using the point and click method. We can right-click on the [Shopping List] table, and then choose the Delete option from the pop up menu. This will open a Delete Object dialog box that gives us several choices. We are NOT going to click OK yet but want to go over the buttons first. We can click the ‘OK’ button to confirm we want the table deleted, or we can press the ‘Cancel’ button to exit out of the operation. What many SQL users are not aware of is that when we press the OK button, SQL Server will actually execute T-SQL code in the background, relieving us from having to write the code to delete this table ourselves.
What if we are asked to help delete this table on a server that someone else owns, and that we are not allowed to access directly? This is a common situation. When a SQL Server is being used in a production environment, very few people have access for security purposes. Since our job is to provide the code that will be run on this production SQL Server, it will be up to someone else to perform the action to delete the [Shopping List] table either with code or a point and click solution.
Years of personal experience tells us that if we try to talk someone through all of the point and click steps necessary to accomplish this task there is a chance for a misunderstanding. A simple misunderstanding when deleting a table could have an immediate negative effect on mission critical readiness, which is definitely not a desirable outcome. It will be far easier and safer to create this action as a script that we could send to the company’s SQL Operations team. This would allow another person to run the scripted action on their machine with the exact same results as the code we have already safely tested. This is also a better result as the other person can complete their work with just one execution step.
A task like the one just described is a great use of the code generator feature. The SSMS UI, as the T-SQL code generated for these types of actions will always be consistent and safe. It is the exact same code that SQL Server runs when we perform a task via the point and click method.
We can discover how to use the code generator by going through each of the point and click steps (plus one to generate code). Instead of pressing OK as the last step to delete the table, we will press ‘Cancel’ to prevent the table from being deleted until we test the code that has been generated for us by SQL Server.
We have been asked to write a script that can be used to delete the Members table at some point in the future. Of course, we are tasked to write this script when we are already in the middle of an important test. The quickest and safest way to write this new script is to use the SSMS UI to generate the code for us.
The first step is to right-click the Members table of the dbBasics database then choose the Delete option from the popup menu. This will open a Delete Object dialog box giving us several options to choose from.
Warning: DO NOT left-click the OK button during any of these steps as it will instruct SQL Server to execute the code which will delete the Members table, preventing this exercise to work correctly.
The next step is to find the Script dropdown menu button located at the top of the right-hand pane in the Delete Object dialog box (figure below). Simply left-click on the dropdown menu arrow and then choose the Script Action to New Query Window option. Finish the process by left-clicking on the Cancel button to exit the dialog box and view the code generated in the new query window.
A new query window will open in SSMS containing the code generated to delete the Members table of the dbBasics database. An example of what this code will look like is shown here:
USE [dbBasics] GO /****** Object: Table [dbo].[Members] Script Date: 8/29/2013 2:15:19 PM ******/ DROP TABLE [dbo].[Members] GO
We can verify that this code works properly by clicking on the ‘! Execute’ button to run the code. When this is complete it will be necessary to refresh the Tables folder inside the dbBasics database (right-click the Tables folder, then select the Refresh option in the popup menu). Once this folder is refreshed we can visibly see that the Members table no longer exists in the dbBasics database.
The SSMS code generator is very useful in these types of situations, as it will quickly produce reliable code to perform basic database maintenance actions. It also comes in handy for those times when it is difficult to remember exactly what syntax is necessary to write certain T-SQL code. It can even serve as an excellent learning tool to show what code will accomplish certain actions that we have yet to work with.
Action Item
Get the book for yourself and your friend. This is just a reference everyone must have it.
Available in Paperback (USA), Kindle (Worldwide)
Reference: Pinal Dave (https://blog.sqlauthority.com)
