ISO/IEC 9075-11:2023

INTERNATIONAL ISO/IEC
STANDARD 9075-11
Fifth edition
2023-06
Information technology — Database
languages SQL —
Part 11:
Information and definition schemas
(SQL/Schemata)
Technologies de l'information — Langages de base de données SQL —
Partie 11: Schémas des informations et des définitions (SQL/
Schemata)
Reference number
© ISO/IEC 2023
© ISO/IEC 2023
All rights reserved. Unless otherwise specified, or required in the context of its implementation, no part of this publication may
be reproduced or utilized otherwise in any form or by any means, electronic or mechanical, including photocopying, or posting on
the internet or an intranet, without prior written permission. Permission can be requested from either ISO at the address below
or ISO’s member body in the country of the requester.
ISO copyright office
CP 401 • Ch. de Blandonnet 8
CH-1214 Vernier, Geneva
Phone: +41 22 749 01 11
Email: copyright@iso.org
Website: www.iso.org
Published in Switzerland
ii
© ISO/IEC 2023 – All rights reserved

ISO/IEC9075-11:2023(E)
Contents Page
Foreword.ix
Introduction.xi
1 Scope.1
2 Normativereferences.2
3 Termsanddefinitions.3
4 Concepts.4
4.1 Notationsandconventions.4
4.1.1 Notations.4
4.1.2 ValuesinDescriptions.4
4.2 IntroductiontotheDefinitionSchema.4
4.3 IntroductiontotheInformationSchema.5
5 Lexicalelements.7
5.1 and.7
6 Information Schema.8
6.1 InformationSchemadigitalartifact.8
6.2 INFORMATION_SCHEMA Schema.8
6.3 INFORMATION_SCHEMA_CATALOG_NAMEview.9
6.4 CARDINAL_NUMBER domain.10
6.5 CHARACTER_DATAdomain.11
6.6 SQL_IDENTIFIERdomain.12
6.7 TIME_STAMPdomain.13
6.8 YES_OR_NO domain.14
6.9 ADMINISTRABLE_ROLE_AUTHORIZATIONSview.15
6.10 APPLICABLE_ROLES view.16
6.11 ASSERTIONSview.17
6.12 ATTRIBUTESview.18
6.13 CHARACTER_SETSview.20
6.14 CHECK_CONSTRAINT_ROUTINE_USAGEview.21
6.15 CHECK_CONSTRAINTS view.22
6.16 COLLATIONSview.23
6.17 COLLATION_CHARACTER_SET_APPLICABILITYview.24
6.18 COLUMN_COLUMN_USAGEview.25
6.19 COLUMN_DOMAIN_USAGE view.26
6.20 COLUMN_PRIVILEGESview.27
6.21 COLUMN_UDT_USAGEview.28
6.22 COLUMNSview.29
6.23 CONSTRAINT_COLUMN_USAGE view.32
6.24 CONSTRAINT_PERIOD_USAGEview.34
©ISO/IEC2023–Allrightsreserved iii

ISO/IEC9075-11:2023(E)
6.25 CONSTRAINT_TABLE_USAGE view.36
6.26 DATA_TYPE_PRIVILEGESview.37
6.27 DIRECT_SUPERTABLESview.38
6.28 DIRECT_SUPERTYPESview.39
6.29 DOMAIN_CONSTRAINTSview.40
6.30 DOMAINS view.41
6.31 ELEMENT_TYPESview.43
6.32 ENABLED_ROLESview.45
6.33 FIELDS view.46
6.34 KEY_COLUMN_USAGE view.47
6.35 KEY_PERIOD_USAGE view.49
6.36 METHOD_SPECIFICATION_PARAMETERSview.50
6.37 METHOD_SPECIFICATIONSview.52
6.38 PARAMETERSview.54
6.39 PERIODSview.56
6.40 PRIVATE_PARAMETERS view.58
6.41 REFERENCED_TYPES view.60
6.42 REFERENTIAL_CONSTRAINTSview.61
6.43 ROLE_COLUMN_GRANTSview.62
6.44 ROLE_ROUTINE_GRANTS view.63
6.45 ROLE_TABLE_GRANTSview.64
6.46 ROLE_TABLE_METHOD_GRANTSview.65
6.47 ROLE_USAGE_GRANTS view.66
6.48 ROLE_UDT_GRANTSview.67
6.49 ROUTINE_COLUMN_USAGEview.68
6.50 ROUTINE_PERIOD_USAGE view.69
6.51 ROUTINE_PRIVILEGES view.70
6.52 ROUTINE_ROUTINE_USAGE view.71
6.53 ROUTINE_SEQUENCE_USAGEview.72
6.54 ROUTINE_TABLE_USAGEview.73
6.55 ROUTINESview.74
6.56 SCHEMATA view.77
6.57 SEQUENCES view.78
6.58 SQL_FEATURES view.79
6.59 SQL_IMPLEMENTATION_INFO view.80
6.60 SQL_PARTSview.81
6.61 SQL_SIZINGview.82
6.62 TABLE_CONSTRAINTSview.83
6.63 TABLE_METHOD_PRIVILEGES view.84
6.64 TABLE_PRIVILEGESview.85
6.65 TABLESview.86
6.66 TRANSFORMS view.87
6.67 TRANSLATIONSview.88
6.68 TRIGGERED_UPDATE_COLUMNSview.89
6.69 TRIGGER_COLUMN_USAGEview.90
6.70 TRIGGER_PERIOD_USAGEview.91
6.71 TRIGGER_ROUTINE_USAGE view.92
iv ©ISO/IEC2023–Allrightsreserved

