Monday, November 24, 2008

Tip #16 – Get Database Information via SQL

Advantage has twenty-two system views which can be used to display information about a database. These views allow you to obtain metadata for all of the objects in the dictionary. Each of these view names is preceded with “system.” and are shown below:

columns dictionary effective permissions fts functions
indexes index files links objects packages
permissions publication articles publications relations stored procedures
subscriptions tables triggers usergroup members usergroups
users views      

For this tip I will focus on system.dictionary, since it is rarely used but contains a lot of useful information. This system view contains all the properties of the dictionary itself. This includes the major and minor versions, the table encryption password (only available to adssys), whether logins are required or disabled and many other properties.

System.Dictionary Table

The system views are available to all data dictionary users, although some fields are only available to administrative users. These tables can be very useful for displaying information in your application and for administrative scripts.

No comments: