Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Micros 3700 database table listing 1
- Thread starter Serenitee
- Start date
Serenitee, you can also use a 3rd-party program if you need more functionality than what Sybase Central provides. I use a program called RazorSQL. It has a lot of features, one of which is the ability to search for tables and columns within tables. There are over 1000 tables in our version of Res. They generally follow consistent naming conventions, but there are some cases where they don't.
Like pmegan said, there is very little to go on as far as explanations of what the tables are. I've figured a lot out through trial and error. In some cases, you can look at the Crystal Reports, find what stored procedure they're using, and look in the stored procedure to figure out what tables it uses. Micros's database is very difficult to work with, but we have figured out where to find a lot of useful data.
Like pmegan said, there is very little to go on as far as explanations of what the tables are. I've figured a lot out through trial and error. In some cases, you can look at the Crystal Reports, find what stored procedure they're using, and look in the stored procedure to figure out what tables it uses. Micros's database is very difficult to work with, but we have figured out where to find a lot of useful data.
-
1
- #4
The 3700 database elements seem to be a mess, but if you stare at them long enough, (I've been at it for 17 years), they start to make sense.
Tables that end in "def" are definition, or configuration, tables. Most of these are linked to the configurator in one way or another and can be updated through SQL using a non-dba login.
Those that end in "dtl" are mostly user transactions and are written to by POS operations or backoffice tools like PM or FM. These need dba access to edit, but you really don't want to be messing with these.
Tables ending with "ttl" hold accumulated totals, and are written to from the posting stored procedures. You also need dba access to edit these, and you also don't want to be doing that.
Another helpful thing to do is look at the relationships, (primary/foreign keys), for the tables. That will help understand how things are linked together.
There are two report templates loaded with the 3700 that aren't in the report list, but can be really helpful if you open them in Crystal - Schema.rpt and Domain.rpt. The first will diaplay the entire database schema, and the second diaplays all the custom data types and some will have a definition for the codes they use.
Tables that end in "def" are definition, or configuration, tables. Most of these are linked to the configurator in one way or another and can be updated through SQL using a non-dba login.
Those that end in "dtl" are mostly user transactions and are written to by POS operations or backoffice tools like PM or FM. These need dba access to edit, but you really don't want to be messing with these.
Tables ending with "ttl" hold accumulated totals, and are written to from the posting stored procedures. You also need dba access to edit these, and you also don't want to be doing that.
Another helpful thing to do is look at the relationships, (primary/foreign keys), for the tables. That will help understand how things are linked together.
There are two report templates loaded with the 3700 that aren't in the report list, but can be really helpful if you open them in Crystal - Schema.rpt and Domain.rpt. The first will diaplay the entire database schema, and the second diaplays all the custom data types and some will have a definition for the codes they use.
- Thread starter
- #5
You can't know how helpful these breadcrumbs are, thanks.
Especially because I'm trying to figure the db out without Crystal Reports. I also keep hearing rumors of some 3700sql_man doc that would make my life easier, but that doc seems to be a bit of a unicorn.
Thanks again.
Especially because I'm trying to figure the db out without Crystal Reports. I also keep hearing rumors of some 3700sql_man doc that would make my life easier, but that doc seems to be a bit of a unicorn.
Thanks again.
Actually, I don't know why I put .pdf on it. I've literally only ever seen the printed, covered in a layer of dust version of the SQL man. But its does say 3700 MAN SQL on the front of the binder. The only PDF's I have with manuals are the SIM_MAN and various 9700_MAN_* files. The 3700 stuff isn't posted anywhere; like I said, I've only seen the printed form of those. I think they used to also put documentation on the discs they sent with new software orders too, though I'm not sure about that.
wewantphil
Programmer
that is the "newest" version I've ever seen too (v2.6). year 2000, SqlAnywhere 6.0
Ha! I suppose they were way ahead of the times in abandoning writing software manuals.
Ha! I suppose they were way ahead of the times in abandoning writing software manuals.
- Status