ISO/IEC9075-11:2023(E)
6.72 TRIGGER_SEQUENCE_USAGEview.93
6.73 TRIGGER_TABLE_USAGEview.94
6.74 TRIGGERSview.95
6.75 UDT_PRIVILEGESview.97
6.76 USAGE_PRIVILEGESview.98
6.77 USER_DEFINED_TYPESview.99
6.78 VIEW_COLUMN_USAGEview.101
6.79 VIEW_PERIOD_USAGEview.102
6.80 VIEW_ROUTINE_USAGE view.103
6.81 VIEW_TABLE_USAGEview.104
6.82 VIEWSview.105
6.83 Shortnameviews.106
7 DefinitionSchema.126
7.1 DefinitionSchemadigitalartifact.126
7.2 DEFINITION_SCHEMA Schema.126
7.3 EQUAL_KEY_DEGREESassertion.127
7.4 KEY_DEGREE_GREATER_THAN_OR_EQUAL_TO_1 assertion.128
7.5 UNIQUE_CONSTRAINT_NAME assertion.129
7.6 ASSERTIONSbasetable.130
7.7 ATTRIBUTESbasetable.132
7.8 AUTHORIZATIONSbasetable.134
7.9 CATALOG_NAMEbasetable.135
7.10 CHARACTER_ENCODING_FORMSbasetable.136
7.11 CHARACTER_REPERTOIRESbasetable.138
7.12 CHARACTER_SETSbasetable.140
7.13 CHECK_COLUMN_USAGEbasetable.143
7.14 CHECK_CONSTRAINT_ROUTINE_USAGEbasetable.144
7.15 CHECK_CONSTRAINTSbasetable.145
7.16 CHECK_PERIOD_USAGEbasetable.146
7.17 CHECK_TABLE_USAGEbasetable.147
7.18 COLLATIONSbasetable.148
7.19 COLLATION_CHARACTER_SET_APPLICABILITYbasetable.150
7.20 COLUMN_COLUMN_USAGEbasetable.152
7.21 COLUMN_PRIVILEGESbasetable.153
7.22 COLUMNSbasetable.155
7.23 DATA_TYPE_DESCRIPTORbasetable.159
7.24 DIRECT_SUPERTABLESbasetable.170
7.25 DIRECT_SUPERTYPESbasetable.172
7.26 DOMAIN_CONSTRAINTSbasetable.174
7.27 DOMAINSbasetable.176
7.28 ELEMENT_TYPESbasetable.177
7.29 FIELDSbasetable.179
7.30 KEY_COLUMN_USAGEbasetable.181
7.31 KEY_PERIOD_USAGEbasetable.183
7.32 METHOD_SPECIFICATION_PARAMETERSbasetable.185
7.33 METHOD_SPECIFICATIONSbasetable.187
7.34 PARAMETERSbasetable.191
©ISO/IEC2023–Allrightsreserved v

ISO/IEC9075-11:2023(E)
7.35 PERIODSbasetable.194
7.36 PRIVATE_PARAMETERSbasetable.195
7.37 REFERENCED_TYPESbasetable.197
7.38 REFERENTIAL_CONSTRAINTSbasetable.199
7.39 ROLE_AUTHORIZATION_DESCRIPTORSbasetable.201
7.40 ROUTINE_COLUMN_USAGEbasetable.203
7.41 ROUTINE_PERIOD_USAGEbasetable.204
7.42 ROUTINE_PRIVILEGESbasetable.205
7.43 ROUTINE_ROUTINE_USAGEbasetable.207
7.44 ROUTINE_SEQUENCE_USAGEbasetable.208
7.45 ROUTINE_TABLE_USAGEbasetable.209
7.46 ROUTINESbasetable.210
7.47 SCHEMATAbasetable.218
7.48 SEQUENCESbasetable.220
7.49 SQL_CONFORMANCEbasetable.222
7.50 SQL_IMPLEMENTATION_INFObasetable.225
7.51 SQL_SIZINGbasetable.228
7.52 TABLE_CONSTRAINTSbasetable.230
7.53 TABLE_METHOD_PRIVILEGESbasetable.232
7.54 TABLE_PRIVILEGESbasetable.234
7.55 TABLESbasetable.236
7.56 TRANSFORMSbasetable.239
7.57 TRANSLATIONSbasetable.241
7.58 TRIGGERED_UPDATE_COLUMNSbasetable.243
7.59 TRIGGER_COLUMN_USAGEbasetable.244
7.60 TRIGGER_PERIOD_USAGEbasetable.245
7.61 TRIGGER_ROUTINE_USAGEbasetable.246
7.62 TRIGGER_SEQUENCE_USAGEbasetable.247
7.63 TRIGGER_TABLE_USAGEbasetable.248
7.64 TRIGGERSbasetable.249
7.65 USAGE_PRIVILEGESbasetable.252
7.66 USER_DEFINED_TYPE_PRIVILEGESbasetable.254
7.67 USER_DEFINED_TYPESbasetable.256
7.68 VIEW_COLUMN_USAGEbasetable.259
7.69 VIEW_PERIOD_USAGEbasetable.260
7.70 VIEW_ROUTINE_USAGEbasetable.261
7.71 VIEW_TABLE_USAGEbasetable.262
7.72 VIEWSbasetable.263
8 Conformance.265
8.1 ClaimsofconformancetoSQL/Schemata.265
8.2 AdditionalconformancerequirementsforSQL/Schemata.265
8.3 ImpliedfeaturerelationshipsofSQL/Schemata.265
AnnexA(informative) SQLconformancesummary.266
AnnexB(informative) Implementation-defined elements.292
AnnexC(informative) Implementation-dependent elements.295
AnnexD(informative) SQLoptionalfeaturetaxonomy.296
vi ©ISO/IEC2023–Allrightsreserved

