Perl for System Administration

Server Documentation

A great deal of time and energy goes into the configuration of an SQL server and the objects that reside on it. Having a way to document this sort of information can come in handy in a number of situations. If a database gets corrupted and there’s no backup, you may be called upon to recreate all of its tables. You may have to migrate data from one server to another; knowing the source and destination configuration can be important. Even for your own database programming, being able to see a table map can be very helpful.

To give you a flavor of the nonportable nature of database administration, let me show you an example of the same simple task as written for three different SQL servers using both DBI and ODBC. Each of these programs does the exact same thing: print out a listing of all of the databases on a server, their tables, and the basic structure of each table. These scripts could easily be expanded to show more information about each object. For instance, it might be useful to show which columns in a table had NULL or NOT NULL set. The output of all three programs looks roughly like this:

---sysadm--- hosts name [char(30)] ipaddr [char(15)] aliases [char(50)] owner [char(40)] dept [char(15)] bldg [char(10)] room [char(4)] manuf [char(10)] model [char(10)] ---hpotter--- customers cid [char(4)] cname [varchar(13)] city [varchar(20)] discnt [real(7)] agents aid [char(3)] aname [varchar(13)] city [varchar(20)] percent [int(10)] products pid [char(3)] pname ...