Hi Keith: If there is a standard naming convention out there, we should consider adopting it. How about the naming listed in the link below:https://www.postgresql.org/docs/9.3/information-schema.html https://www.cmi.ac.in/~madhavan/courses/databases10/mysql-5.0-reference-manual/information-schema.html
Regards -----Original Message----- From: Keith Medcalf <[email protected]> To: SQLite Users ([email protected]) <[email protected]> Sent: Thu, Jun 6, 2019 1:55 pm Subject: [sqlite] New Information Schema Views This is an update to the Schema Information views that I previously posted. This version has the capability to display information for all available schema names (attached databases) simultaneously. It requires that the SQL function "eval" be available since it runs dynamically generated SQL and I could not figure out a better way to collect information from all the attached database sqlite_master tables simultaneously. Particularly the SysObjects view is the one that does this. Any improvements or suggestions welcome! Note that if you have embeded ';' in your identifier names you are buggered as the parsing of the eval() output uses that as a column separator ... -- Catalog Views using sqlite_master for SysObjects (Object Names) -- and the various pragma_<infotype>(ObjectName) tables to retrieve schema data -- all TEXT columns in views have "collate nocase" attached to the output -- columns to ensure that where conditions on retrievals are not case sensitive -- Column Names in views defined so as to not conflict with keywords to ensure -- quoting when using views is not required drop view if exists SysIndexColumns; drop view if exists SysIndexes; drop view if exists SysColumns; drop view if exists SysObjects; create view if not exists SysObjects as with table1 as (select group_concat('select ''' || name || ''' as schema, type, name from ' || name || '.sqlite_master', ' union ') as sql from pragma_database_list), table2 as (select eval(sql, ';') || ';' as s from table1), table3 (t, token, remainder) as (select 0, '', s from table2 union select t+1, substr(remainder, 1, instr(remainder, ';') - 1), substr(remainder, instr(remainder, ';') + 1) from table3 where length(remainder) > 0), table4 (r, c, token) as (select (t - 1) / 3, (t - 1) % 3, token from table3 where t > 0) select (select token from table4 where r = row and c = 0) as ObjectSchema, (select token from table4 where r = row and c = 1) as ObjectType, (select token from table4 where r = row and c = 2) as ObjectName from (select distinct r as row from table4); create view if not exists SysColumns as select ObjectSchema collate nocase, ObjectType collate nocase, ObjectName collate nocase, ColumnID collate nocase, ColumnName collate nocase, Type collate nocase, Affinity collate nocase, isNotNull, DefaultValue, isPrimaryKey, isHidden from ( select ObjectSchema, ObjectType, ObjectName, cid as ColumnID, name as ColumnName, type as Type, --- Affinity Rules from https://www.sqlite.org/datatype3.html Section 3.1 case when trim(type) = '' then 'Blob' when instr(UPPER(type), 'INT') > 0 then 'Integer' when instr(UPPER(type), 'CLOB') > 0 then 'Text' when instr(UPPER(type), 'CHAR') > 0 then 'Text' when instr(UPPER(type), 'TEXT') > 0 then 'Text' when instr(UPPER(type), 'BLOB') > 0 then 'Blob' when instr(UPPER(type), 'REAL') > 0 then 'Real' when instr(UPPER(type), 'FLOA') > 0 then 'Real' when instr(UPPER(type), 'DOUB') > 0 then 'Real' else 'Numeric' end as Affinity, "notnull" as isNotNull, dflt_value as DefaultValue, pk as isPrimaryKey, Hidden as isHidden from SysObjects cross join pragma_table_xinfo where arg = ObjectName and schema = ObjectSchema and ObjectType in ('table', 'view') ); create view if not exists SysIndexes as select ObjectSchema collate nocase, ObjectType collate nocase, ObjectName collate nocase, IndexName collate nocase, IndexID, isUniqueIndex, IndexOrigin collate nocase, isPartialIndex from ( select ObjectSchema, ObjectType, ObjectName, name as IndexName, seq as IndexID, "unique" as isUniqueIndex, origin as IndexOrigin, partial as isPartialIndex from SysObjects cross join pragma_index_list where arg = ObjectName and schema = ObjectSchema and ObjectType = 'table' ); create view if not exists SysIndexColumns as select ObjectSchema collate nocase, ObjectType collate nocase, ObjectName collate nocase, IndexName collate nocase, IndexColumnSequence, ColumnID, ColumnName collate nocase, isDescendingOrder, Collation collate nocase, isPartOfKey from ( select ObjectSchema, ObjectType, ObjectName, IndexName, seqno as IndexColumnSequence, cid as ColumnID, name as ColumnName, "desc" as isDescendingOrder, coll as Collation, key as isPartOfKey from SysIndexes cross join pragma_index_xinfo where arg = IndexName and schema = ObjectSchema ); --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