ISO/IEC9075-11:2023(E)
AnnexE(informative) Deprecatedfeatures.299
AnnexF(informative) IncompatibilitieswithISO/IEC9075:2016.300
AnnexG(informative) DefectReportsnotaddressedinthiseditionofthisdocument.301
AnnexH(informative) SQLmandatoryfeaturetaxonomy.302
Index.304
©ISO/IEC2023–Allrightsreserved vii

ISO/IEC9075-11:2023(E)
Tables
Table Page
1 ImpliedfeaturerelationshipsofSQL/Schemata.265
A.1 FeaturedefinitionsoutsideofConformanceRules.266
D.1 Featuretaxonomyforoptionalfeatures.296
H.1 Featuretaxonomyanddefinitionformandatoryfeatures.302
viii ©ISO/IEC2023–Allrightsreserved

Foreword
ISO (the International Organization for Standardization) and IEC (the International Electrotechnical
Commission) form the specialized system for worldwide standardization. National bodies that are
members of ISO or IEC participate in the development of International Standards through technical
committees established by the respective organization to deal with particular fields of technical activity.
ISO and IEC technical committees collaborate in fields of mutual interest. Other international
organizations, governmental and non-governmental, in liaison with ISO and IEC, also take part in the
work.
The procedures used to develop this document and those intended for its further maintenance are
described in the ISO/IEC Directives, Part 1. In particular, the different approval criteria needed for the
different types of document should be noted. This document was drafted in accordance with the editorial
rules of the ISO/IEC Directives, Part 2 (see www.iso.org/directives or
www.iec.ch/members_experts/refdocs).
ISO and IEC draw attention to the possibility that the implementation of this document may involve the
use of (a) patent(s). ISO and IEC take no position concerning the evidence, validity or applicability of any
claimed patent rights in respect thereof. As of the date of publication of this document, ISO and IEC have
not received notice of (a) patent(s) which may be required to implement this document. However,
implementers are cautioned that this may not represent the latest information, which may be obtained
from the patent database available at www.iso.org/patents and https://patents.iec.ch. ISO and IEC shall
not be held responsible for identifying any or all such patent rights.
Any trade name used in this document is information given for the convenience of users and does not
constitute an endorsement.
For an explanation of the voluntary nature of standards, the meaning of ISO specific terms and
expressions related to conformity assessment, as well as information about ISO's adherence to the World
Trade Organization (WTO) principles in the Technical Barriers to Trade (TBT) see
www.iso.org/iso/foreword.html. In the IEC, see www.iec.ch/understanding-standards.
This document was prepared by Joint Technical Committee ISO/IEC JTC 1, Information technology,
Subcommittee SC 32, Data management and interchange.
This fifth edition cancels and replaces the fourth edition (ISO/IEC 9075-11:2016), which has been
technically revised. It also incorporates the Technical Corrigenda ISO/IEC 9075-9:2016/Cor.1:2019 and
ISO/IEC 9075-9:2016/Cor.2:2022.
The main changes are as follows:
— improve the presentation and accuracy of the summaries of implementation-defined and
implementation-dependent aspects of this document;
— introduction of several digital artifacts;
— alignment with updated ISO house style and other guidelines for creating standards.
© ISO/IEC 2023 – All rights reserved ix

This fifth edition of ISO/IEC 9075-11 is designed to be used in conjunction with the following editions of
other parts of the ISO/IEC 9075 series, all published in 2023:
— ISO/IEC 9075-1, sixth edition;
— ISO/IEC 9075-2, sixth edition;
— ISO/IEC 9075-3, sixth edition;
— ISO/IEC 9075-4, seventh edition;
— ISO/IEC 9075-9, fifth edition;
— ISO/IEC 9075-10, fifth edition;
— ISO/IEC 9075-13, fifth edition;
— ISO/IEC 9075-14, sixth edition;
— ISO/IEC 9075-15, second edition;
— ISO/IEC 9075-16, first edition.
A list of all parts in the ISO/IEC 9075 series can be found on the ISO and IEC websites.
Any feedback or questions on this document should be directed to the user’s national standards body. A
complete listing of these bodies can be found at www.iso.org/members.html and www.iec.ch/national-
committees.
x © ISO/IEC 2023 – All rights reserved

