Scripts in SQL Server

One would think this is a small and easy topic, as Scripts seem to be a pretty easy concept to grasp. However, there are a few things to know first – are you trying to affect the database itself (create it, add or drop a table, etc.), or are you generating a script to run a query on the data? This makes a difference, and of course, as with any project you do, you should have a plan first. For this post, I’ll concentrate on Database Engine scripts (you can also run scripts against other parts of SQL Server, such as the Service Broker, Reporting Services, Analysis Services, etc.). The best tool for this job is our old friend SQL Server Management Studio (SSMS). SSMS contains a Query Editor window where you can write scripts, and there is also a Template Library where you can find all sorts of samples to do the “normal” SQL Server functions – for example Creating and Dropping databases, Creating and Dropping Tables, creating Backups, etc.

Templates are very handy for a couple of reasons – first, they save you a lot of time when you have to do routine tasks, and you have a ready-made starting point where you don’t have to create all the code from scratch. Next, they are a great way to learn about what is going on and how to do it. By looking at a template, you can see correct syntax, commands you might not be familiar with, and also things like error/exception handling.

Despite the fact that I just told you how cool Templates are, I think I will just do this “Au Natural” and do my own script so you can see what needs to be done. I will create a database called “Test1”, then create a few tables in it (Customer, Order and Employee), then undo what I did add get rid of all of these.

Step 1 is to open SSMS, and click on New Query. To create the database, it is really easy, go to the Query window in SSMS and type in:

CREATE DATABASE Test1

Then hit Execute, and you now have a database. You can do all sorts of additional things with this command, such as specify the location, logon information, database size, etc., but there are perfectly good Help files to help you with that if you need any of these extra commands. A database is cool, but it is not very useful without some Tables in it too, so now let’s create the three aforementioned Tables. The “gotcha” here is that a Table without a Column is just as useless as a Database without a Table, so you need to specify a Column or two (with a data type) for each Table as well:

USE Test1

CREATE TABLE Customer (pk INT, Name text)

CREATE TABLE CustOrder (OrdNum bigint, OrdDesc text)

CREATE TABLE Employee (EmpNum int, FName text, LName text)

Let’s look at a few things here to show what I have done – first, I created Table called Customer with two Columns (to store a Primary Key, pk and a Name field). The next statement created a Table called CustOrder (for extra credit, tell me why you can’t create a Table named “Order”) that also has two Columns, one for the Order Number and one for the text description of the order. The third Table has three Columns for an Employee ID, and a First and Last name. You can verify that this worked by right-clicking on the Databases node in the Object Explorer in SSMS and selecting “Refresh” – this is one window you don’t want to hit F5 to refresh, as that command will run the script again!

As you can see, it worked! As with creating a Database, there are many other parameters you can use to create the Tables and Columns, but we’re just focusing on the basics for now. The other important task to be able to do is to undo everything I did here, and for this I will use the “Drop” command:

USE AdventureWorks

DROP DATABASE Test1

You have to use the first line here because you can’t drop a database that you are using, and my first line of the CREATE statements above used the Test1 database.

So there you have it, you now know how to create a Database, Tables and Columns and then delete them too using Scripts.

Comments

• Anonymous
  August 01, 2010
  Extra credit, eh? create table [Order] (col1 int, col2 int) order is a reserved word, but wrap it in brackets and you should be good?!? -- eliasen

• Anonymous
  August 16, 2010
  Full marks, Jan, that is exactly right!