Build a web page for database from a create database sql file

• Download source files - 6.12 Kb

Introduction

Always we need develop application with the database, and there are many file to describe the database structure, like database design, the sql of create database, etc. so is any way to easy to manage these files? here, I give my way to do it.

Get ready?

First, we need know what file you need to run SqlDoc vbscript file. I add a sample file in the source file, may be you can download it now, it contains a sample dbpm_iv.sql file. which creat a database in SQL server 2k5, Ah, I used Sql Server 2k5 for my application, it also can work with Sql Server 2k.

Run the SqlDoc.vbs

Double click the SqlDoc.vbs file, you will see a small dialog show 'done' the work. Then you can find a db_design.html file in the current folder. This is what we want.

How to implement it?

First, you may know something about regular expression, because all I parse the sql sentence is implymented by using regular expression.

Need help with regular expression? a good website recommand you to learn it. Regular Expressions Reference - Basic Syntax

I used the following regular expression to get objects in *.sql file.

dim rxDatabase, rxTable, rxPK, rxFK, rxColumn, rxTableSummary
rxColumn = "([a-zA-Z0-9]+)\s+(int identity|int|char|varchar|nvarchar|bit)\s?(\([\d\s,]+\))?\s?(not null|null)?\,\s?(/\*([^\*/]+)\*/)?"
'group info: 0 - columnName,1 - type, 2 - range, 3 - null?

rxDatabase = "create\s+database\s+([0-9a-zA-Z]+)"
'group info: 0 - databaseName

rxTable = "create\s+table\s+([0-9a-zA-Z]+)"
'group info: 0 - tableName

rxPK = "primary\s+key\s+\(([0-9a-zA-Z]+)\)"
'group info: 0 - PK columnName

rxFK = "foreign\s+key\s+\(([0-9a-zA-Z]+)\)"
'group info: 0 - FK columnName

rxTableSummary = "/\*\s?summary:\s?(.+)\*/"
'group info: 0 - summary text

Notes: there is difference using regular between in vbscript and others language, e.g C#.

Then, I format these object(database, table, column, pk-primary key, fk-foreign key, tablesummary-summary of table) text in a html table just like the image on the page top.

that's all. please tell more details from source, CODE is everything.

about my first article

Oh, this is my first post a article on CP. please give me some suggestion if you find anything wasn't good. (and my English doesn't so good as my mother language -- Chinese, please forgive my poor English).