ISO/IEC9075-11:2023(E)
Introduction
Theorganizationofthisdocumentisasfollows:
1) Clause1,“Scope”,specifiesthescopeofthisdocument.
2) Clause2,“Normativereferences”,identifiesadditionalstandardsthat,throughreferenceinthis
document,constituteprovisionsofthisdocument.
3) Clause3,“Termsanddefinitions”,definesthetermsanddefinitionsusedinthisdocument.
4) Clause4,“Concepts”,presentsconceptsusedinthedefinitionofPersistentSQLmodules.
5) Clause5,“Lexicalelements”,definesthelexicalelementsofthelanguage.
6) Clause6,“InformationSchema”,definesviewedtablesthatcontainschemainformation.
7) Clause7,“DefinitionSchema”,definesbasetablesonwhichtheviewedtablescontainingschema
informationdepend.
8) Clause8,“Conformance”,definesthecriteriaforconformancetothisdocument.
9) AnnexA,“SQLconformancesummary”,isaninformativeAnnex.Itsummarizestheconformance
requirementsoftheSQLlanguage.
10) AnnexB,“Implementation-definedelements”,isaninformativeAnnex.Itliststhosefeaturesfor
whichthebodyofthisdocumentstatesthatthesyntax,themeaning,thereturnedresults,theeffect
onSQL-dataand/orschemas,orotheraspectispartlyorwhollyimplementation-defined.
11) AnnexC,“Implementation-dependentelements”,isaninformativeAnnex.Itliststhosefeaturesfor
whichthebodyofthisdocumentstatesthatthesyntax,themeaning,thereturnedresults,theeffect
onSQL-dataand/orschemas,orotheraspectispartlyorwhollyimplementation-dependent.
12) AnnexD,“SQLoptionalfeaturetaxonomy”,isaninformativeAnnex.Itidentifiestheoptionalfeatures
oftheSQLlanguagespecifiedinthisdocumentbyanidentifierandashortdescriptivename.This
taxonomyisusedtospecifyconformance.
13) AnnexE,“Deprecatedfeatures”,isaninformativeAnnex.Itlistsfeaturesthattheresponsible
TechnicalCommitteeintendsnottoincludeinafutureeditionofthisdocument.
14) AnnexF,“IncompatibilitieswithISO/IEC9075:2016”,isaninformativeAnnex.Itlistsincompatib-
ilitieswiththepreviouseditionofthisdocument.
15) AnnexG,“DefectReportsnotaddressedinthiseditionofthisdocument”,isaninformativeAnnex.
ItdescribestheDefectReportsthatwereknownatthetimeofpublicationofthisdocument.Each
oftheseproblemsisaproblemcarriedforwardfromthepreviouseditionoftheISO/IEC9075
series.Nonewproblemshavebeencreatedinthedraftingofthisdocument.
16) AnnexH,“SQLmandatoryfeaturetaxonomy”,isaninformativeAnnex.Itidentifiesmandatoryfea-
turesandsubfeaturesoftheSQLlanguagespecifiedinthisdocumentbyanidentifierandashort
descriptivename.ThistaxonomyisusedtospecifyconformancetoCoreSQL.
Inthetextofthisdocument,inClause6,“InformationSchema”,throughClause8,“Conformance”,Sub-
clausesbeginnewpages.Anyresultingblankspaceisnotsignificant.
©ISO/IEC2023–Allrightsreserved xi

INTERNATIONAL STANDARD ISO/IEC 9075-11:2023(E)
Informationtechnology—DatabaselanguageSQL—
Part11:
InformationandDefinitionSchemas(SQL/Schemata)
1 Scope
ThisdocumentspecifiesanInformationSchemaandaDefinitionSchemathatdescribesthefollowing
information.
— ThestructureandintegrityconstraintsofSQL-data.
— ThesecurityandauthorizationspecificationsrelatingtoSQL-data.
— ThefeaturesandsubfeaturesoftheISO/IEC9075series,andthesupportthateachofthesehasin
anSQL-implementation.
— TheSQL-implementationinformationandsizingitemsoftheISO/IEC9075seriesandthevalues
supportedbyanSQL-implementation.
©ISO/IEC2023–Allrightsreserved 1

ISO/IEC9075-11:2023(E)
2 Normativereferences
Thefollowingdocumentsarereferredtointhetextinsuchawaythatsomeoralloftheircontentconsti-
tutesrequirementsofthisdocument.Fordatedreferences,onlytheeditioncitedapplies.Forundated
references,thelatesteditionofthereferenceddocument(includinganyamendments)applies.
ISO/IEC9075-1,Informationtechnology—Databaselanguages—SQL—Part1:Framework
(SQL/Framework)
ISO/IEC9075-2,Informationtechnology—Databaselanguages—SQL—Part2:Foundation
(SQL/Foundation)
2 ©ISO/IEC2023–Allrightsreserved

ISO/IEC9075-11:2023(E)
3 Termsanddefinitions
Forthepurposesofthisdocument,thetermsanddefinitionsgiveninISO/IEC9075-1andISO/IEC9075-
2apply.
ISOandIECmaintainterminologicaldatabasesforuseinstandardizationatthefollowingaddresses:
— ISOOnlinebrowsingplatform:availableathttps://www.iso.org/obp
— IECElectropedia:availableathttps://www.electropedia.org/
©ISO/IEC2023–Allrightsreserved 3

