Wednesday, February 10, 2010

Creating DDL For An Entire Database In SQL Server 2008

Recently, I started a new project which has a database component. I looked around for some visual data modeling tools, and I settled on just using the diagrams capability of SQL Server. Since the client is using SQL Server, it was simple to download SQL Server Express and get started using their diagramming tool.

After creating a bunch of tables, I learned that SQL Server Database Diagrams do not produce the Data Definition Language (DDL) to create the database. Instead, the tables are created in sync with the diagram. Furthermore, SQL Server does not have a command that creates the DDL for an entire database. Right clicking on two dozen tables is cumbersome. But even worse, it would not provide complete DDL, since the table DDL does not include index definitions.

I have seen some debate on the web about the merits of graphical tools versus text DDL. Each has their advantages, and, personally, I believe that a decent database tool should allow users to switch between the two. The graphical environment lets me see the tables and their relationships. The text allows me to make global changes, such as:
  • Changing all the SMALLDATETIME data types to DATE when I go to a commercial version of SQL Server. The Expression version does not support DATE, alas.
  • Adding auditing columns -- such as user, creation date, and update date -- to almost all tables.
  • Adding table-specific comments.
Doing these types of actions in a point-and-click environment is cumbersome, inefficient, and prone to error. At the same time, the GUI environment is great for designing the tables and visualizing their relationships.

So, I searched on the web for a DDL program that would allow me to create the DDL for an entire SQL Server database. Because I did not find any, I decided that I had to write something myself. The attached file contains script-all-tables.sql contains my script.

This script uses SQL to generate SQL code -- a trick that I talk about in my book Data Analysis Using SQL and Excel. The script generates code for the following:
  1. Dropping all tables in the database, if they exist.
  2. Creating new versions of the tables, taking into account primary keys, data types, and identity columns.
  3. Creating foreign key constraints on the table.
  4. Creating indexes on the table.
This is a very common subset of DDL used for databases. And, importantly, it seems to cover almost all that you can do using Database Diagrams. However, the list of what it is missing from fully re-creating any database is very, very long, ranging from user defined types, functions, and procedures, to the storage architecture, replication, and triggers.

The script uses the view in the sys schema rather than in Information_Schema simply because I found it easier to find the information that I needed to put the SQL together.

4 comments:

  1. You can use Oracle's new "Data modeler" for this.
    www.oracle.com/technology/products/database/datamodeler/index.html

    It has a graphical interface, and will generate (most of) DDL scripts for you. It works with SQL Server as well as Oralcle. If you use it for evaluation, it is free.

    ReplyDelete
  2. Hi Gordon

    So I looked at your script, and the part with the comment "delete the tables" is a scary one to run for someone who does not know what they are doing. It is always safer to include an automated backup of the tables in such scripts, or tell people that they should be backing up their database before dropping tables.

    As you mention, the "very common subset" of DDL which this script handles only focuses on the table and index structure. Like other major databases, SQL Server is a production environment which not only describes table relationships but also perhaps triggers and other dynamic specifications which help maintain the security and integrity of a database. Put into other words, tables and indexes do not take care of themselves, but require metadynamics to provide automated or semi-automated management.

    As you imply, people can recreate tables, or database parameters, or indexes by right clicking those elements, and choosing "Script Table > Create To > New Query Window" from within Management Studio. It seems simpler and safer to copy from one source database to a new named database and dropping all the contents (meaning the data). Among many good vendors, Redgate provides many useful tools for SQL Server.

    My website markab.net provides links and resources for people using SQL Server data mining.

    ReplyDelete
  3. Good one. You might be interested to take a look at the collection of Tutorials and videos on Data mining.
    Tutorials: http://www.dataminingtools.net/browsetutorials.php
    Videos: http://www.dataminingtools.net/browse.php

    ReplyDelete
  4. I will be taking Data mining in the fall semester. I am looking for a good book on data mining that explains the following: decision trees, similarity measures, regression, Bayes theorem, nearest neighbor, neural networks and genetic algorithms, researching data mining applications and integrating data mining with data warehouses.

    Any suggestions?

    ReplyDelete

Your comment will appear when it has been reviewed by the moderators.