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
