In case anyone followed this topic, it was noted to me off-list that I
included a mistake in the SQL posted, which might confuse people who try it!
Please see below the corrected version.
Cheers!
-- Schema Info Views
--
-- This is a set of views that supply queryable Schema information for
SQLite DBs in
-- table format.
--
DROP VIEW SysIndexColumns;
DROP VIEW SysIndexes;
DROP VIEW SysColumns;
DROP VIEW SysObjects;
CREATE VIEW SysObjects AS
SELECT ObjectType COLLATE NOCASE, ObjectName COLLATE NOCASE
FROM (SELECT type AS ObjectType, name AS ObjectName
FROM sqlite_master
WHERE type IN ('table', 'view', 'index')
)
;
CREATE VIEW SysColumns AS
SELECT ObjectType COLLATE NOCASE, ObjectName COLLATE NOCASE, ColumnID
COLLATE NOCASE,
ColumnName COLLATE NOCASE, Type COLLATE NOCASE, Affinity COLLATE
NOCASE,
IsNotNull, DefaultValue, IsPrimaryKey
FROM (SELECT ObjectType, ObjectName, cid AS ColumnID, name AS
ColumnName, type AS Type,
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
FROM SysObjects
JOIN pragma_table_info(ObjectName)
)
;
CREATE VIEW SysIndexes AS
SELECT ObjectType COLLATE NOCASE, ObjectName COLLATE NOCASE, IndexName
COLLATE NOCASE,
IndexID, IsUnique COLLATE NOCASE, IndexOrigin COLLATE NOCASE,
isPartialIndex
FROM (SELECT ObjectType,ObjectName,name AS IndexName, seq AS IndexID,
"unique" AS isUnique, origin AS IndexOrigin, partial AS
isPartialIndex
FROM SysObjects
JOIN pragma_index_list(ObjectName)
)
;
CREATE VIEW SysIndexColumns AS
SELECT ObjectType COLLATE NOCASE, ObjectName COLLATE NOCASE, IndexName
COLLATE NOCASE,
IndexColumnSequence, ColumnID, ColumnName COLLATE NOCASE,
isDescendingOrder, Collation, isPartOfKey
FROM (SELECT ObjectType, ObjectName, IndexName, seqno AS
IndexColumnSequence, cid AS ColumnID,
name AS ColumnName, "desc" AS isDescendingOrder, coll AS
Collation, key AS isPartOfKey
FROM SysIndexes
JOIN pragma_index_xinfo(IndexName)
)
;
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
