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.
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:
- Dropping all tables in the database, if they exist.
- Creating new versions of the tables, taking into account primary keys, data types, and identity columns.
- Creating foreign key constraints on the table.
- Creating indexes on the table.
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.
You can use Oracle's new "Data modeler" for this.
ReplyDeletewww.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.
Hi Gordon
ReplyDeleteSo 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.
Good one. You might be interested to take a look at the collection of Tutorials and videos on Data mining.
ReplyDeleteTutorials: http://www.dataminingtools.net/browsetutorials.php
Videos: http://www.dataminingtools.net/browse.php
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.
ReplyDeleteAny suggestions?