Created
February 29, 2024 14:04
-
-
Save danielleevandenbosch/c17ebbde6f1fe1aff39e01cc387220f7 to your computer and use it in GitHub Desktop.
A series of pervasive SQL "Information schema" queries
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Query for X$Attrib table | |
SELECT | |
my_Attrib.Xa$Id AS my_Id | |
, my_Attrib.Xa$Type AS my_Type | |
, my_Attrib.Xa$ASize AS my_ASize | |
, my_Attrib.Xa$Attrs AS my_Attrs | |
FROM X$Attrib AS my_Attrib; | |
-- Query for X$Field table | |
SELECT | |
my_Field.Xe$Id AS my_Id | |
, my_Field.Xe$File AS my_File | |
, my_Field.Xe$Name AS my_Name | |
, my_Field.Xe$DataType AS my_DataType | |
, my_Field.Xe$Offset AS my_Offset | |
, my_Field.Xe$Size AS my_Size | |
, my_Field.Xe$Dec AS my_Dec | |
, my_Field.Xe$Flags AS my_Flags | |
, CASE | |
WHEN my_Field.Xe$DataType = 15 THEN | |
CASE | |
WHEN my_Field.Xe$Size = 2 THEN 'SMALLIDENTITY' | |
WHEN my_Field.Xe$Size = 4 THEN 'IDENTITY' | |
WHEN my_Field.Xe$Size = 8 THEN 'BIGIDENTITY' | |
ELSE 'UNKNOWN' | |
END | |
WHEN my_Field.Xe$DataType = 9 THEN | |
CASE | |
WHEN my_Field.Xe$Size = 4 THEN 'BFLOAT4' | |
WHEN my_Field.Xe$Size = 8 THEN 'BFLOAT8' | |
ELSE 'UNKNOWN' | |
END | |
WHEN my_Field.Xe$DataType = 21 THEN 'LONGVARBINARY' | |
WHEN my_Field.Xe$DataType = 19 THEN 'CURRENCY' | |
WHEN my_Field.Xe$DataType = 3 THEN 'DATE' | |
WHEN my_Field.Xe$DataType = 30 THEN 'DATETIME' | |
WHEN my_Field.Xe$DataType = 5 THEN 'DECIMAL' | |
WHEN my_Field.Xe$DataType = 2 THEN | |
CASE | |
WHEN my_Field.Xe$Size = 4 THEN 'REAL' | |
WHEN my_Field.Xe$Size = 8 THEN 'DOUBLE' | |
ELSE 'UNKNOWN' | |
END | |
WHEN my_Field.Xe$DataType = 27 THEN 'UNIQUEIDENTIFIER' | |
WHEN my_Field.Xe$DataType = 1 THEN | |
CASE | |
WHEN my_Field.Xe$Size = 1 THEN 'TINYINT' | |
WHEN my_Field.Xe$Size = 2 THEN 'SMALLINT' | |
WHEN my_Field.Xe$Size = 4 THEN 'INTEGER' | |
WHEN my_Field.Xe$Size = 8 THEN 'BIGINT' | |
ELSE 'UNKNOWN' | |
END | |
WHEN my_Field.Xe$DataType = 6 THEN 'DECIMAL' | |
WHEN my_Field.Xe$DataType = 8 THEN 'NUMERIC' | |
WHEN my_Field.Xe$DataType = 18 THEN 'NUMERICSA' | |
WHEN my_Field.Xe$DataType = 28 THEN 'NUMERICSLB' | |
WHEN my_Field.Xe$DataType = 29 THEN 'NUMERICSLS' | |
WHEN my_Field.Xe$DataType = 31 THEN 'NUMERICSTB' | |
WHEN my_Field.Xe$DataType = 17 THEN 'NUMERICSTS' | |
WHEN my_Field.Xe$DataType = 0 THEN | |
CASE | |
WHEN my_Field.Xe$Size <= 8000 THEN 'CHAR' | |
ELSE 'UNKNOWN' | |
END | |
WHEN my_Field.Xe$DataType = 4 THEN 'TIME' | |
WHEN my_Field.Xe$DataType = 20 THEN 'TIMESTAMP' | |
WHEN my_Field.Xe$DataType = 14 THEN | |
CASE | |
WHEN my_Field.Xe$Size = 1 THEN 'UTINYINT' | |
WHEN my_Field.Xe$Size = 2 THEN 'USMALLINT' | |
WHEN my_Field.Xe$Size = 4 THEN 'UINTEGER' | |
WHEN my_Field.Xe$Size = 8 THEN 'UBIGINT' | |
ELSE 'UNKNOWN' | |
END | |
WHEN my_Field.Xe$DataType = 25 THEN 'NCHAR' | |
WHEN my_Field.Xe$DataType = 26 THEN 'NVARCHAR' | |
WHEN my_Field.Xe$DataType = 11 THEN 'VARCHAR' | |
WHEN my_Field.Xe$DataType = 16 THEN 'BIT' | |
WHEN my_Field.Xe$DataType = 7 THEN 'BIT' | |
-- Add any other mappings you may have | |
ELSE 'UNKNOWN' | |
END AS FriendlyDataType | |
FROM X$Field AS my_Field; -- returns 50,727 | |
-- Query for X$File table | |
SELECT | |
my_File.Xf$Id AS my_Id | |
, my_File.Xf$Name AS my_Name | |
, my_File.Xf$Loc AS my_Loc | |
, my_File.Xf$Flags AS my_Flags | |
, my_File.Xf$Reserved AS my_Reserved | |
FROM X$File AS my_File | |
WHERE my_File.Xf$Name LIKE '%AP_INV_TAX_DTL_H%' | |
; -- returns 1875 -- it would seem file means table. | |
-- Query for X$Index table | |
SELECT | |
my_Index.Xi$File AS my_File | |
, my_Index.Xi$Field AS my_Field | |
, my_Index.Xi$Number AS my_Number | |
, my_Index.Xi$Part AS my_Part | |
, my_Index.Xi$Flags AS my_Flags | |
FROM X$Index AS my_Index; -- returns 7713 tuples | |
-- Query for X$Occurs table | |
SELECT | |
my_Occurs.Xo$FileId AS my_FileId | |
, my_Occurs.Xo$ParentId AS my_ParentId | |
, my_Occurs.Xo$Length AS my_Length | |
, my_Occurs.Xo$Offset AS my_Offset | |
, my_Occurs.Xo$Count AS my_Count | |
, my_Occurs.Xo$MappingIndex AS my_MappingIndex | |
, my_Occurs.Xo$Flags AS my_Flags | |
FROM X$Occurs AS my_Occurs; -- returns aught | |
-- Query for X$Proc table | |
SELECT | |
Xp$Name AS my_Name | |
, Xp$Ver AS my_Ver | |
, Xp$Id AS my_Id | |
, Xp$Flags AS my_Flags | |
, Xp$Trustee AS my_Trustee | |
, Xp$Sequence AS my_Sequence | |
, Xp$Misc AS my_ddl | |
FROM X$Proc AS my_Proc; -- returns 66 | |
-- Query for X$Relate table | |
SELECT | |
my_Relate.Xr$PId AS my_PId | |
, my_Relate.Xr$Index AS my_Index | |
, my_Relate.Xr$FId AS my_FId | |
, my_Relate.Xr$FIndex AS my_FIndex | |
, my_Relate.Xr$Name AS my_Name | |
, my_Relate.Xr$UpdateRule AS my_UpdateRule | |
, my_Relate.Xr$DeleteRule AS my_DeleteRule | |
, my_Relate.Xr$Reserved AS my_Reserved | |
FROM X$Relate AS my_Relate; -- returns aught... there are no foreign keys meaning lots of orphans | |
-- Query for X$Rights table | |
SELECT | |
my_Rights.Xr$User AS my_User | |
, my_Rights.Xr$Object AS my_Object | |
, my_Rights.Xr$Type AS my_Type | |
, my_Rights.Xr$Column AS my_Column | |
, my_Rights.Xr$Rights AS my_Rights | |
FROM X$Rights AS my_Rights; -- returns 3011 | |
-- Query for X$Trigger table | |
SELECT | |
my_Trigger.Xt$Name AS my_Name | |
, my_Trigger.Xt$Version AS my_Version | |
, my_Trigger.Xt$File AS my_File | |
, my_Trigger.Xt$Event AS my_Event | |
, my_Trigger.Xt$ActionTime AS my_ActionTime | |
, my_Trigger.Xt$ForEach AS my_ForEach | |
, my_Trigger.Xt$Order AS my_Order | |
, my_Trigger.Xt$Sequence AS my_Sequence | |
, my_Trigger.Xt$Misc AS my_Misc | |
FROM X$Trigger AS my_Trigger; -- returns aught | |
-- Query for X$User table | |
SELECT TOP 10 | |
my_User.Xu$Id AS my_Id | |
, my_User.Xu$Name AS my_Name | |
, my_User.Xu$Password AS my_Password | |
, my_User.Xu$Flags AS my_Flags | |
FROM X$User AS my_User; -- returns 3 tuples | |
-- Query for X$Variant table | |
SELECT -- returns aught | |
my_Variant.Xvar$FileId AS my_FileId | |
, my_Variant.Xvar$Pdf AS my_Pdf | |
FROM X$Variant AS my_Variant; -- returns aught | |
-- Query for X$View table | |
SELECT TOP 1 | |
my_View.Xv$Name AS my_Name | |
, my_View.Xv$Version AS my_Version | |
, my_View.Xv$Id AS my_Id | |
, my_View.Xv$Trustee AS my_Trustee | |
, my_View.Xv$Sequence AS my_Sequence | |
, my_View.Xv$Misc AS my_the_drl | |
FROM X$View AS my_View ; -- returns a lot | |
--=========================queries================================= | |
SELECT | |
my_Index.Xi$File AS my_File | |
, my_Index.Xi$Field AS my_Field | |
, my_Index.Xi$Number AS my_Number | |
, my_Index.Xi$Part AS my_Part | |
, my_Index.Xi$Flags AS my_Flags | |
, my_File.Xf$Name AS my_table_name | |
, my_Field.Xe$Name AS my_column_name | |
FROM X$Index AS my_Index | |
INNER JOIN X$File AS my_File ON my_Index.Xi$File = my_File.Xf$Id | |
INNER JOIN X$Field AS my_Field ON my_Index.Xi$Field = my_Field.Xe$Id | |
WHERE 1=1 | |
--AND UPPER(my_File.Xf$Name) LIKE 'INVENTORY_MSTR' | |
ORDER BY | |
my_table_name | |
, my_Number | |
, my_Part | |
-- Query for X$Field table | |
SELECT | |
my_Field.Xe$Id AS my_Id | |
, my_Field.Xe$File AS my_File | |
, my_Table.Xf$Name AS my_table_name | |
, my_Field.Xe$Name AS my_Name | |
, my_Field.Xe$DataType AS my_DataType | |
, my_Field.Xe$Offset AS my_Offset | |
, my_Field.Xe$Size AS my_Size | |
, my_Field.Xe$Dec AS my_Dec | |
, my_Field.Xe$Flags AS my_Flags | |
, CASE | |
WHEN my_Field.Xe$DataType = 15 THEN | |
CASE | |
WHEN my_Field.Xe$Size = 2 THEN 'SMALLIDENTITY' | |
WHEN my_Field.Xe$Size = 4 THEN 'IDENTITY' | |
WHEN my_Field.Xe$Size = 8 THEN 'BIGIDENTITY' | |
ELSE 'UNKNOWN' | |
END | |
WHEN my_Field.Xe$DataType = 9 THEN | |
CASE | |
WHEN my_Field.Xe$Size = 4 THEN 'BFLOAT4' | |
WHEN my_Field.Xe$Size = 8 THEN 'BFLOAT8' | |
ELSE 'UNKNOWN' | |
END | |
WHEN my_Field.Xe$DataType = 21 THEN 'LONGVARBINARY' | |
WHEN my_Field.Xe$DataType = 19 THEN 'CURRENCY' | |
WHEN my_Field.Xe$DataType = 3 THEN 'DATE' | |
WHEN my_Field.Xe$DataType = 30 THEN 'DATETIME' | |
WHEN my_Field.Xe$DataType = 5 THEN 'DECIMAL' | |
WHEN my_Field.Xe$DataType = 2 THEN | |
CASE | |
WHEN my_Field.Xe$Size = 4 THEN 'REAL' | |
WHEN my_Field.Xe$Size = 8 THEN 'DOUBLE' | |
ELSE 'UNKNOWN' | |
END | |
WHEN my_Field.Xe$DataType = 27 THEN 'UNIQUEIDENTIFIER' | |
WHEN my_Field.Xe$DataType = 1 THEN | |
CASE | |
WHEN my_Field.Xe$Size = 1 THEN 'TINYINT' | |
WHEN my_Field.Xe$Size = 2 THEN 'SMALLINT' | |
WHEN my_Field.Xe$Size = 4 THEN 'INTEGER' | |
WHEN my_Field.Xe$Size = 8 THEN 'BIGINT' | |
ELSE 'UNKNOWN' | |
END | |
WHEN my_Field.Xe$DataType = 6 THEN 'DECIMAL' | |
WHEN my_Field.Xe$DataType = 8 THEN 'NUMERIC' | |
WHEN my_Field.Xe$DataType = 18 THEN 'NUMERICSA' | |
WHEN my_Field.Xe$DataType = 28 THEN 'NUMERICSLB' | |
WHEN my_Field.Xe$DataType = 29 THEN 'NUMERICSLS' | |
WHEN my_Field.Xe$DataType = 31 THEN 'NUMERICSTB' | |
WHEN my_Field.Xe$DataType = 17 THEN 'NUMERICSTS' | |
WHEN my_Field.Xe$DataType = 0 THEN | |
CASE | |
WHEN my_Field.Xe$Size <= 8000 THEN 'CHAR' | |
ELSE 'UNKNOWN' | |
END | |
WHEN my_Field.Xe$DataType = 4 THEN 'TIME' | |
WHEN my_Field.Xe$DataType = 20 THEN 'TIMESTAMP' | |
WHEN my_Field.Xe$DataType = 14 THEN | |
CASE | |
WHEN my_Field.Xe$Size = 1 THEN 'UTINYINT' | |
WHEN my_Field.Xe$Size = 2 THEN 'USMALLINT' | |
WHEN my_Field.Xe$Size = 4 THEN 'UINTEGER' | |
WHEN my_Field.Xe$Size = 8 THEN 'UBIGINT' | |
ELSE 'UNKNOWN' | |
END | |
WHEN my_Field.Xe$DataType = 25 THEN 'NCHAR' | |
WHEN my_Field.Xe$DataType = 26 THEN 'NVARCHAR' | |
WHEN my_Field.Xe$DataType = 11 THEN 'VARCHAR' | |
WHEN my_Field.Xe$DataType = 16 THEN 'BIT' | |
WHEN my_Field.Xe$DataType = 7 THEN 'BIT' | |
-- Add any other mappings you may have | |
ELSE 'UNKNOWN' | |
END AS FriendlyDataType | |
FROM X$Field AS my_Field | |
LEFT JOIN X$File AS my_Table ON my_Field.Xe$File = my_Table.Xf$Id | |
WHERE 1=1 | |
AND UPPER(my_Table.Xf$Name) LIKE '%%' | |
AND UPPER(my_Field.Xe$Name) LIKE '%%' | |
--AND my_Field.Xe$Size = 120 | |
--AND my_Table.Xf$Name NOT IN() -- place tables here you wish to exclued | |
ORDER BY my_table_name, my_Name | |
; -- returns 22,540 | |
SELECT * | |
FROM X$Field AS my_Field | |
LEFT JOIN X$File AS my_Table ON my_Field.Xe$File = my_Table.Xf$Id | |
WHERE my_Table.Xf$Name = 'APSV3_ActPerDef' | |
AND my_Field.Xe$DataType <> 255 | |
AND my_Field.Xe$DataType <> 227 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment