What is the query to get a list of schemas names in a specific database in Informix?
2 Answers
Schemas are not commonly used in Informix databases and have very little trackability within a database. The CREATE SCHEMA notation is supported because it was part of SQL-89. The AUTHORIZATION clause is used to determine the (default) 'owner' of the objects created with the CREATE SCHEMA statement. There is nothing to stop a single user running the CREATE SCHEMA statement multiple times, either consecutively or at widely different times (in any given database within an Informix instance).
CREATE SCHEMA AUTHORIZATION "pokemon"
CREATE TABLE gizmo (s SERIAL NOT NULL PRIMARY KEY, v VARCHAR(20) NOT NULL)
CREATE TABLE widget(t SERIAL NOT NULL PRIMARY KEY, d DATETIME YEAR TO SECOND NOT NULL)
;
CREATE SCHEMA AUTHORIZATION "pokemon"
CREATE TABLE object (u SERIAL NOT NULL PRIMARY KEY, i INTEGER NOT NULL)
CREATE TABLE "pikachu".complain (C SERIAL NOT NULL PRIMARY KEY, v VARCHAR(255) NOT NULL)
;
After the CREATE SCHEMA statement executes, there is no way of tracking that either pair of these tables were created together as part of the same schema; there's no way to know that "pikachu".complain was part of a CREATE SCHEMA statement executed on behalf of "pokemon". There is no DROP SCHEMA statement that would necessitate such support.
answered Feb 3, 2019 at 23:43
Jonathan Leffler
759k145 gold badges961 silver badges1.3k bronze badges
Comments
A schema belongs to a user. You can list all available users from the sysusers system catalog :
SELECT username FROM "informix".sysusers;
Since only DBAs and Resource privilieges allow a user to issue a CREATE SCHEMA statement, we could restrict the query like :
SELECT username FROM "informix".sysusers WHERE usertype IN ('D', 'R');
Another solution is to list only users that actually have created tables ; for that, you can query the systables system catalog and list distinct owners.
SELECT DISTINCT owner FROM FROM "informix".systables
As commented by @JonathanLeffler, a user could have been granted RESOURCE privileges and have created a table, and then be 'demoted' to CONNECT privileges. The user would still own the table. Hence the second solution is the most accurate.
7 Comments
mor222
Thanks for your reply. In Informix architecture, every product installation of Informix can have multiple instances, every instance can have multiple databases and each db can have multiple schemas right? I want the list of schemas for a specific database or user (based on what you said)
GMB
@mor222 : yes... each user (may) have a single schema. These queries operate on database-level and should give you the result you expect.
mor222
So you mean every user can have only 0 or 1 schema? and the name of that schema is the same as the user's name?
GMB
@mor222 : yes, exactly. Check out the link to the CREATE SCHEMA statement that I put in my answer.
Jonathan Leffler
A user could have been granted RESOURCE privileges and create a table, and then be 'demoted' to CONNECT privileges. The user would still own the table. Also, it's possible for users not listed in SysUsers to own tables — witness "pokemon" and "pikachu" in the example in my answer. Plus it's possible to grant privileges to PUBLIC so anyone can connect. Thus, selecting from SysUsers is wholly unreliable as a way of determining which names could be owners of schema objects. Searching through SysTables is much more reliable; that will list users who own tables and views — which is 99% accurate.
|