ISO/IEC9075-11:2023(E)
4 Concepts
ThisClausemodifiesClause4,“Concepts”,inISO/IEC9075-2.
4.1 Notationsandconventions
ThisSubclausemodifiesSubclause4.1,“Notationsandconventions”,inISO/IEC9075-2.
4.1.1 Notations
ThisSubclausemodifiesSubclause4.1.1,“Notations”,inISO/IEC9075-2.
ThenotationsusedinthisdocumentaredefinedinISO/IEC9075-1.
4.1.2 ValuesinDescriptions
TheDescriptionsinClause7,“DefinitionSchema”,sometimesspecifyvaluesthataretoappearinrows
ofbasetables.Whensuchavalueisgivenasasequenceofcapitallettersenclosedins,
itdenotesthesamevalueaswouldbedenotedbytheobtainedbyreplacing
theenclosingsbys.Theneedforsuchnotationariseswhenthecolumnin
questionsometimes,inotherrows,containscharacterstringsdenotingSQLexpressions,possiblyeven
s.
4.2 IntroductiontotheDefinitionSchema
TheDefinitionSchemabasetablesaredefinedasbeinginaschemanamedDEFINITION_SCHEMA.The
tabledefinitionsareascompleteasthedefinitionalpowerofSQLallows.Thetabledefinitionsaresup-
plementedwithassertionswhereappropriate.
TheonlypurposeoftheDefinitionSchemaistoprovideadatamodeltosupporttheInformationSchema
andtoassistunderstanding.AnSQL-implementationneeddonomorethansimulatetheexistenceofthe
DefinitionSchema,asviewedthroughtheInformationSchemaviews.Thespecificationdoesnotimply
thatanSQL-implementationshallprovidethefunctionalityinthemannerdescribedintheDefinition
Schema.
ADefinitionSchemaDScompletelydescribesallcontentsofeveryschema,excludingitself,butincluding
theInformationSchema,containedinthecatalogCthatcontainsDS.Whensomeobject,suchasaconstraint
oraview,referencesanobjectcontainedinaschemacontainedinacatalogOC,OC≠C,thereferenceto
thatobjectcannotbeconfirmed,becausetheinformationaboutobjectscontainedinOCisnotnecessarily
availabletoDS.TheconstraintsdefinedinDScanthusguaranteeconsistencyonlywithinC.
BecausetheDEFINITION_SCHEMAreferencesobjectsthatareonlydefinedintheINFORMATION_SCHEMA,
itwouldnotnormallybepossibletocreatethesetwoschematausingtheCREATESCHEMAstatements
aswritten.ItisassumedthatanINFORMATION_SCHEMAanditsunderlyingDEFINITION_SCHEMAare
createdinsomeimplementation-dependent(UW003)wayatthetimethatacatalogiscreated.
InadditiontothedescriptorscreatedbytheeffectiveexecutionoftheCREATEINFORMATION_SCHEMA
statement,theDEFINITION_SCHEMAmustalsocontaindescriptionsofotherobjectdefinedbythe
standard,e.g.,thecharacterrepertoireSQL_IDENTIFER.
Thewayinwhichcertainconstraintsareexpressedcatersforthepossibilitythatanobjectisbeingref-
erencedthatexistsinacatalogthatisoutsidethepurviewoftheDefinitionSchemacontainingthereference
inquestion.Forexample,thedefinitionoftheVIEW_TABLE_USAGEbasetableinSubclause7.71,
“VIEW_TABLE_USAGEbasetable”,includesthefollowingconstraint:
4 ©ISO/IEC2023–Allrightsreserved

ISO/IEC9075-11:2023(E)
4.2 IntroductiontotheDefinitionSchema
CONSTRAINT VIEW_TABLE_USAGE_CHECK_REFERENCES_TABLES
CHECK ( TABLE_CATALOG NOT IN
( SELECT CATALOG_NAME
FROM SCHEMATA )
OR
( TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME ) IN
( SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM TABLES ) )
EitherthetablebeingusedbytheviewexistsinacatalogwithinthisDefinitionSchema’spurview,in
whichcaseitsexistenceisguaranteed,oritisassumedbutnotguaranteedtoexistinsomecatalogthat
isoutsidethisDefinitionSchema’spurview.
BecausesareprohibitedbySR10)ofSubclause5.4,“Namesandidentifiers”,
inISO/IEC9075-2,fromspecifyingDEFINITION_SCHEMA,theDefinitionSchemacannotnormallybe
accessedinanSQL-statement.However,viewdefinitionsintheInformationSchemaassumetheexistence
oftheDefinitionSchemaandreferencebasetableswhoseisDEFINITION_SCHEMA.They
usetheDefinitionSchematodefinethecontentoftheInformationSchema.RegardlessofSR14)of
Subclause5.4,“Namesandidentifiers”,inISO/IEC9075-2,theDEFINITION_SCHEMAis
neverqualifiedbya.Itisimplementation-defined(IA027)whethertheDEFINI-
TION_SCHEMAreferencedbyanINFORMATION_SCHEMAdescribesschemasincatalogsotherthanthe
cataloginwhichtheINFORMATION_SCHEMAislocated.
AbasetableintheDefinitionSchemaisspecifiedby:
— aFunction,whichsummarizesthepurposeandcontentsofthetable;
— aDefinition,givingtheSQLstatement(s)neededtodefinethetable;
— aDescriptionofthecolumnvaluescomprisingarowofthetable;
— anInitialTablePopulationdetailingtheinitialcontentsofthetable.AnInitialTablePopulation
entryof“None”impliesthatthetablecontentsshouldbefullyapparentfromtheFunction,Definition,
andDescription;itdoesnotnecessarilyimplythatthetablehasnorows.
4.3 IntroductiontotheInformationSchema
TheviewsoftheInformationSchemaareviewedtablesdefinedintermsofthebasetablesoftheDefinition
Schema.
TheInformationSchemaviewsaredefinedasbeinginaschemanamedINFORMATION_SCHEMA,enabling
theseviewstobeaccessedinthesamewayasothertablesinotherschemas.SELECTonmostofthese
viewsisgrantedtoPUBLICWITHGRANTOPTION,sothattheycanbequeriedbyallusersandsothat
SELECTprivilegecanbefurthergrantedonviewsthatreferencetheseInformationSchemaviews.No
otherprivilegeisgrantedonthem,sotheycannotbeupdated.
InordertoprovideaccesstothesameinformationthatisavailableviatheINFORMATION_SCHEMAto
anSQL-AgentinanSQL-environmentwheretheSQL-implementationdoesnotsupportFeatureF391,
“Longidentifiers”,alternativeviewsareprovidedthatuseonlyshortidentifiers.TheInformationSchema
alsocontainsasmallnumberofdomainsonwhichthecolumnsoftheDefinitionSchemaarebased.USAGE
onallthesedomainsisgrantedtoPUBLICWITHGRANTOPTION,sothattheycanbeusedbyallusers.
AnSQL-implementationmaydefineobjectsthatareassociatedwithINFORMATION_SCHEMAthatare
notdefinedinthisClause.SQL-implementationsandfutureversionsoftheISO/IEC9075seriesmayalso
addcolumnstotablesthataredefinedinthisClause.
NOTE1—TheInformationSchematablesaresupposedtoberepresentedintheDefinitionSchemainthesamewayas
othertables,andarehenceself-describing.
NOTE2—TheInformationSchemaisadefinitionoftheSQLdatamodel,specifiedasanSQL-schema,intermsof schemastatement>sasdefinedintheISO/IEC9075series.ConstraintsdefinedinthisClausearenotactualSQLconstraints.
©ISO/IEC2023–Allrightsreserved 5

ISO/IEC9075-11:2023(E)
4.3 IntroductiontotheInformationSchema
TherepresentationofaninthebasetablesandviewsoftheInformationSchemaisbya
characterstringcorrespondingtoits(inthecaseofa)orits
(inthecaseofa).Withinthischaracterstring,every
lower-caseletterappearinginaisreplacedbytheequivalentupper-caseletter,and
everyappearinginaisreplacedbya.
WhereanhasmultipleformsthatareequalaccordingtotherulesofSubclause8.2,
“”,inISO/IEC9075-2,theformstoredisthatencounteredatdefinitiontime.
6 ©ISO/IEC2023–Allrightsreserved

ISO/IEC9075-11:2023(E)
5 Lexicalelements
ThisClausemodifiesClause5,“Lexicalelements”,inISO/IEC9075-2.
5.1 and
ThisSubclausemodifiesSubclause5.2,“and”,inISO/IEC9075-2.
Function
Specifylexicalunits(tokensandseparators)thatparticipateinSQLlanguage.
Format
::=
!! All alternatives from ISO/IEC 9075-2
::=
!! All alternatives from ISO/IEC 9075-2
©ISO/IEC2023–Allrightsreserved 7

ISO/IEC9075-11:2023(E)
6 InformationSchema
ThisClauseismodifiedbyClause20,“InformationSchema”,inISO/IEC9075-4.
ThisClauseismodifiedbyClause24,“InformationSchema”,inISO/IEC9075-9.
ThisClauseismodifiedbyClause14,“InformationSchema”,inISO/IEC9075-13.
ThisClauseismodifiedbyClause21,“InformationSchema”,inISO/IEC9075-14.
ThisClauseismodifiedbyClause18,“InformationSchema”,inISO/IEC9075-15.
ThisClauseismodifiedbyClause15,“InformationSchema”,inISO/IEC9075-16.
6.1 InformationSchemadigitalartifact
ThisSubclauseismodifiedbySubclause20.1,“InformationSchemadigitalartifact”,inISO/IEC9075-4.
ThisSubclauseismodifiedbySubclause24.1,“InformationSchemadigitalartifact”,inISO/IEC9075-9.
ThisSubclauseismodifiedbySubclause14.1,“InformationSchemadigitalartifact”,inISO/IEC9075-13.
ThisSubclauseismodifiedbySubclause21.1,“InformationSchemadigitalartifact”,inISO/IEC9075-14.
ThisSubclauseismodifiedbySubclause18.1,“InformationSchemadigitalartifact”,inISO/IEC9075-15.
ThisSubclauseismodifiedbySubclause15.1,“InformationSchemadigitalartifact”,inISO/IEC9075-16.
040913141516TheseschemadefinitionandmanipulationstatementsarealsoavailablefromtheISOwebsite
asa“digitalartifact”.Seehttps://standards.iso.org/iso-iec/9075/-11/ed-5/en/to
downloaddigitalartifactsforthisdocument.Todownloadtheschemadefinitionandmanipulation
statements,selectthefilenamedISO_IEC_9075-11(E)_Schemata-schema-definition.sql.
6.2 INFORMATION_SCHEMASchema
Function
IdentifytheschemathatistocontaintheInformationSchematables.
Definition
CREATE SCHEMA INFORMATION_SCHEMA
AUTHORIZATION INFORMATION_SCHEMA;
ConformanceRules
None.
8 ©ISO/IEC2023–Allrightsreserved

ISO/IEC9075-11:2023(E)
6.3 INFORMATION_SCHEMA_CATALOG_NAMEview
6.3 INFORMATION_SCHEMA_CATALOG_NAMEview
Function
IdentifythecatalogthatcontainstheInformationSchema.
Definition
CREATE VIEW INFORMATION_SCHEMA_CATALOG_NAME AS
SELECT CATALOG_NAME
FROM DEFINITION_SCHEMA.CATALOG_NAME
WHERE CATALOG_NAME = 'CN';
GRANT SELECT ON TABLE INFORMATION_SCHEMA_CATALOG_NAME
TO PUBLIC WITH GRANT OPTION;
Description
1) CNisthenameofthecataloginwhichthisInformationSchemaresides.
ConformanceRules
1) WithoutFeatureF391,“Longidentifiers”,conformingSQLlanguageshallnotreferencetheview
INFORMATION_SCHEMA.INFORMATION_SCHEMA_CATALOG_NAME.
2) WithoutFeatureF651,“Catalognamequalifiers”,conformingSQLlanguageshallnotreferencethe
viewINFORMATION_SCHEMA.INFORMATION_SCHEMA_CATALOG_NAME.
©ISO/IEC2023–Allrightsreserved 9

ISO/IEC9075-11:2023(E)
6.4 CARDINAL_NUMBERdomain
6.4 CARDINAL_NUMBERdomain
Function
Defineadomainthatcontainsanon-negativenumber.
Definition
CREATE DOMAIN CARDINAL_NUMBER AS INTEGER
CONSTRAINT CARDINAL_NUMBER_DOMAIN_CHECK
CHECK ( VALUE >= 0 );
GRANT USAGE ON DOMAIN CARDINAL_NUMBER
TO PUBLIC WITH GRANT OPTION;
Description
1) ThedomainCARDINAL_NUMBERcontainsnon-negativenumbersthatarelessthanorequaltothe
implementation-defined(IL011)maximumforINTEGER.
ConformanceRules
1) WithoutFeatureF251,“Domainsupport”,conformingSQLlanguageshallnotreferencethedomain
INFORMATION_SCHEMA.CARDINAL_NUMBER.
10 ©ISO/IEC2023–Allrightsreserved

ISO/IEC9075-11:2023(E)
6.5 CHARACTER_DATAdomain
6.5 CHARACTER_DATAdomain
Function
Defineadomainthatcontainscharacterdata.
Definition
CREATE DOMAIN CHARACTER_DATA AS
CHARACTER VARYING (ML)
CHARACTER SET SQL_TEXT;
GRANT USAGE ON DOMAIN CHARACTER_DATA
TO PUBLIC WITH GRANT OPTION;
Description
1) Thisdomainspecifiescharacterdata.
2) MListheimplementation-defined(IL006)maximumlengthofavariable-lengthcharacterstring.
ConformanceRules
1) WithoutFeatureF251,“Domainsupport”,conformingSQLlanguageshallnotreferencethedomain
INFORMATION_SCHEMA.CHARACTER_DATA.
©ISO/IEC2023–Allrightsreserved 11

ISO/IEC9075-11:2023(E)
6.6 SQL_IDENTIFIERdomain
6.6 SQL_IDENTIFIERdomain
Function
Defineadomainthatcontainsallvalidsands.
Definition
CREATE DOMAIN SQL_IDENTIFIER AS
CHARACTER VARYING (L)
CHARACTER SET SQL_IDENTIFIER;
GRANT USAGE ON DOMAIN SQL_IDENTIFIER
TO PUBLIC WITH GRANT OPTION;
Description
1) Thisdomainspecifiesallvariable-lengthcharactervaluesthatconformtotherulesforformation
andrepresentationofanSQLoranSQL.
NOTE3—ThereisnowayinSQLtospecifyathatwouldbetrueforthebodyofeveryvalid
SQLorandfalseforallothercharacterstringvalues.
2) Listheimplementation-defined(IL005)maximumlengthofand identifierbody>.
ConformanceRules
1) WithoutFeatureF251,“Domainsupport”,conformingSQLlanguageshallnotreferencethedomain
INFORMATION_SCHEMA.SQL_IDENTIFIER.
12 ©ISO/IEC2023–Allrightsreserved

ISO/IEC9075-11:2023(E)
6.7 TIME_STAMPdomain
6.7 TIME_STAMPdomain
Function
Defineadomainthatcontainsatimestamp.
Definition
CREATE DOMAIN TIME_STAMP AS TIMESTAMP(2) WITH TIME ZONE;
GRANT USAGE ON DOMAIN TIME_STAMP
TO PUBLIC WITH GRANT OPTION;
Description
1) ThedomainTIME_STAMPcontainsanSQLtimestampvalue.
ConformanceRules
1) WithoutbothFeatureF251,“Domainsupport”,andFeatureT011,“TimestampinInformation
Schema”,conformingSQLlanguageshallnotreferencethedomainINFORMA-
TION_SCHEMA.TIME_STAMP.
©ISO/IEC2023–Allrightsreserved 13

ISO/IEC9075-11:2023(E)
6.8 YES_OR_NOdomain
6.8 YES_OR_NOdomain
Function
Defineadomainthatcontainsacharacterstringvalue,butallowsonlytwopossiblestrings,YESorNO.
Definition
CREATE DOMAIN YES_OR_NO AS
CHARACTER VARYING (3)
CHARACTER SET SQL_IDENTIFIER
CONSTRAINT YES_OR_NO_CHECK
CHECK (VALUE IN ( 'YES', 'NO' ) );
GRANT USAGE ON DOMAIN YES_OR_NO
TO PUBLIC WITH GRANT OPTION;
Description
1) ThisDomainspecifiesallBooleanvalues,whichareneededinthedefinitionschema,encodedin
thetwostrings'YES'and'NO'.
ConformanceRules
1) WithoutFeatureF251,“Domainsupport”,conformingSQLlanguageshallnotreferencethedomain
INFORMATION_SCHEMA.YES_OR_NO.
14 ©ISO/IEC2023–Allrightsreserved

ISO/IEC9075-11:2023(E)
6.9 ADMINISTRABLE_ROLE_AUTHORIZATIONSview
6.9 ADMINISTRABLE_ROLE_AUTHORIZATIONSview
Function
IdentifyroleauthorizationsforwhichthecurrentuserorrolehasWITHADMINOPTION.
Definition
CREATE VIEW ADMINISTRABLE_ROLE_AUTHORIZATIONS AS
SELECT GRANTEE, ROLE_NAME, IS_GRANTABLE
FROM DEFINITION_SCHEMA.ROLE_AUTHORIZATION_DESCRIPTORS
WHERE ROLE_NAME IN
( SELECT ROLE_NAME
FROM INFORMATION_SCHEMA.APPLICABLE_ROLES
WHERE IS_GRANTABLE = 'YES' );
GRANT SELECT ON TABLE ADMINISTRABLE_ROLE_AUTHORIZATIONS
TO PUBLIC WITH GRANT OPTION;
ConformanceRules
1) WithoutFeatureT331,“Basicroles”,conformingSQLlanguageshallnotreferencetheview
INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONS.
2) WithoutFeatureF391,“Longidentifiers”,conformingSQLlanguageshallnotreferencetheview
INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONS.
©ISO/IEC2023–Allrightsreserved 15

ISO/IEC9075-11:2023(E)
6.10 APPLICABLE_ROLESview
6.10 APPLICABLE_ROLESview
Function
Identifiestheapplicablerolesforthecurrentuser.
Definition
CREATE RECURSIVE VIEW APPLICABLE_ROLES ( GRANTEE, ROLE_NAME, IS_GRANTABLE ) AS
( ( SELECT GRANTEE, ROLE_NAME, IS_GRANTABLE
FROM DEFINITION_SCHEMA.ROLE_AUTHORIZATION_DESCRIPTORS
WHERE ( GRANTEE IN
( CURRENT_USER, 'PUBLIC' )
OR
GRANTEE IN
( SELECT ROLE_NAME
FROM ENABLED_ROLES ) ) )
UNION
( SELECT RAD.GRANTEE, RAD.ROLE_NAME, RAD.IS_GRANTABLE
FROM DEFINITION_SCHEMA.ROLE_AUTHORIZATION_DESCRIPTORS RAD
JOIN
APPLICABLE_ROLES R
ON
RAD.GRANTEE = R.ROLE_NAME ) );
GRANT SELECT ON TABLE APPLICABLE_ROLES
TO PUBLIC WITH GRANT OPTION;
ConformanceRules
1) WithoutFeatureT331,“Basicroles”,conformingSQLlanguageshallnotreferencetheview
INFORMATION_SCHEMA.APPLICABLE_ROLES.
16 ©ISO/IEC2023–Allrightsreserved

ISO/IEC9075-11:2023(E)
6.11 ASSERTIONSview
6.11 ASSERTIONSview
Function
Identifytheassertionsdefinedinthiscatalogthatareownedbyagivenuserorrole.
Definition
CREATE VIEW ASSERTIONS AS
SELECT A.CONSTRAINT_CATALOG, A.CONSTRAINT_SCHEMA, A.CONSTRAINT_NAME,
A.IS_DEFERRABLE, A.INITIALLY_DEFERRED
FROM DEFINITION_SCHEMA.ASSERTIONS AS A
JOIN
DEFINITION_SCHEMA.SCHEMATA AS S
ON ( ( A.CONSTRAINT_CATALOG, A.CONSTRAINT_SCHEMA )
= ( S.CATALOG_NAME, S.SCHEMA_NAME ) )
WHERE ( S.SCHEMA_OWNER = CURRENT_USER
OR
S.SCHEMA_OWNER IN
( SELECT ER.ROLE_NAME
FROM ENABLED_ROLES AS ER ) )
AND
A.CONSTRAINT_CATALOG
= ( SELECT ISCN.CATALOG_NAME
FROM INFORMATION_SCHEMA_CATALOG_NAME AS ISCN );
GRANT SELECT ON TABLE ASSERTIONS
TO PUBLIC WITH GRANT OPTION;
ConformanceRules
1) WithoutFeatureF521,“Assertions”,conformingSQLlanguageshallnotreferencetheview
INFORMATION_SCHEMA.ASSERTIONS.
©ISO/IEC2023–Allrightsreserved 17

ISO/IEC9075-11:2023(E)
6.12 ATTRIBUTESview
6.12 ATTRIBUTESview
ThisSubclauseismodifiedbySubclause24.2,“ATTRIBUTESview”,inISO/IEC9075-9.
ThisSubclauseismodifiedbySubclause21.4,“ATTRIBUTESview”,inISO/IEC9075-14.
Function
Identifytheattributesofuser-definedtypesdefinedinthiscatalogthatareaccessibletoagivenuseror
role.
Definition
CREATE VIEW ATTRIBUTES AS
SELECT DISTINCT
UDT_CATALOG, UDT_SCHEMA, UDT_NAME,
A.ATTRIBUTE_NAME, ORDINAL_POSITION, ATTRIBUTE_DEFAULT,
DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH,
D1.CHARACTER_SET_CATALOG, D1.CHARACTER_SET_SCHEMA, D1.CHARACTER_SET_NAME,
D1.COLLATION_CATALOG, D1.COLLATION_SCHEMA, D1.COLLATION_NAME,
NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,
DATETIME_PRECISION, INTERVAL_TYPE, INTERVAL_PRECISION,
D1.USER_DEFINED_TYPE_CATALOG AS ATTRIBUTE_UDT_CATALOG,
D1.USER_DEFINED_TYPE_SCHEMA AS ATTRIBUTE_UDT_SCHEMA,
D1.USER_DEFINED_TYPE_NAME AS ATTRIBUTE_UDT_NAME,
D1.SCOPE_CATALOG, D1.SCOPE_SCHEMA, D1.SCOPE_NAME,
MAXIMUM_CARDINALITY, A.DTD_IDE
...