1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
1602
1603
1604
1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
1694
1695
1696
1697
1698
1699
1700
1701
1702
1703
1704
1705
1706
1707
1708
1709
1710
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
1734
1735
1736
1737
1738
1739
1740
1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751
1752
1753
1754
1755
1756
1757
1758
1759
1760
1761
1762
1763
1764
1765
1766
1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
1779
1780
1781
1782
1783
1784
1785
1786
1787
1788
1789
1790
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809
1810
1811
1812
1813
1814
1815
1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
1837
1838
1839
1840
1841
1842
1843
1844
1845
1846
1847
1848
1849
1850
1851
1852
1853
1854
1855
1856
1857
1858
1859
1860
1861
1862
1863
1864
1865
1866
1867
1868
1869
1870
1871
1872
1873
1874
1875
1876
1877
1878
1879
1880
1881
1882
1883
1884
1885
1886
1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909
1910
1911
1912
1913
1914
1915
1916
1917
1918
1919
1920
1921
1922
1923
1924
1925
1926
1927
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944
1945
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
1963
1964
1965
1966
1967
1968
1969
1970
1971
1972
1973
1974
1975
1976
1977
1978
1979
1980
1981
1982
1983
1984
1985
1986
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
2034
2035
2036
2037
2038
2039
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
2078
2079
2080
2081
2082
2083
2084
2085
2086
2087
2088
2089
2090
2091
2092
2093
2094
2095
2096
2097
2098
2099
2100
2101
2102
2103
2104
2105
2106
2107
2108
2109
2110
2111
2112
2113
2114
2115
2116
2117
2118
2119
2120
2121
2122
2123
2124
2125
2126
2127
2128
2129
2130
2131
2132
2133
2134
2135
2136
2137
2138
2139
2140
2141
2142
2143
2144
2145
2146
2147
2148
2149
2150
2151
2152
2153
2154
2155
2156
2157
2158
2159
2160
2161
2162
2163
2164
2165
2166
2167
2168
2169
2170
2171
2172
2173
2174
2175
2176
2177
2178
2179
2180
2181
2182
2183
2184
2185
2186
2187
2188
2189
2190
2191
2192
2193
2194
2195
2196
2197
2198
2199
2200
2201
2202
2203
2204
2205
2206
2207
2208
2209
2210
2211
2212
2213
2214
2215
2216
2217
2218
2219
2220
2221
2222
2223
2224
2225
2226
2227
2228
2229
2230
2231
2232
2233
2234
2235
2236
2237
2238
2239
2240
2241
2242
2243
2244
2245
2246
2247
2248
2249
2250
2251
2252
2253
2254
2255
2256
2257
2258
2259
2260
2261
2262
2263
2264
2265
2266
2267
2268
2269
2270
2271
2272
2273
2274
2275
2276
2277
2278
2279
2280
2281
2282
|
<!DOCTYPE html>
<html><head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<link href="sqlite.css" rel="stylesheet">
<title>Pragma statements supported by SQLite</title>
<!-- path= -->
</head>
<body>
<div class=nosearch>
<a href="index.html">
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
</a>
<div><!-- IE hack to prevent disappearing logo --></div>
<div class="tagline desktoponly">
Small. Fast. Reliable.<br>Choose any three.
</div>
<div class="menu mainmenu">
<ul>
<li><a href="index.html">Home</a>
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
<li class='wideonly'><a href='about.html'>About</a>
<li class='desktoponly'><a href="docs.html">Documentation</a>
<li class='desktoponly'><a href="download.html">Download</a>
<li class='wideonly'><a href='copyright.html'>License</a>
<li class='desktoponly'><a href="support.html">Support</a>
<li class='desktoponly'><a href="prosupport.html">Purchase</a>
<li class='search' id='search_menubutton'>
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
</ul>
</div>
<div class="menu submenu" id="submenu">
<ul>
<li><a href='about.html'>About</a>
<li><a href='docs.html'>Documentation</a>
<li><a href='download.html'>Download</a>
<li><a href='support.html'>Support</a>
<li><a href='prosupport.html'>Purchase</a>
</ul>
</div>
<div class="searchmenu" id="searchmenu">
<form method="GET" action="search">
<select name="s" id="searchtype">
<option value="d">Search Documentation</option>
<option value="c">Search Changelog</option>
</select>
<input type="text" name="q" id="searchbox" value="">
<input type="submit" value="Go">
</form>
</div>
</div>
<script>
function toggle_div(nm) {
var w = document.getElementById(nm);
if( w.style.display=="block" ){
w.style.display = "none";
}else{
w.style.display = "block";
}
}
function toggle_search() {
var w = document.getElementById("searchmenu");
if( w.style.display=="block" ){
w.style.display = "none";
} else {
w.style.display = "block";
setTimeout(function(){
document.getElementById("searchbox").focus()
}, 30);
}
}
function div_off(nm){document.getElementById(nm).style.display="none";}
window.onbeforeunload = function(e){div_off("submenu");}
/* Disable the Search feature if we are not operating from CGI, since */
/* Search is accomplished using CGI and will not work without it. */
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
document.getElementById("search_menubutton").style.display = "none";
}
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
function hideorshow(btn,obj){
var x = document.getElementById(obj);
var b = document.getElementById(btn);
if( x.style.display!='none' ){
x.style.display = 'none';
b.innerHTML='show';
}else{
x.style.display = '';
b.innerHTML='hide';
}
return false;
}
var antiRobot = 0;
function antiRobotGo(){
if( antiRobot!=3 ) return;
antiRobot = 7;
var j = document.getElementById("mtimelink");
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
}
function antiRobotDefense(){
document.body.onmousedown=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousedown=null;
}
document.body.onmousemove=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousemove=null;
}
setTimeout(function(){
antiRobot |= 1;
antiRobotGo();
}, 100)
antiRobotGo();
}
antiRobotDefense();
</script>
<h1 align="center">PRAGMA Statements</h1>
<p>The PRAGMA statement is an SQL extension specific to SQLite and used to
modify the operation of the SQLite library or to query the SQLite library for
internal (non-table) data. The PRAGMA statement is issued using the same
interface as other SQLite commands (e.g. <a href="lang_select.html">SELECT</a>, <a href="lang_insert.html">INSERT</a>) but is
different in the following important respects:
</p>
<ul>
<li>The pragma command is specific to SQLite and is
not compatible with any other SQL database engine.
<li>Specific pragma statements may be removed and others added in future
releases of SQLite. There is no guarantee of backwards compatibility.
<li>No error messages are generated if an unknown pragma is issued.
Unknown pragmas are simply ignored. This means if there is a typo in
a pragma statement the library does not inform the user of the fact.
<li>Some pragmas take effect during the SQL compilation stage, not the
execution stage. This means if using the C-language <a href="c3ref/prepare.html">sqlite3_prepare()</a>,
<a href="c3ref/step.html">sqlite3_step()</a>, <a href="c3ref/finalize.html">sqlite3_finalize()</a> API (or similar in a wrapper
interface), the pragma may run during the <a href="c3ref/prepare.html">sqlite3_prepare()</a> call,
not during the <a href="c3ref/step.html">sqlite3_step()</a> call as normal SQL statements do.
Or the pragma might run during sqlite3_step() just like normal
SQL statements. Whether or not the pragma runs during sqlite3_prepare()
or sqlite3_step() depends on the pragma and on the specific release
of SQLite.
<li>The <a href="lang_explain.html">EXPLAIN</a> and <a href="eqp.html">EXPLAIN QUERY PLAN</a> prefixes to SQL statements
only affect the behavior of the statement during <a href="c3ref/step.html">sqlite3_step()</a>.
That means that PRAGMA statements that take effect during
<a href="c3ref/prepare.html">sqlite3_prepare()</a> will behave the same way regardless of whether or
not they are prefaced by "EXPLAIN".
</ul>
<p>The C-language API for SQLite provides the <a href="c3ref/c_fcntl_begin_atomic_write.html#sqlitefcntlpragma">SQLITE_FCNTL_PRAGMA</a>
<a href="c3ref/file_control.html">file control</a> which gives <a href="vfs.html">VFS</a> implementations the
opportunity to add new PRAGMA statements or to override the meaning of
built-in PRAGMA statements.</p>
<hr /><a name="syntax"></a>
<h2>PRAGMA command syntax</h2>
<p><b><a href="syntax/pragma-stmt.html">pragma-stmt:</a></b>
<button id='xd117fae3' onclick='hideorshow("xd117fae3","xa21fab4a")'>hide</button></p>
<div id='xa21fab4a' class='imgcontainer'>
<div style="max-width:824px"><svg xmlns='http://www.w3.org/2000/svg' class="pikchr" viewBox="0 0 824.352 99.576">
<circle cx="5" cy="17" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="32,17 20,21 20,12" style="fill:rgb(0,0,0)"/>
<path d="M9,17L26,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M47,32L104,32A15 15 0 0 0 119 17A15 15 0 0 0 104 2L47,2A15 15 0 0 0 32 17A15 15 0 0 0 47 32Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="75" y="17" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">PRAGMA</text>
<polygon points="155,17 143,21 143,12" style="fill:rgb(0,0,0)"/>
<path d="M119,17L149,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M170,32L266,32A15 15 0 0 0 282 17A15 15 0 0 0 266 2L170,2A15 15 0 0 0 155 17A15 15 0 0 0 170 32Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="218" y="17" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">schema-name</text>
<polygon points="305,17 293,21 293,12" style="fill:rgb(0,0,0)"/>
<path d="M282,17L299,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M320,32A15 15 0 0 0 335 17A15 15 0 0 0 320 2A15 15 0 0 0 305 17A15 15 0 0 0 320 32Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="320" y="17" text-anchor="middle" font-weight="bold" fill="rgb(0,0,0)" dominant-baseline="central">.</text>
<polygon points="371,17 359,21 359,12" style="fill:rgb(0,0,0)"/>
<path d="M335,17L365,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M386,32L483,32A15 15 0 0 0 498 17A15 15 0 0 0 483 2L386,2A15 15 0 0 0 371 17A15 15 0 0 0 386 32Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="435" y="17" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">pragma-name</text>
<polygon points="540,82 528,86 528,77" style="fill:rgb(0,0,0)"/>
<path d="M498,17 L 506,17 Q 513,17 513,32 L 513,67 Q 513,82 524,82 L 534,82" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M555,97A15 15 0 0 0 570 82A15 15 0 0 0 555 67A15 15 0 0 0 540 82A15 15 0 0 0 555 97Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="555" y="82" text-anchor="middle" font-weight="bold" fill="rgb(0,0,0)" dominant-baseline="central">(</text>
<polygon points="593,82 582,86 582,77" style="fill:rgb(0,0,0)"/>
<path d="M570,82L587,82" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M593,97L720,97L720,67L593,67Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="656" y="82" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">pragma-value</text>
<polygon points="743,82 731,86 731,77" style="fill:rgb(0,0,0)"/>
<path d="M720,82L737,82" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M758,97A15 15 0 0 0 773 82A15 15 0 0 0 758 67A15 15 0 0 0 743 82A15 15 0 0 0 758 97Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="758" y="82" text-anchor="middle" font-weight="bold" fill="rgb(0,0,0)" dominant-baseline="central">)</text>
<polygon points="814,17 803,21 803,12" style="fill:rgb(0,0,0)"/>
<path d="M773,82 L 780,82 Q 788,82 788,67 L 788,32 Q 788,17 798,17 L 809,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<circle cx="818" cy="17" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="540,44 528,48 528,40" style="fill:rgb(0,0,0)"/>
<path d="M498,17 L 506,17 Q 513,17 513,30 Q 513,44 524,44 L 534,44" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M555,59A15 15 0 0 0 570 44L570,44A15 15 0 0 0 555 29A15 15 0 0 0 540 44L540,44A15 15 0 0 0 555 59Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="555" y="44" text-anchor="middle" font-weight="bold" fill="rgb(0,0,0)" dominant-baseline="central">=</text>
<polygon points="593,44 582,48 582,40" style="fill:rgb(0,0,0)"/>
<path d="M570,44L587,44" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M593,59L720,59L720,29L593,29Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="656" y="44" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">pragma-value</text>
<polygon points="773,44 761,48 761,40" style="fill:rgb(0,0,0)"/>
<path d="M720,44L767,44" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M773,44 L 780,44 Q 788,44 788,36 L 788,29" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="656,17 645,21 645,12" style="fill:rgb(0,0,0)"/>
<path d="M498,17L651,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M656,17L803,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="218,44 207,48 207,40" style="fill:rgb(0,0,0)"/>
<path d="M119,17 L 126,17 Q 134,17 134,30 Q 134,44 149,44 L 198,44 L 213,44" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M218,44 L 335,44 Q 350,44 350,30 Q 350,17 357,17 L 365,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
</svg>
</div>
<p><b><a href="syntax/pragma-value.html">pragma-value:</a></b>
<button id='xc2c54c29' onclick='hideorshow("xc2c54c29","xdbeedbf8")'>hide</button></p>
<div id='xdbeedbf8' class='imgcontainer'>
<div style="max-width:264px"><svg xmlns='http://www.w3.org/2000/svg' class="pikchr" viewBox="0 0 264.499 110.16">
<circle cx="5" cy="17" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="63,17 51,21 51,12" style="fill:rgb(0,0,0)"/>
<path d="M9,17L57,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M63,32L201,32L201,2L63,2Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="132" y="17" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">signed-number</text>
<polygon points="255,17 243,21 243,12" style="fill:rgb(0,0,0)"/>
<path d="M201,17L249,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<circle cx="258" cy="17" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M78,70L105,70A15 15 0 0 0 120 55L120,55A15 15 0 0 0 105 39L78,39A15 15 0 0 0 63 55L63,55A15 15 0 0 0 78 70Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="92" y="55" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">name</text>
<path d="M78,108L171,108A15 15 0 0 0 186 92A15 15 0 0 0 171 77L78,77A15 15 0 0 0 63 92A15 15 0 0 0 78 108Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="125" y="92" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">signed-literal</text>
<polygon points="63,92 51,97 51,88" style="fill:rgb(0,0,0)"/>
<path d="M21,17 L 29,17 Q 36,17 36,32 L 36,77 Q 36,92 47,92 L 57,92" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="201,92 189,97 189,88" style="fill:rgb(0,0,0)"/>
<path d="M186,92L195,92" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M201,92 L 208,92 Q 216,92 216,77 L 216,32 Q 216,17 223,17 L 231,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="201,55 189,59 189,50" style="fill:rgb(0,0,0)"/>
<path d="M120,55L195,55" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M201,55 L 208,55 Q 216,55 216,47 L 216,40" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="63,55 51,59 51,50" style="fill:rgb(0,0,0)"/>
<path d="M36,39 L 36,47 Q 36,55 47,55 L 57,55" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
</svg>
</div>
<p><b><a href="syntax/signed-number.html">signed-number:</a></b>
<button id='x8e288662' onclick='hideorshow("x8e288662","x58b8c58c")'>show</button></p>
<div id='x58b8c58c' style='display:none;' class='imgcontainer'>
<div style="max-width:292px"><svg xmlns='http://www.w3.org/2000/svg' class="pikchr" viewBox="0 0 292.013 99.576">
<circle cx="5" cy="17" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="50,44 39,48 39,40" style="fill:rgb(0,0,0)"/>
<path d="M9,17 L 16,17 Q 24,17 24,30 Q 24,44 34,44 L 45,44" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M66,59A15 15 0 0 0 81 44L81,44A15 15 0 0 0 66 29A15 15 0 0 0 50 44L50,44A15 15 0 0 0 66 59Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="66" y="44" text-anchor="middle" font-weight="bold" fill="rgb(0,0,0)" dominant-baseline="central">+</text>
<polygon points="122,17 111,21 111,12" style="fill:rgb(0,0,0)"/>
<path d="M81,44 L 88,44 Q 96,44 96,30 Q 96,17 106,17 L 116,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M137,32L244,32A15 15 0 0 0 259 17A15 15 0 0 0 244 2L137,2A15 15 0 0 0 122 17A15 15 0 0 0 137 32Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="191" y="17" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">numeric-literal</text>
<polygon points="282,17 271,21 271,12" style="fill:rgb(0,0,0)"/>
<path d="M259,17L276,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<circle cx="286" cy="17" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="50,82 39,86 39,77" style="fill:rgb(0,0,0)"/>
<path d="M9,17 L 16,17 Q 24,17 24,32 L 24,67 Q 24,82 34,82 L 45,82" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M66,97A15 15 0 0 0 81 82L81,82A15 15 0 0 0 66 67A15 15 0 0 0 50 82L50,82A15 15 0 0 0 66 97Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="66" y="82" text-anchor="middle" font-weight="bold" fill="rgb(0,0,0)" dominant-baseline="central">-</text>
<path d="M81,82 L 88,82 Q 96,82 96,67 L 96,44 L 96,29" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="66,17 54,21 54,12" style="fill:rgb(0,0,0)"/>
<path d="M9,17L60,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M66,17L111,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
</svg>
</div>
</div>
</div>
</div>
<p>
A pragma can take either zero or one argument. The argument is may be either
in parentheses or it may be separated from the pragma name by an equal sign.
The two syntaxes yield identical results.
In many pragmas, the argument is a boolean. The boolean can be one of:
</p>
<center>
<b>1 yes true on<br>0 no false off</b>
</center>
<p>Keyword arguments can optionally appear in quotes.
(Example: <tt>'yes' [FALSE]</tt>.) Some pragmas
takes a string literal as their argument. When pragma takes a keyword
argument, it will usually also take a numeric equivalent as well.
For example, "0" and "no" mean the same thing, as does "1" and "yes".
When querying the value of a setting, many pragmas return the number
rather than the keyword.</p>
<p>A pragma may have an optional <span class='yyterm'>schema-name</span>
before the pragma name.
The <span class='yyterm'>schema-name</span> is the name of an <a href="lang_attach.html">ATTACH</a>-ed database
or "main" or "temp" for the main and the TEMP databases. If the optional
schema name is omitted, "main" is assumed. In some pragmas, the schema
name is meaningless and is simply ignored. In the documentation below,
pragmas for which the schema name is meaningful are shown with a
"<i>schema.</i>" prefix.</p>
<hr /><a name="pragfunc"></a>
<h2>PRAGMA functions</h2>
<p>
PRAGMAs that return results and that have no side-effects can be
accessed from ordinary <a href="lang_select.html">SELECT</a> statements as <a href="vtab.html#tabfunc2">table-valued functions</a>.
For each participating PRAGMA, the corresponding table-valued function
has the same name as the PRAGMA with a 7-character "pragma_" prefix.
The PRAGMA argument and schema, if any, are passed as arguments to the
table-valued function, with the schema as an optional, last argument.
<p>For example, information about the columns in an index can be
read using the <a href="pragma.html#pragma_index_info">index_info pragma</a> as follows:
<blockquote><pre>
PRAGMA index_info('idx52');
</pre></blockquote>
<p>Or, the same content can be read using:
<blockquote><pre>
SELECT * FROM pragma_index_info('idx52');
</pre></blockquote>
<p>The advantage of the table-valued function format is that the query
can return just a subset of the PRAGMA columns, can include a WHERE clause,
can use aggregate functions, and the table-valued function can be just
one of several data sources in a join.
For example, to get a list of all indexed columns in a schema, one
could query:
<blockquote><pre>
SELECT DISTINCT m.name || '.' || ii.name AS 'indexed-columns'
FROM sqlite_schema AS m,
pragma_index_list(m.name) AS il,
pragma_index_info(il.name) AS ii
WHERE m.type='table'
ORDER BY 1;
</pre></blockquote>
<p>
Additional notes:
<ul>
<li><p>
Table-valued functions exist only for built-in PRAGMAs, not for PRAGMAs
defined using the <a href="c3ref/c_fcntl_begin_atomic_write.html#sqlitefcntlpragma">SQLITE_FCNTL_PRAGMA</a> file control.
<li><p>
Table-valued functions exist only for PRAGMAs that return results and
that have no side-effects.
<li><p>
This feature could be used to implement
<a href="https://en.wikipedia.org/wiki/Information_schema">information schema</a>
by first creating a separate schema using
<blockquote><pre>
<a href="lang_attach.html">ATTACH</a> ':memory:' AS 'information_schema';
</pre></blockquote>
Then creating
<a href="lang_createview.html">VIEWs</a> in that schema that implement the official information schema
tables using table-valued PRAGMA functions.
<li><p>
The table-valued functions for PRAGMA feature was added
in SQLite version 3.16.0 (2017-01-02). Prior versions of SQLite
cannot use this feature.
</ul>
<hr /><a name="toc"></a>
<h2>List Of PRAGMAs</h2>
<div class='columns' style='columns: 12em auto;'>
<ul style='padding-top:0;'>
<li><a href='pragma.html#pragma_analysis_limit'>analysis_limit</a></li>
<li><a href='pragma.html#pragma_application_id'>application_id</a></li>
<li><a href='pragma.html#pragma_auto_vacuum'>auto_vacuum</a></li>
<li><a href='pragma.html#pragma_automatic_index'>automatic_index</a></li>
<li><a href='pragma.html#pragma_busy_timeout'>busy_timeout</a></li>
<li><a href='pragma.html#pragma_cache_size'>cache_size</a></li>
<li><a href='pragma.html#pragma_cache_spill'>cache_spill</a></li>
<li><a href='pragma.html#pragma_case_sensitive_like'><s>case_sensitive_like¹</s></a></li>
<li><a href='pragma.html#pragma_cell_size_check'>cell_size_check</a></li>
<li><a href='pragma.html#pragma_checkpoint_fullfsync'>checkpoint_fullfsync</a></li>
<li><a href='pragma.html#pragma_collation_list'>collation_list</a></li>
<li><a href='pragma.html#pragma_compile_options'>compile_options</a></li>
<li><a href='pragma.html#pragma_count_changes'><s>count_changes¹</s></a></li>
<li><a href='pragma.html#pragma_data_store_directory'><s>data_store_directory¹</s></a></li>
<li><a href='pragma.html#pragma_data_version'>data_version</a></li>
<li><a href='pragma.html#pragma_database_list'>database_list</a></li>
<li><a href='pragma.html#pragma_default_cache_size'><s>default_cache_size¹</s></a></li>
<li><a href='pragma.html#pragma_defer_foreign_keys'>defer_foreign_keys</a></li>
<li><a href='pragma.html#pragma_empty_result_callbacks'><s>empty_result_callbacks¹</s></a></li>
<li><a href='pragma.html#pragma_encoding'>encoding</a></li>
<li><a href='pragma.html#pragma_foreign_key_check'>foreign_key_check</a></li>
<li><a href='pragma.html#pragma_foreign_key_list'>foreign_key_list</a></li>
<li><a href='pragma.html#pragma_foreign_keys'>foreign_keys</a></li>
<li><a href='pragma.html#pragma_freelist_count'>freelist_count</a></li>
<li><a href='pragma.html#pragma_full_column_names'><s>full_column_names¹</s></a></li>
<li><a href='pragma.html#pragma_fullfsync'>fullfsync</a></li>
<li><a href='pragma.html#pragma_function_list'>function_list</a></li>
<li><a href='pragma.html#pragma_hard_heap_limit'>hard_heap_limit</a></li>
<li><a href='pragma.html#pragma_ignore_check_constraints'>ignore_check_constraints</a></li>
<li><a href='pragma.html#pragma_incremental_vacuum'>incremental_vacuum</a></li>
<li><a href='pragma.html#pragma_index_info'>index_info</a></li>
<li><a href='pragma.html#pragma_index_list'>index_list</a></li>
<li><a href='pragma.html#pragma_index_xinfo'>index_xinfo</a></li>
<li><a href='pragma.html#pragma_integrity_check'>integrity_check</a></li>
<li><a href='pragma.html#pragma_journal_mode'>journal_mode</a></li>
<li><a href='pragma.html#pragma_journal_size_limit'>journal_size_limit</a></li>
<li><a href='pragma.html#pragma_legacy_alter_table'>legacy_alter_table</a></li>
<li><a href='pragma.html#pragma_legacy_file_format'>legacy_file_format</a></li>
<li><a href='pragma.html#pragma_locking_mode'>locking_mode</a></li>
<li><a href='pragma.html#pragma_max_page_count'>max_page_count</a></li>
<li><a href='pragma.html#pragma_mmap_size'>mmap_size</a></li>
<li><a href='pragma.html#pragma_module_list'>module_list</a></li>
<li><a href='pragma.html#pragma_optimize'>optimize</a></li>
<li><a href='pragma.html#pragma_page_count'>page_count</a></li>
<li><a href='pragma.html#pragma_page_size'>page_size</a></li>
<li><a href='pragma.html#pragma_parser_trace'>parser_trace²</a></li>
<li><a href='pragma.html#pragma_pragma_list'>pragma_list</a></li>
<li><a href='pragma.html#pragma_query_only'>query_only</a></li>
<li><a href='pragma.html#pragma_quick_check'>quick_check</a></li>
<li><a href='pragma.html#pragma_read_uncommitted'>read_uncommitted</a></li>
<li><a href='pragma.html#pragma_recursive_triggers'>recursive_triggers</a></li>
<li><a href='pragma.html#pragma_reverse_unordered_selects'>reverse_unordered_selects</a></li>
<li><a href='pragma.html#pragma_schema_version'>schema_version³</a></li>
<li><a href='pragma.html#pragma_secure_delete'>secure_delete</a></li>
<li><a href='pragma.html#pragma_short_column_names'><s>short_column_names¹</s></a></li>
<li><a href='pragma.html#pragma_shrink_memory'>shrink_memory</a></li>
<li><a href='pragma.html#pragma_soft_heap_limit'>soft_heap_limit</a></li>
<li><a href='pragma.html#pragma_stats'>stats³</a></li>
<li><a href='pragma.html#pragma_synchronous'>synchronous</a></li>
<li><a href='pragma.html#pragma_table_info'>table_info</a></li>
<li><a href='pragma.html#pragma_table_list'>table_list</a></li>
<li><a href='pragma.html#pragma_table_xinfo'>table_xinfo</a></li>
<li><a href='pragma.html#pragma_temp_store'>temp_store</a></li>
<li><a href='pragma.html#pragma_temp_store_directory'><s>temp_store_directory¹</s></a></li>
<li><a href='pragma.html#pragma_threads'>threads</a></li>
<li><a href='pragma.html#pragma_trusted_schema'>trusted_schema</a></li>
<li><a href='pragma.html#pragma_user_version'>user_version</a></li>
<li><a href='pragma.html#pragma_vdbe_addoptrace'>vdbe_addoptrace²</a></li>
<li><a href='pragma.html#pragma_vdbe_debug'>vdbe_debug²</a></li>
<li><a href='pragma.html#pragma_vdbe_listing'>vdbe_listing²</a></li>
<li><a href='pragma.html#pragma_vdbe_trace'>vdbe_trace²</a></li>
<li><a href='pragma.html#pragma_wal_autocheckpoint'>wal_autocheckpoint</a></li>
<li><a href='pragma.html#pragma_wal_checkpoint'>wal_checkpoint</a></li>
<li><a href='pragma.html#pragma_writable_schema'>writable_schema³</a></li>
</ul>
</div>
<p>Notes:
<ol>
<li>Pragmas whose names are <s>struck through</s>
are deprecated. Do not use them. They exist
for historical compatibility.
<li>These pragmas are only available in builds using non-standard
compile-time options.
<li>These pragmas are used for testing SQLite and are not recommended
for use in application programs.</ol></p>
<a name="pragma_analysis_limit"></a>
<h _id=pragma_analysis_limit style="display:none"> PRAGMA analysis_limit</h><hr>
<p><b>PRAGMA analysis_limit;
<br>PRAGMA analysis_limit = </b><i>N</i><b>;</b></p>
<p>Query or change a limit on the <a href="lang_analyze.html#approx">approximate ANALYZE</a> setting.
This is the approximate number of
rows examined in each index by the <a href="lang_analyze.html">ANALYZE</a> command.
If the argument <i>N</i> is omitted, then the analysis limit
is unchanged.
If the limit is zero, then the analysis limit is disabled and
the ANALYZE command will examine all rows of each index.
If N is greater than zero, then the analysis limit is set to N
and subsequent ANALYZE commands will stop analyzing
each index after it has examined approximately N rows.
If N is a negative number or something other than an integer value,
then the pragma behaves as if the N argument was omitted.
In all cases, the value returned is the new analysis limit used
for subsequent ANALYZE commands.
<p>This pragma can be used to help the ANALYZE command run faster
on large databases. The results of analysis are not as good
when only part of each index is examined, but the results are
usually good enough. Setting N to 100 or 1000 allows the
ANALYZE command to run very quickly, even on multi-gigabyte
database files. This pragma is particularly useful in combination
with <a href="pragma.html#pragma_optimize">PRAGMA optimize</a>.
<p>This pragma was added in SQLite version 3.32.0 (2020-05-22).
The current implementation only uses the lower 31 bits of the
N value - higher order bits are silently ignored. Future versions
of SQLite might begin using higher order bits.
<a name="pragma_application_id"></a>
<h _id=pragma_application_id style="display:none"> PRAGMA application_id</h><hr>
<p><b>PRAGMA </b><i>schema.</i><b>application_id;
<br>PRAGMA </b><i>schema.</i><b>application_id = </b><i>integer </i><b>;</b>
<p>The application_id PRAGMA is used to query or set the 32-bit
signed big-endian "Application ID" integer located at offset
68 into the <a href="fileformat2.html#database_header">database header</a>. Applications that use SQLite as their
<a href="appfileformat.html">application file-format</a> should set the Application ID integer to
a unique integer so that utilities such as
<a href="http://www.darwinsys.com/file/">file(1)</a> can determine the specific
file type rather than just reporting "SQLite3 Database". A list of
assigned application IDs can be seen by consulting the
<a href="https://www.sqlite.org/src/artifact?ci=trunk&filename=magic.txt">magic.txt</a> file in the SQLite source repository.
<p> See also the <a href="pragma.html#pragma_user_version">user_version pragma</a>.
<a name="pragma_auto_vacuum"></a>
<h _id=pragma_auto_vacuum style="display:none"> PRAGMA auto_vacuum</h><hr>
<p><b>PRAGMA </b><i>schema.</i><b>auto_vacuum;<br>
PRAGMA </b><i>schema.</i><b>auto_vacuum = </b>
<i>0 | NONE | 1 | FULL | 2 | INCREMENTAL</i><b>;</b></p>
<p>Query or set the auto-vacuum status in the database.</p>
<p>The default setting for auto-vacuum is 0 or "none",
unless the <a href="compile.html#default_autovacuum">SQLITE_DEFAULT_AUTOVACUUM</a> compile-time option is used.
The "none" setting means that auto-vacuum is disabled.
When auto-vacuum is disabled and data is deleted data from a database,
the database file remains the same size. Unused database file
pages are added to a "<a href="fileformat2.html#freelist">freelist</a>" and reused for subsequent inserts. So
no database file space is lost. However, the database file does not
shrink. In this mode the <a href="lang_vacuum.html">VACUUM</a>
command can be used to rebuild the entire database file and
thus reclaim unused disk space.</p>
<p>When the auto-vacuum mode is 1 or "full", the freelist pages are
moved to the end of the database file and the database file is truncated
to remove the freelist pages at every transaction commit.
Note, however, that auto-vacuum only truncates the freelist pages
from the file. Auto-vacuum does not defragment the database nor
repack individual database pages the way that the
<a href="lang_vacuum.html">VACUUM</a> command does. In fact, because
it moves pages around within the file, auto-vacuum can actually
make fragmentation worse.</p>
<p>Auto-vacuuming is only possible if the database stores some
additional information that allows each database page to be
traced backwards to its referrer. Therefore, auto-vacuuming must
be turned on before any tables are created. It is not possible
to enable or disable auto-vacuum after a table has been created.</p>
<p>When the value of auto-vacuum is 2 or "incremental" then the additional
information needed to do auto-vacuuming is stored in the database file
but auto-vacuuming does not occur automatically at each commit as it
does with auto_vacuum=full. In incremental mode, the separate
<a href="pragma.html#pragma_incremental_vacuum">incremental_vacuum</a> pragma must
be invoked to cause the auto-vacuum to occur.</p>
<p>The database connection can be changed between full and incremental
autovacuum mode at any time. However, changing from
"none" to "full" or "incremental" can only occur when the database
is new (no tables
have yet been created) or by running the <a href="lang_vacuum.html">VACUUM</a> command. To
change auto-vacuum modes, first use the auto_vacuum pragma to set
the new desired mode, then invoke the <a href="lang_vacuum.html">VACUUM</a> command to
reorganize the entire database file. To change from "full" or
"incremental" back to "none" always requires running <a href="lang_vacuum.html">VACUUM</a> even
on an empty database.
</p>
<p>When the auto_vacuum pragma is invoked with no arguments, it
returns the current auto_vacuum mode.</p>
<a name="pragma_automatic_index"></a>
<h _id=pragma_automatic_index style="display:none"> PRAGMA automatic_index</h><hr>
<p><b>PRAGMA automatic_index;
<br>PRAGMA automatic_index = </b><i>boolean</i><b>;</b></p>
<p>Query, set, or clear the <a href="optoverview.html#autoindex">automatic indexing</a> capability.
<p><a href="optoverview.html#autoindex">Automatic indexing</a> is enabled by default as of
<a href="releaselog/3_7_17.html">version 3.7.17</a> (2013-05-20),
but this might change in future releases of SQLite.
<a name="pragma_busy_timeout"></a>
<h _id=pragma_busy_timeout style="display:none"> PRAGMA busy_timeout</h><hr>
<p><b>PRAGMA busy_timeout;
<br>PRAGMA busy_timeout = </b><i>milliseconds</i><b>;</b></p>
<p>Query or change the setting of the
<a href="c3ref/busy_timeout.html">busy timeout</a>.
This pragma is an alternative to the <a href="c3ref/busy_timeout.html">sqlite3_busy_timeout()</a> C-language
interface which is made available as a pragma for use with language
bindings that do not provide direct access to <a href="c3ref/busy_timeout.html">sqlite3_busy_timeout()</a>.
<p>Each database connection can only have a single
<a href="c3ref/busy_handler.html">busy handler</a>. This PRAGMA sets the busy handler
for the process, possibly overwriting any previously set busy handler.
<a name="pragma_cache_size"></a>
<h _id=pragma_cache_size style="display:none"> PRAGMA cache_size</h><hr>
<p><b>PRAGMA </b><i>schema.</i><b>cache_size;
<br>PRAGMA </b><i>schema.</i><b>cache_size = </b><i>pages</i><b>;
<br>PRAGMA </b><i>schema.</i><b>cache_size = -</b><i>kibibytes</i><b>;</b></p>
<p>Query or change the suggested maximum number of database disk pages
that SQLite will hold in memory at once per open database file. Whether
or not this suggestion is honored is at the discretion of the
<a href="c3ref/pcache_methods2.html">Application Defined Page Cache</a>.
The default page cache that is built into SQLite honors the request,
however alternative application-defined page cache implementations
may choose to interpret the suggested cache size in different ways
or to ignore it all together.
The default suggested cache size is -2000, which means the cache size
is limited to 2048000 bytes of memory.
The default suggested cache size can be altered using the
<a href="compile.html#default_cache_size">SQLITE_DEFAULT_CACHE_SIZE</a> compile-time options.
The TEMP database has a default suggested cache size of 0 pages.</p>
<p>If the argument N is positive then the suggested cache size is set
to N. If the argument N is negative, then the
number of cache pages is adjusted to be a number of pages that would
use approximately abs(N*1024) bytes of memory based on the current
page size. SQLite remembers the number of pages in the page cache,
not the amount of memory used. So if you set the cache size using
a negative number and subsequently change the page size (using the
<a href="pragma.html#pragma_page_size">PRAGMA page_size</a> command) then the maximum amount of cache
memory will go up or down in proportion to the change in page size.
<p><i>Backwards compatibility note:</i>
The behavior of cache_size with a negative N
was different prior to <a href="releaselog/3_7_10.html">version 3.7.10</a> (2012-01-16). In
earlier versions, the number of pages in the cache was set
to the absolute value of N.</p>
<p>When you change the cache size using the cache_size pragma, the
change only endures for the current session. The cache size reverts
to the default value when the database is closed and reopened.</p>
<p>The default page cache implemention does not allocate
the full amount of cache memory all at once. Cache memory
is allocated in smaller chunks on an as-needed basis. The page_cache
setting is a (suggested) upper bound on the amount of memory that the
cache can use, not the amount of memory it will use all of the time.
This is the behavior of the default page cache implementation, but an
<a href="c3ref/pcache_methods2.html">application defined page cache</a> is free
to behave differently if it wants.
<a name="pragma_cache_spill"></a>
<h _id=pragma_cache_spill style="display:none"> PRAGMA cache_spill</h><hr>
<p><b>PRAGMA cache_spill;
<br>PRAGMA cache_spill=</b><i>boolean</i><b>;
<br>PRAGMA </b><i>schema.</i><b>cache_spill=<i>N</i>;</b></p>
<p>The cache_spill pragma enables or disables the ability of the pager
to spill dirty cache pages to the database file in the middle of a
transaction. Cache_spill is enabled by default and most applications
should leave it that way as cache spilling is usually advantageous.
However, a cache spill has the side-effect of acquiring an
<a href="lockingv3.html#excl_lock">EXCLUSIVE lock</a> on the database file. Hence, some applications that
have large long-running transactions may want to disable cache spilling
in order to prevent the application from acquiring an exclusive lock
on the database until the moment that the transaction <a href="lang_transaction.html">COMMIT</a>s.
<p>The "PRAGMA cache_spill=<i>N</i>" form of this pragma sets a minimum
cache size threshold required for spilling to occur. The number of pages
in cache must exceed both the cache_spill threshold and the maximum cache
size set by the <a href="pragma.html#pragma_cache_size">PRAGMA cache_size</a> statement in order for spilling to
occur.
<p>The "PRAGMA cache_spill=<i>boolean</i>" form of this pragma applies
across all databases attached to the database connection. But the
"PRAGMA cache_spill=<i>N</i>" form of this statement only applies to
the "main" schema or whatever other schema is specified as part of the
statement.
<a name="pragma_case_sensitive_like"></a>
<h _id=pragma_case_sensitive_like style="display:none"> PRAGMA case_sensitive_like</h><hr>
<p><b>PRAGMA case_sensitive_like = </b><i>boolean</i><b>;</b></p>
<p>The default behavior of the <a href="lang_expr.html#like">LIKE</a> operator is to ignore case
for ASCII characters. Hence, by default <b>'a' LIKE 'A'</b> is
true. The case_sensitive_like pragma installs a new application-defined
LIKE function that is either case sensitive or insensitive depending
on the value of the case_sensitive_like pragma.
When case_sensitive_like is disabled, the default LIKE behavior is
expressed. When case_sensitive_like is enabled, case becomes
significant. So, for example,
<b>'a' LIKE 'A'</b> is false but <b>'a' LIKE 'a'</b> is still true.</p>
<p>This pragma uses <a href="c3ref/create_function.html">sqlite3_create_function()</a> to overload the
LIKE and GLOB functions, which may override previous implementations
of LIKE and GLOB registered by the application. This pragma
only changes the behavior of the SQL <a href="lang_expr.html#like">LIKE</a> operator. It does not
change the behavior of the <a href="c3ref/strlike.html">sqlite3_strlike()</a> C-language interface,
which is always case insensitive.</p>
<p><b>WARNING:</b> If a database uses the LIKE operator anywhere in
the schema, such as in a <a href="lang_createtable.html#ckconst">CHECK constraint</a> or in an
<a href="expridx.html">expression index</a> or in the WHERE clause of a <a href="partialindex.html">partial index</a>, then
changing the definition of the LIKE operator using this PRAGMA can
cause the database to appear to be corrupt. <a href="pragma.html#pragma_integrity_check">PRAGMA integrity_check</a>
will report errors. The database is not really corrupt in that
changing the behavior of LIKE back to the way
it was when the schema was defined and the database was populated
will clear the problem. If the use of LIKE occurs only in indexes,
then the problem can be cleared by running <a href="lang_reindex.html">REINDEX</a>. Nevertheless,
the use of the case_sensitive_like pragma is discouraged.</p>
<p style='background-color: #ffd0d0;'>
<b>This pragma is deprecated</b> and exists
for backwards compatibility only. New applications
should avoid using this pragma. Older applications should discontinue
use of this pragma at the earliest opportunity. This pragma may be omitted
from the build when SQLite is compiled using <a href="compile.html#omit_deprecated">SQLITE_OMIT_DEPRECATED</a>.
</p>
<a name="pragma_cell_size_check"></a>
<h _id=pragma_cell_size_check style="display:none"> PRAGMA cell_size_check</h><hr>
<p><b>PRAGMA cell_size_check
<br>PRAGMA cell_size_check = </b><i>boolean</i><b>;</b></p>
<p>The cell_size_check pragma enables or disables additional sanity
checking on database b-tree pages as they are initially read from disk.
With cell size checking enabled, database corruption is detected earlier
and is less likely to "spread". However, there is a small performance
hit for doing the extra checks and so cell size checking is turned off
by default.
<a name="pragma_checkpoint_fullfsync"></a>
<h _id=pragma_checkpoint_fullfsync style="display:none"> PRAGMA checkpoint_fullfsync</h><hr>
<p><b>PRAGMA checkpoint_fullfsync
<br>PRAGMA checkpoint_fullfsync = </b><i>boolean</i><b>;</b></p>
<p>Query or change the fullfsync flag for <a href="wal.html#ckpt">checkpoint</a> operations.
If this flag is set, then the F_FULLFSYNC syncing method is used
during checkpoint operations on systems that support F_FULLFSYNC.
The default value of the checkpoint_fullfsync flag
is off. Only Mac OS-X supports F_FULLFSYNC.</p>
<p>If the <a href="pragma.html#pragma_fullfsync">fullfsync</a> flag is set, then the F_FULLFSYNC syncing
method is used for all sync operations and the checkpoint_fullfsync
setting is irrelevant.</p>
<a name="pragma_collation_list"></a>
<h _id=pragma_collation_list style="display:none"> PRAGMA collation_list</h><hr>
<p><b>PRAGMA collation_list;</b></p>
<p>Return a list of the collating sequences defined for the current
database connection.</p>
<a name="pragma_compile_options"></a>
<h _id=pragma_compile_options style="display:none"> PRAGMA compile_options</h><hr>
<p><b>PRAGMA compile_options;</b></p>
<p>This pragma returns the names of <a href="compile.html">compile-time options</a> used when
building SQLite, one option per row. The "SQLITE_" prefix is omitted
from the returned option names. See also the
<a href="c3ref/compileoption_get.html">sqlite3_compileoption_get()</a> C/C++ interface and the
<a href="lang_corefunc.html#sqlite_compileoption_get">sqlite_compileoption_get()</a> SQL functions.</p>
<a name="pragma_count_changes"></a>
<h _id=pragma_count_changes style="display:none"> PRAGMA count_changes</h><hr>
<p><b>PRAGMA count_changes;
<br>PRAGMA count_changes = </b>boolean</i><b>;</b></p>
<p>Query or change the count-changes flag. Normally, when the
count-changes flag is not set, <a href="lang_insert.html">INSERT</a>, <a href="lang_update.html">UPDATE</a> and <a href="lang_delete.html">DELETE</a> statements
return no data. When count-changes is set, each of these commands
returns a single row of data consisting of one integer value - the
number of rows inserted, modified or deleted by the command. The
returned change count does not include any insertions, modifications
or deletions performed by triggers, any changes made automatically
by <a href="foreignkeys.html#fk_actions">foreign key actions</a>, or updates caused by an <a href="lang_upsert.html">upsert</a>.</p>
<p>Another way to get the row change counts is to use the
<a href="c3ref/changes.html">sqlite3_changes()</a> or <a href="c3ref/total_changes.html">sqlite3_total_changes()</a> interfaces.
There is a subtle different, though. When an INSERT, UPDATE, or
DELETE is run against a view using an <a href="lang_createtrigger.html#instead_of_trigger">INSTEAD OF trigger</a>,
the count_changes pragma reports the number of rows in the view
that fired the trigger, whereas <a href="c3ref/changes.html">sqlite3_changes()</a> and
<a href="c3ref/total_changes.html">sqlite3_total_changes()</a> do not.
<p style='background-color: #ffd0d0;'>
<b>This pragma is deprecated</b> and exists
for backwards compatibility only. New applications
should avoid using this pragma. Older applications should discontinue
use of this pragma at the earliest opportunity. This pragma may be omitted
from the build when SQLite is compiled using <a href="compile.html#omit_deprecated">SQLITE_OMIT_DEPRECATED</a>.
</p>
<a name="pragma_data_store_directory"></a>
<h _id=pragma_data_store_directory style="display:none"> PRAGMA data_store_directory</h><hr>
<p><b>PRAGMA data_store_directory;
<br>PRAGMA data_store_directory = '</b><i>directory-name</i><b>';</b></p>
<p>Query or change the value of the <a href="c3ref/data_directory.html">sqlite3_data_directory</a> global
variable, which windows operating-system interface backends use to
determine where to store database files specified using a relative
pathname.</p>
<p>Changing the data_store_directory setting is <u>not</u> threadsafe.
Never change the data_store_directory setting if another thread
within the application is running any SQLite interface at the same time.
Doing so results in undefined behavior. Changing the data_store_directory
setting writes to the <a href="c3ref/data_directory.html">sqlite3_data_directory</a> global
variable and that global variable is not protected by a mutex.</p>
<p>This facility is provided for WinRT which does not have an OS
mechanism for reading or changing the current working directory.
The use of this pragma in any other context is discouraged and may
be disallowed in future releases.</p>
<p style='background-color: #ffd0d0;'>
<b>This pragma is deprecated</b> and exists
for backwards compatibility only. New applications
should avoid using this pragma. Older applications should discontinue
use of this pragma at the earliest opportunity. This pragma may be omitted
from the build when SQLite is compiled using <a href="compile.html#omit_deprecated">SQLITE_OMIT_DEPRECATED</a>.
</p>
<a name="pragma_data_version"></a>
<h _id=pragma_data_version style="display:none"> PRAGMA data_version</h><hr>
<p><b>PRAGMA </b><i>schema.</i><b>data_version;</b></p>
<p>The "PRAGMA data_version" command provides an indication that the
database file has been modified.
Interactive programs that hold database content in memory or that
display database content on-screen can use the PRAGMA data_version
command to determine if they need to flush and reload their memory
or update the screen display.</p>
<p>The integer values returned by two
invocations of "PRAGMA data_version" from the same connection
will be different if changes were committed to the database
by any other connection in the interim.
The "PRAGMA data_version" value is unchanged for commits made
on the same database connection.
The behavior of "PRAGMA data_version" is the same for all database
connections, including database connections in separate processes
and <a href="sharedcache.html">shared cache</a> database connections.
<p>The "PRAGMA data_version" value is a local property of each
database connection and so values returned by two concurrent invocations
of "PRAGMA data_version" on separate database connections are
often different even though the underlying database is identical.
It is only meaningful to compare the "PRAGMA data_version" values
returned by the same database connection at two different points in
time.
<a name="pragma_database_list"></a>
<h _id=pragma_database_list style="display:none"> PRAGMA database_list</h><hr>
<p><b>PRAGMA database_list;</b></p>
<p>This pragma works like a query to return one row for each database
attached to the current database connection.
The second column is "main" for the main database file, "temp"
for the database file used to store TEMP objects, or the name of the
ATTACHed database for other database files.
The third column is the name of the database file itself, or an empty
string if the database is not associated with a file.</p>
<a name="pragma_default_cache_size"></a>
<h _id=pragma_default_cache_size style="display:none"> PRAGMA default_cache_size</h><hr>
<b>PRAGMA </b><i>schema.</i><b>default_cache_size;
<br>PRAGMA </b><i>schema.</i><b>default_cache_size
= </b><i>Number-of-pages</i><b>;</b></p>
<p>This pragma queries or sets the suggested maximum number of pages
of disk cache that will be allocated per open database file.
The difference between this pragma and <a href="pragma.html#pragma_cache_size">cache_size</a> is that the
value set here persists across database connections.
The value of the default cache size is stored in the 4-byte
big-endian integer located at offset 48 in the header of the
database file.
</p>
<p style='background-color: #ffd0d0;'>
<b>This pragma is deprecated</b> and exists
for backwards compatibility only. New applications
should avoid using this pragma. Older applications should discontinue
use of this pragma at the earliest opportunity. This pragma may be omitted
from the build when SQLite is compiled using <a href="compile.html#omit_deprecated">SQLITE_OMIT_DEPRECATED</a>.
</p>
<a name="pragma_defer_foreign_keys"></a>
<h _id=pragma_defer_foreign_keys style="display:none"> PRAGMA defer_foreign_keys</h><hr>
<p><b>PRAGMA defer_foreign_keys
<br>PRAGMA defer_foreign_keys = </b><i>boolean</i><b>;</b></p>
<p>When the defer_foreign_keys <a href="pragma.html#syntax">PRAGMA</a> is on,
enforcement of all <a href="foreignkeys.html">foreign key constraints</a> is delayed until the
outermost transaction is committed. The defer_foreign_keys pragma
defaults to OFF so that foreign key constraints are only deferred if
they are created as "DEFERRABLE INITIALLY DEFERRED". The
defer_foreign_keys pragma is automatically switched off at each
COMMIT or ROLLBACK. Hence, the defer_foreign_keys pragma must be
separately enabled for each transaction. This pragma is
only meaningful if foreign key constraints are enabled, of course.</p>
<p>The <a href="c3ref/db_status.html">sqlite3_db_status</a>(db,<a href="c3ref/c_dbstatus_options.html#sqlitedbstatusdeferredfks">SQLITE_DBSTATUS_DEFERRED_FKS</a>,...)
C-language interface can be used during a transaction to determine
if there are deferred and unresolved foreign key constraints.</p>
<a name="pragma_empty_result_callbacks"></a>
<h _id=pragma_empty_result_callbacks style="display:none"> PRAGMA empty_result_callbacks</h><hr>
<p><b>PRAGMA empty_result_callbacks;
<br>PRAGMA empty_result_callbacks = </b><i>boolean</i><b>;</b></p>
<p>Query or change the empty-result-callbacks flag.</p>
<p>The empty-result-callbacks flag affects the <a href="c3ref/exec.html">sqlite3_exec()</a> API only.
Normally, when the empty-result-callbacks flag is cleared, the
callback function supplied to the <a href="c3ref/exec.html">sqlite3_exec()</a> is not invoked
for commands that return zero rows of data. When empty-result-callbacks
is set in this situation, the callback function is invoked exactly once,
with the third parameter set to 0 (NULL). This is to enable programs
that use the <a href="c3ref/exec.html">sqlite3_exec()</a> API to retrieve column-names even when
a query returns no data.</p>
<p style='background-color: #ffd0d0;'>
<b>This pragma is deprecated</b> and exists
for backwards compatibility only. New applications
should avoid using this pragma. Older applications should discontinue
use of this pragma at the earliest opportunity. This pragma may be omitted
from the build when SQLite is compiled using <a href="compile.html#omit_deprecated">SQLITE_OMIT_DEPRECATED</a>.
</p>
<a name="pragma_encoding"></a>
<h _id=pragma_encoding style="display:none"> PRAGMA encoding</h><hr>
<p><b>PRAGMA encoding;
<br>PRAGMA encoding = 'UTF-8';
<br>PRAGMA encoding = 'UTF-16';
<br>PRAGMA encoding = 'UTF-16le';
<br>PRAGMA encoding = 'UTF-16be';</b></p>
<p>In first form, if the main database has already been
created, then this pragma returns the text encoding used by the
main database, one of 'UTF-8', 'UTF-16le' (little-endian UTF-16
encoding) or 'UTF-16be' (big-endian UTF-16 encoding). If the main
database has not already been created, then the value returned is the
text encoding that will be used to create the main database, if
it is created by this session.</p>
<p>The second through fifth forms of this pragma
set the encoding that the main database will be created with if
it is created by this session. The string 'UTF-16' is interpreted
as "UTF-16 encoding using native machine byte-ordering". It is not
possible to change the text encoding of a database after it has been
created and any attempt to do so will be silently ignored.</p>
<p>If no encoding is first set with this pragma,
then the encoding with which the main database will be created
defaults to one determined by the
<a href="c3ref/open.html">API used to open the connection</a>.</p>
<p>Once an encoding has been set for a database, it cannot be changed.</p>
<p>Databases created by the <a href="lang_attach.html">ATTACH</a> command always use the same encoding
as the main database. An attempt to <a href="lang_attach.html">ATTACH</a> a database with a different
text encoding from the "main" database will fail.</p>
<a name="pragma_foreign_key_check"></a>
<h _id=pragma_foreign_key_check style="display:none"> PRAGMA foreign_key_check</h><hr>
<p><b>PRAGMA </b><i>schema.</i><b>foreign_key_check;
<br>PRAGMA </b><i>schema.</i><b>foreign_key_check(</b><i>table-name</i><b>);</b></b></p>
<p>The foreign_key_check pragma checks the database, or the table
called "<i>table-name</i>", for
<a href="foreignkeys.html">foreign key constraints</a> that are violated. The foreign_key_check
pragma returns one row output for each foreign key violation.
There are four columns in each result row.
The first column is the name of the table that contains the REFERENCES
clause. The second column is the <a href="lang_createtable.html#rowid">rowid</a> of the row that
contains the invalid REFERENCES clause, or NULL if the child table is a
<a href="withoutrowid.html">WITHOUT ROWID</a> table. The third column is the name
of the table that is referred to. The fourth column is the index of
the specific foreign key constraint that failed. The fourth column
in the output of the foreign_key_check pragma is the same integer as
the first column in the output of the <a href="pragma.html#pragma_foreign_key_list">foreign_key_list pragma</a>.
When a "<i>table-name</i>" is specified, the only foreign key constraints
checked are those created by REFERENCES clauses in the
CREATE TABLE statement for <i>table-name</i>.</p>
<a name="pragma_foreign_key_list"></a>
<h _id=pragma_foreign_key_list style="display:none"> PRAGMA foreign_key_list</h><hr>
<p><b>PRAGMA foreign_key_list(</b><i>table-name</i><b>);</b></p>
<p>This pragma returns one row for each <a href="foreignkeys.html">foreign key constraint</a>
created by a REFERENCES clause in the CREATE TABLE statement of
table "<i>table-name</i>".
<a name="pragma_foreign_keys"></a>
<h _id=pragma_foreign_keys style="display:none"> PRAGMA foreign_keys</h><hr>
<p><b>PRAGMA foreign_keys;
<br>PRAGMA foreign_keys = </b><i>boolean</i><b>;</b></p>
<p>Query, set, or clear the enforcement of <a href="foreignkeys.html">foreign key constraints</a>.
<p>This pragma is a no-op within a transaction; foreign key constraint
enforcement may only be enabled or disabled when there is no pending
<a href="lang_transaction.html">BEGIN</a> or <a href="lang_savepoint.html">SAVEPOINT</a>.
<p>Changing the foreign_keys setting affects the execution of
all statements prepared
using the database connection, including those prepared before the
setting was changed. Any existing statements prepared using the legacy
<a href="c3ref/prepare.html">sqlite3_prepare()</a> interface may fail with an <a href="rescode.html#schema">SQLITE_SCHEMA</a> error
after the foreign_keys setting is changed.
<p>As of SQLite <a href="releaselog/3_6_19.html">version 3.6.19</a>, the default setting for foreign
key enforcement is OFF. However, that might change in a future
release of SQLite. The default setting for foreign key enforcement
can be specified at compile-time using the <a href="compile.html#default_foreign_keys">SQLITE_DEFAULT_FOREIGN_KEYS</a>
preprocessor macro. To minimize future problems, applications should
set the foreign key enforcement flag as required by the application
and not depend on the default setting.
<a name="pragma_freelist_count"></a>
<h _id=pragma_freelist_count style="display:none"> PRAGMA freelist_count</h><hr>
<p><b>PRAGMA </b><i>schema.</i><b>freelist_count;</b></p>
<p>Return the number of unused pages in the database file.</p>
<a name="pragma_full_column_names"></a>
<h _id=pragma_full_column_names style="display:none"> PRAGMA full_column_names</h><hr>
<p><b>PRAGMA full_column_names;
<br>PRAGMA full_column_names = </b><i>boolean</i><b>;</b></p>
<p>Query or change the full_column_names flag. This flag together
with the <a href="pragma.html#pragma_short_column_names">short_column_names</a> flag determine
the way SQLite assigns names to result columns of <a href="lang_select.html">SELECT</a> statements.
Result columns are named by applying the following rules in order:
<ol>
<li><p>If there is an AS clause on the result, then the name of
the column is the right-hand side of the AS clause.</p></li>
<li><p>If the result is a general expression, not a just the name of
a source table column,
then the name of the result is a copy of the expression text.</p></li>
<li><p>If the <a href="pragma.html#pragma_short_column_names">short_column_names</a> pragma is ON, then the name of the
result is the name of the source table column without the
source table name prefix: COLUMN.</p></li>
<li><p>If both pragmas <a href="pragma.html#pragma_short_column_names">short_column_names</a> and <a href="pragma.html#pragma_full_column_names">full_column_names</a>
are OFF then case (2) applies.
</p></li>
<li><p>The name of the result column is a combination of the source table
and source column name: TABLE.COLUMN</p></li>
</ol>
<p style='background-color: #ffd0d0;'>
<b>This pragma is deprecated</b> and exists
for backwards compatibility only. New applications
should avoid using this pragma. Older applications should discontinue
use of this pragma at the earliest opportunity. This pragma may be omitted
from the build when SQLite is compiled using <a href="compile.html#omit_deprecated">SQLITE_OMIT_DEPRECATED</a>.
</p>
<a name="pragma_fullfsync"></a>
<h _id=pragma_fullfsync style="display:none"> PRAGMA fullfsync</h><hr>
<p><b>PRAGMA fullfsync
<br>PRAGMA fullfsync = </b><i>boolean</i><b>;</b></p>
<p>Query or change the fullfsync flag. This flag
determines whether or not the F_FULLFSYNC syncing method is used
on systems that support it. The default value of the fullfsync flag
is off. Only Mac OS X supports F_FULLFSYNC.</p>
<p>See also <a href="pragma.html#pragma_checkpoint_fullfsync">checkpoint_fullfsync</a>.</p>
<a name="pragma_function_list"></a>
<h _id=pragma_function_list style="display:none"> PRAGMA function_list</h><hr>
<p><b>PRAGMA function_list;</b>
<p>This pragma returns a list of SQL functions
known to the database connection. Each row of the result
describes a single calling signature for a single SQL function.
Some SQL functions will have multiple rows in the result set
if they can (for example) be invoked with a varying number of
arguments or can accept text in various encodings.
<a name="pragma_hard_heap_limit"></a>
<h _id=pragma_hard_heap_limit style="display:none"> PRAGMA hard_heap_limit</h><hr>
<p><b>PRAGMA hard_heap_limit<br>
PRAGMA hard_heap_limit=</b><i>N</i></p>
<p>This pragma invokes the <a href="c3ref/hard_heap_limit64.html">sqlite3_hard_heap_limit64()</a> interface with
the argument N, if N is specified and N is a positive integer that
is less than the current hard heap limit.
The hard_heap_limit pragma always returns the same integer
that would be returned by the <a href="c3ref/hard_heap_limit64.html">sqlite3_hard_heap_limit64</a>(-1) C-language
function. That is to say, it always returns the value of the hard
heap limit that is set after any changes imposed by this PRAGMA.
</p>
<p>This pragma can only lower the heap limit, never raise it.
The C-language interface <a href="c3ref/hard_heap_limit64.html">sqlite3_hard_heap_limit64()</a> must be used
to raise the heap limit.</p>
<p>See also the <a href="pragma.html#pragma_soft_heap_limit">soft_heap_limit pragma</a>.
<a name="pragma_ignore_check_constraints"></a>
<h _id=pragma_ignore_check_constraints style="display:none"> PRAGMA ignore_check_constraints</h><hr>
<p><b>PRAGMA ignore_check_constraints = </b><i>boolean</i><b>;</b></p>
<p>This pragma enables or disables the enforcement of CHECK constraints.
The default setting is off, meaning that CHECK constraints are
enforced by default.</p>
<a name="pragma_incremental_vacuum"></a>
<h _id=pragma_incremental_vacuum style="display:none"> PRAGMA incremental_vacuum</h><hr>
<p><b>PRAGMA </b><i>schema.</i><b>incremental_vacuum</b><i>(N)</i><b>;<br>
PRAGMA </b><i>schema.</i><b>incremental_vacuum;</b></p>
<p>The incremental_vacuum pragma causes up to <i>N</i> pages to
be removed from the <a href="fileformat2.html#freelist">freelist</a>. The database file is truncated by
the same amount. The incremental_vacuum pragma has no effect if
the database is not in
<a href="#pragma_auto_vacuum">auto_vacuum=incremental</a> mode
or if there are no pages on the freelist. If there are fewer than
<i>N</i> pages on the freelist, or if <i>N</i> is less than 1, or
if the "(<i>N</i>)" argument is omitted, then the entire
freelist is cleared.</p>
<a name="pragma_index_info"></a>
<h _id=pragma_index_info style="display:none"> PRAGMA index_info</h><hr>
<p><b>PRAGMA </b><i>schema.</i><b>index_info(</b><i>index-name</i><b>);</b></p>
<p>This pragma returns one row for each key column in the named index.
A key column is a column that is actually named in the <a href="lang_createindex.html">CREATE INDEX</a>
index statement or <a href="lang_createtable.html#uniqueconst">UNIQUE constraint</a> or <a href="lang_createtable.html#primkeyconst">PRIMARY KEY constraint</a> that
created the index. Index entries also usually contain auxiliary
columns that point back to the table row being indexed. The auxiliary
index-columns are not shown by the index_info pragma, but they are
listed by the <a href="pragma.html#pragma_index_xinfo">index_xinfo pragma</a>.</p>
<p>Output columns from the index_info pragma are as follows:
<ol>
<li>The rank of the column within the index. (0 means left-most.)
<li>The rank of the column within the table being indexed.
A value of -1 means <a href="lang_createtable.html#rowid">rowid</a> and a value of -2 means that an
<a href="expridx.html">expression</a> is being used.
<li>The name of the column being indexed. This columns is NULL
if the column is the <a href="lang_createtable.html#rowid">rowid</a> or an <a href="expridx.html">expression</a>.
</ol>
<p>If there is no index named <i>index-name</i> but there is a
<a href="withoutrowid.html">WITHOUT ROWID</a> table with that name, then (as of
SQLite <a href="releaselog/3_30_0.html">version 3.30.0</a> on 2019-10-04) this pragma returns the
PRIMARY KEY columns of the WITHOUT ROWID table as they are used
in the records of the underlying b-tree, which is to say with
duplicate columns removed.
<a name="pragma_index_list"></a>
<h _id=pragma_index_list style="display:none"> PRAGMA index_list</h><hr>
<p><b>PRAGMA </b><i>schema.</i><b>index_list(</b><i>table-name</i><b>);</b></p>
<p>This pragma returns one row for each index associated with the
given table.
<p>Output columns from the index_list pragma are as follows:
<ol>
<li>A sequence number assigned to each index for internal tracking
purposes.
<li>The name of the index.
<li>"1" if the index is UNIQUE and "0" if not.
<li>"c" if the index was created by a <a href="lang_createindex.html">CREATE INDEX</a> statement,
"u" if the index was created by a <a href="lang_createtable.html#uniqueconst">UNIQUE constraint</a>, or
"pk" if the index was created by a <a href="lang_createtable.html#primkeyconst">PRIMARY KEY constraint</a>.
<li>"1" if the index is a <a href="partialindex.html">partial index</a> and "0" if not.
</ol>
</p>
<a name="pragma_index_xinfo"></a>
<h _id=pragma_index_xinfo style="display:none"> PRAGMA index_xinfo</h><hr>
<p><b>PRAGMA </b><i>schema.</i><b>index_xinfo(</b><i>index-name</i><b>);</b></p>
<p>This pragma returns information about every column in an index.
Unlike this <a href="pragma.html#pragma_index_info">index_info pragma</a>, this pragma returns information about
every column in the index, not just the key columns.
(A key column is a column that is actually named in the <a href="lang_createindex.html">CREATE INDEX</a>
index statement or <a href="lang_createtable.html#uniqueconst">UNIQUE constraint</a> or <a href="lang_createtable.html#primkeyconst">PRIMARY KEY constraint</a> that
created the index. Auxiliary columns are additional columns needed to
locate the table entry that corresponds to each index entry.)
<p>Output columns from the index_xinfo pragma are as follows:
<ol>
<li>The rank of the column within the index. (0 means left-most.
Key columns come before auxiliary columns.)
<li>The rank of the column within the table being indexed, or -1 if
the index-column is the <a href="lang_createtable.html#rowid">rowid</a> of the table being indexed and -2
if the <a href="expridx.html">index is on an expression</a>.
<li>The name of the column being indexed, or NULL if the index-column
is the <a href="lang_createtable.html#rowid">rowid</a> of the table being indexed or an
<a href="expridx.html">expression</a>.
<li>1 if the index-column is sorted in reverse (DESC) order by the
index and 0 otherwise.
<li>The name for the <a href="datatype3.html#collation">collating sequence</a>
used to compare values in the index-column.
<li>1 if the index-column is a key column and 0 if the index-column
is an auxiliary column.
</ol>
<p>If there is no index named <i>index-name</i> but there is a
<a href="withoutrowid.html">WITHOUT ROWID</a> table with that name, then (as of
SQLite <a href="releaselog/3_30_0.html">version 3.30.0</a> on 2019-10-04) this pragma returns the
columns of the WITHOUT ROWID table as they are used
in the records of the underlying b-tree, which is to say with
de-duplicated PRIMARY KEY columns first followed by data columns.
<a name="pragma_integrity_check"></a>
<h _id=pragma_integrity_check style="display:none"> PRAGMA integrity_check</h><hr>
<p><b>PRAGMA </b><i>schema.</i><b>integrity_check;
<br>PRAGMA </b><i>schema.</i><b>integrity_check(</b><i>N</i><b>)
<br>PRAGMA </b><i>schema.</i><b>integrity_check(</b><i>TABLENAME</i><b>)</b></p>
<p>This pragma does a low-level formatting and consistency check
of the database. The integrity_check pragma look for:
<ul>
<li> Table or index entries that are out of sequence
<li> Misformatted records
<li> Missing pages
<li> Missing or surplus index entries
<li> UNIQUE, CHECK, and NOT NULL constraint errors
<li> Integrity of the freelist
<li> Sections of the database that are used more than once, or not at all
</ul>
<p>If the integrity_check pragma finds problems, strings are returned
(as multiple rows with a single column per row) which describe
the problems. Pragma integrity_check will return at most <i>N</i>
errors before the analysis quits, with N defaulting
to 100. If pragma integrity_check finds no errors, a
single row with the value 'ok' is returned.</p>
<p>The usual case is that the entire database file is checked. However,
if the argument is <i>TABLENAME</i>, then checking is only performed for the
the table named and its associated indexes.
This is called a "partial integrity check". Because only a subset of the
database is checked, errors such as unused sections of the file or duplication
use of the same section of the file by two or more tables cannot be detected.
The freelist is only verified on a
partial integrity check if <i>TABLENAME</i> is <a href="schematab.html">sqlite_schema</a> or one of its
aliases. Support for partial integrity checks was added with
version 3.33.0 (2020-08-14).
<p>PRAGMA integrity_check does not find
<a href="foreignkeys.html">FOREIGN KEY</a> errors.
Use the <a href="pragma.html#pragma_foreign_key_check">PRAGMA foreign_key_check</a> command to find errors in
FOREIGN KEY constraints.</p>
<p>See also the <a href="pragma.html#pragma_quick_check">PRAGMA quick_check</a> command which does most of the
checking of PRAGMA integrity_check but runs much faster.</p>
<a name="pragma_journal_mode"></a>
<h _id=pragma_journal_mode style="display:none"> PRAGMA journal_mode</h><hr>
<p><b>PRAGMA </b><i>schema.</i><b>journal_mode;
<br>PRAGMA </b><i>schema.</i><b>journal_mode
= <i>DELETE | TRUNCATE | PERSIST | MEMORY | WAL | OFF</i></b></p>
<p>This pragma queries or sets the journal mode for databases
associated with the current <a href="c3ref/sqlite3.html">database connection</a>.</p>
<p>The first form of this pragma queries the current journaling
mode for <i>database</i>. When <i>database</i> is omitted, the
"main" database is queried.</p>
<p>The second form changes the journaling mode for "<i>database</i>"
or for all attached databases if "<i>database</i>" is omitted.
The new journal mode is returned. If the journal mode
could not be changed, the original journal mode is returned.</p>
<p>The DELETE journaling mode is the normal behavior. In the DELETE
mode, the rollback journal is deleted at the conclusion of each
transaction. Indeed, the delete operation is the action that causes
the transaction to commit.
(See the document titled <a href="atomiccommit.html">
Atomic Commit In SQLite</a> for additional detail.)</p>
<p>The TRUNCATE journaling mode commits transactions by truncating
the rollback journal to zero-length instead of deleting it. On many
systems, truncating a file is much faster than deleting the file since
the containing directory does not need to be changed.</p>
<p>The PERSIST journaling mode prevents the rollback journal from
being deleted at the end of each transaction. Instead, the header
of the journal is overwritten with zeros. This will prevent other
database connections from rolling the journal back. The PERSIST
journaling mode is useful as an optimization on platforms where
deleting or truncating a file is much more expensive than overwriting
the first block of a file with zeros. See also:
<a href="pragma.html#pragma_journal_size_limit">PRAGMA journal_size_limit</a> and <a href="compile.html#default_journal_size_limit">SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT</a>.</p>
<p>The MEMORY journaling mode stores the rollback journal in
volatile RAM. This saves disk I/O but at the expense of database
safety and integrity. If the application using SQLite crashes in
the middle of a transaction when the MEMORY journaling mode is set,
then the database file will very likely
<a href="howtocorrupt.html#cfgerr">go corrupt</a>.</p>
<p>The WAL journaling mode uses a <a href="wal.html">write-ahead log</a> instead of a
rollback journal to implement transactions. The WAL journaling mode
is persistent; after being set it stays in effect
across multiple database connections and after closing and
reopening the database. A database in WAL journaling mode
can only be accessed by SQLite <a href="releaselog/3_7_0.html">version 3.7.0</a> (2010-07-21)
or later.</p>
<p>The OFF journaling mode disables the rollback journal completely.
No rollback journal is ever created and hence there is never a rollback
journal to delete. The OFF journaling mode disables the atomic
commit and rollback capabilities of SQLite. The <a href="lang_transaction.html">ROLLBACK</a> command
no longer works; it behaves in an undefined way. Applications must
avoid using the <a href="lang_transaction.html">ROLLBACK</a> command when the journal mode is OFF.
If the application crashes
in the middle of a transaction when the OFF journaling mode is
set, then the database file will very likely
<a href="howtocorrupt.html#cfgerr">go corrupt</a>. Without a journal, there is no way for
a statement to unwind partially completed operations following
a constraint error. This might also leave the database in a corrupted
state. For example, if a duplicate entry causes a
<a href="lang_createindex.html">CREATE UNIQUE INDEX</a> statement to fail half-way through,
it will leave behind a partially created, and hence corrupt, index.
Because OFF journaling
mode allows the database file to be corrupted using ordinary SQL,
it is disabled when <a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfigdefensive">SQLITE_DBCONFIG_DEFENSIVE</a> is enabled.</p>
<p>Note that the journal_mode for an <a href="inmemorydb.html">in-memory database</a>
is either MEMORY or OFF and can not be changed to a different value.
An attempt to change the journal_mode of an <a href="inmemorydb.html">in-memory database</a> to
any setting other than MEMORY or OFF is ignored. Note also that
the journal_mode cannot be changed while a transaction is active.</p>
<a name="pragma_journal_size_limit"></a>
<h _id=pragma_journal_size_limit style="display:none"> PRAGMA journal_size_limit</h><hr>
<p><b>
PRAGMA </b><i>schema.</i><b>journal_size_limit<br>
PRAGMA </b><i>schema.</i><b>journal_size_limit = </b><i>N</i> <b>;</b>
<p>If a database connection is operating in
<a href="pragma.html#pragma_locking_mode">exclusive locking mode</a> or in
<a href="pragma.html#pragma_journal_mode">persistent journal mode</a>
(PRAGMA journal_mode=persist) then
after committing a transaction the <a href="lockingv3.html#rollback">rollback journal</a> file may remain in
the file-system. This increases performance for subsequent transactions
since overwriting an existing file is faster than append to a file,
but it also consumes
file-system space. After a large transaction (e.g. a <a href="lang_vacuum.html">VACUUM</a>),
the rollback journal file may consume a very large amount of space.
<p>Similarly, in <a href="wal.html">WAL mode</a>, the write-ahead log file is not truncated
following a <a href="wal.html#ckpt">checkpoint</a>. Instead, SQLite reuses the existing file
for subsequent WAL entries since overwriting is faster than appending.
<p>The journal_size_limit pragma may be used to limit the size of
rollback-journal and WAL files left
in the file-system after transactions or checkpoints.
Each time a transaction is committed or a WAL file resets, SQLite
compares the size of the rollback journal file or WAL file left in
the file-system to the size limit
set by this pragma and if the journal or WAL file is larger
it is truncated to the limit.
<p>The second form of the pragma listed above is used to set a new limit
in bytes for the specified database. A negative number implies no limit.
To always truncate rollback journals and WAL files to their minimum size,
set the journal_size_limit to zero.
Both the first and second forms of the pragma listed above return a single
result row containing a single integer column - the value of the journal
size limit in bytes. The default journal size limit is -1 (no limit). The
<a href="compile.html#default_journal_size_limit">SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT</a> preprocessor macro can be used to change
the default journal size limit at compile-time.</p>
<p>This pragma only operates on the single database specified prior
to the pragma name (or on the "main" database if no database is specified.)
There is no way to change the journal size limit on all attached databases
using a single PRAGMA statement. The size limit must be set separately for
each attached database.
<a name="pragma_legacy_alter_table"></a>
<h _id=pragma_legacy_alter_table style="display:none"> PRAGMA legacy_alter_table</h><hr>
<p><b>PRAGMA legacy_alter_table;
<br>PRAGMA legacy_alter_table = <i>boolean</i></b></p>
<p>This pragma sets or queries the value of the legacy_alter_table
flag. When this flag is on, the <a href="lang_altertable.html#altertabrename">ALTER TABLE RENAME</a>
command (for changing the name of a table) works as it did
in SQLite 3.24.0 (2018-06-04) and earlier. More specifically,
when this flag is on
the <a href="lang_altertable.html#altertabrename">ALTER TABLE RENAME</a> command only rewrites the initial occurrence
of the table name in its <a href="lang_createtable.html">CREATE TABLE</a> statement and in any associated
<a href="lang_createindex.html">CREATE INDEX</a> and <a href="lang_createtrigger.html">CREATE TRIGGER</a> statements. Other references to the
table are unmodified, including:
<ul>
<li> References to the table within the bodies of triggers and views.
<li> References to the table within CHECK constraints in the original
CREATE TABLE statement.
<li> References to the table within the WHERE clauses of <a href="partialindex.html">partial indexes</a>.
</ul>
The default setting for this pragma is OFF, which means that all
references to the table anywhere in the schema are converted to the new name.
<p>This pragma is provided as a work-around for older programs that
contain code that expect the incomplete behavior
of <a href="lang_altertable.html#altertabrename">ALTER TABLE RENAME</a> found in older versions of SQLite.
New applications should leave this flag turned off.
<p>For compatibility with older <a href="vtab.html">virtual table</a> implementations,
this flag is turned on temporarily while the <a href="vtab.html#xrename">sqlite3_module.xRename</a>
method is being run. The value of this flag is restored after the
<a href="vtab.html#xrename">sqlite3_module.xRename</a> method finishes.
<p>The legacy alter table behavior can also be toggled on and off
using the <a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfiglegacyaltertable">SQLITE_DBCONFIG_LEGACY_ALTER_TABLE</a> option to the
<a href="c3ref/db_config.html">sqlite3_db_config()</a> interface.
<p>The legacy alter table behavior is a per-connection setting. Turning
this features on or off affects all attached database files within the
<a href="c3ref/sqlite3.html">database connection</a>.
The setting does not persist. Changing this setting in one connection
does not affect any other connections.
<a name="pragma_legacy_file_format"></a>
<h _id=pragma_legacy_file_format style="display:none"> PRAGMA legacy_file_format</h><hr>
<p><b>PRAGMA legacy_file_format;</b>
<p>This pragma no longer functions. It has become a no-op.
The capabilities formerly provided by PRAGMA legacy_file_format
are now available using the <a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfiglegacyfileformat">SQLITE_DBCONFIG_LEGACY_FILE_FORMAT</a>
option to the <a href="c3ref/db_config.html">sqlite3_db_config()</a> C-language interface.
<p>
<a name="pragma_locking_mode"></a>
<h _id=pragma_locking_mode style="display:none"> PRAGMA locking_mode</h><hr>
<p><b>PRAGMA </b><i>schema.</i><b>locking_mode;
<br>PRAGMA </b><i>schema.</i><b>locking_mode
= <i>NORMAL | EXCLUSIVE</i></b></p>
<p>This pragma sets or queries the database connection locking-mode.
The locking-mode is either NORMAL or EXCLUSIVE.
<p>In NORMAL locking-mode (the default unless overridden at compile-time
using <a href="compile.html#default_locking_mode">SQLITE_DEFAULT_LOCKING_MODE</a>), a database connection
unlocks the database file at the conclusion of each read or
write transaction. When the locking-mode is set to EXCLUSIVE, the
database connection never releases file-locks. The first time the
database is read in EXCLUSIVE mode, a shared lock is obtained and
held. The first time the database is written, an exclusive lock is
obtained and held.</p>
<p>Database locks obtained by a connection in EXCLUSIVE mode may be
released either by closing the database connection, or by setting the
locking-mode back to NORMAL using this pragma and then accessing the
database file (for read or write). Simply setting the locking-mode to
NORMAL is not enough - locks are not released until the next time
the database file is accessed.</p>
<p>There are three reasons to set the locking-mode to EXCLUSIVE.
<ol>
<li>The application wants to prevent other processes from
accessing the database file.
<li>The number of system calls for filesystem operations is reduced,
possibly resulting in a small performance increase.
<li><a href="wal.html">WAL</a> databases can be accessed in EXCLUSIVE mode without the
use of shared memory.
(<a href="wal.html#noshm">Additional information</a>)
</ol>
</p>
<p>When the locking_mode pragma specifies a particular database,
for example:</p>
<blockquote>
PRAGMA <b>main.</b>locking_mode=EXCLUSIVE;
</blockquote>
<p>then the locking mode applies only to the named database. If no
database name qualifier precedes the "locking_mode" keyword then
the locking mode is applied to all databases, including any new
databases added by subsequent <a href="lang_attach.html">ATTACH</a> commands.</p>
<p>The "temp" database (in which TEMP tables and indices are stored)
and <a href="inmemorydb.html">in-memory databases</a>
always uses exclusive locking mode. The locking mode of temp and
<a href="inmemorydb.html">in-memory databases</a> cannot
be changed. All other databases use the normal locking mode by default
and are affected by this pragma.</p>
<p>If the locking mode is EXCLUSIVE when first entering
<a href="wal.html">WAL journal mode</a>, then the locking mode cannot be changed to
NORMAL until after exiting WAL journal mode.
If the locking mode is NORMAL when first entering WAL
journal mode, then the locking mode can be changed between NORMAL and
EXCLUSIVE and back again at any time and without needing to exit
WAL journal mode.</p>
<a name="pragma_max_page_count"></a>
<h _id=pragma_max_page_count style="display:none"> PRAGMA max_page_count</h><hr>
<p><b>PRAGMA </b><i>schema.</i><b>max_page_count;
<br>PRAGMA </b><i>schema.</i><b>max_page_count = </b><i>N</i><b>;</b></p>
<p>Query or set the maximum number of pages in the database file.
Both forms of the pragma return the maximum page count. The second
form attempts to modify the maximum page count. The maximum page
count cannot be reduced below the current database size.
</p>
<a name="pragma_mmap_size"></a>
<h _id=pragma_mmap_size style="display:none"> PRAGMA mmap_size</h><hr>
<p><br><b>PRAGMA </b><i>schema.</i><b>mmap_size;
<br>PRAGMA </b><i>schema.</i><b>mmap_size=</b><i>N</i></p>
<p>Query or change the maximum number of bytes that are set
aside for memory-mapped I/O on a single database. The first form
(without an argument) queries the current limit. The second
form (with a numeric argument) sets the limit for the specified
database, or for all databases if the optional database name is
omitted. In the second form, if the database name is omitted, the
limit that is set becomes the default limit for all databases that
are added to the <a href="c3ref/sqlite3.html">database connection</a> by subsequent <a href="lang_attach.html">ATTACH</a>
statements.</p>
<p>The argument N is the maximum number of bytes of the database file
that will be accessed using memory-mapped I/O. If N is zero then
memory mapped I/O is disabled. If N is negative, then the limit
reverts to the default value determined by the most recent
<a href="c3ref/config.html">sqlite3_config</a>(<a href="c3ref/c_config_covering_index_scan.html#sqliteconfigmmapsize">SQLITE_CONFIG_MMAP_SIZE</a>), or to the compile
time default determined by <a href="compile.html#default_mmap_size">SQLITE_DEFAULT_MMAP_SIZE</a> if not
start-time limit has been set.</p>
<p>The <a href="pragma.html#pragma_mmap_size">PRAGMA mmap_size</a> statement will never increase the amount
of address space used for memory-mapped I/O above the
hard limit set by the <a href="compile.html#max_mmap_size">SQLITE_MAX_MMAP_SIZE</a> compile-time option,
nor the hard limit set at startup-time by the second argument to
sqlite3_config(<a href="c3ref/c_config_covering_index_scan.html#sqliteconfigmmapsize">SQLITE_CONFIG_MMAP_SIZE</a>)</p>
<p>The size of the memory-mapped I/O region cannot be changed while
the memory-mapped I/O region is in active use, to avoid unmapping
memory out from under running SQL statements. For this reason,
the mmap_size pragma may be a no-op if the prior mmap_size is non-zero
and there are other SQL statements running concurrently on the same
<a href="c3ref/sqlite3.html">database connection</a>.</p>
<a name="pragma_module_list"></a>
<h _id=pragma_module_list style="display:none"> PRAGMA module_list</h><hr>
<p><b>PRAGMA module_list;</b>
<p>This pragma returns a list of
<a href="vtab.html">virtual table</a> modules registered with the database connection.
<a name="pragma_optimize"></a>
<h _id=pragma_optimize style="display:none"> PRAGMA optimize</h><hr>
<p><b>PRAGMA optimize;
<br>PRAGMA optimize(</b><i>MASK</i><b>);
<br>PRAGMA </b><i>schema</i><b>.optimize;
<br>PRAGMA </b><i>schema</i><b>.optimize(</b><i>MASK</i><b>);</b></p>
<p>Attempt to optimize the database. All schemas are optimized in the
first two forms, and only the specified schema is optimized in the latter
two.</p>
<p>To achieve the best long-term query performance without the need to
do a detailed engineering analysis of the application schema and SQL,
it is recommended that applications run "PRAGMA optimize" (with no arguments)
just before closing each <a href="c3ref/sqlite3.html">database connection</a>. Long-running applications
might also benefit from setting a timer to run "PRAGMA optimize" every
few hours.
</p>
<p>This pragma is usually a no-op or nearly so and is very fast.
However if SQLite feels
that performing database optimizations (such as running <a href="lang_analyze.html">ANALYZE</a>
or creating new indexes) will improve the performance of future queries, then
some database I/O may be done. Applications that want to limit the amount
of work performed can set a timer that will invoke
<a href="c3ref/interrupt.html">sqlite3_interrupt()</a> if the pragma goes on for too long.
Or, since SQLite 3.32.0, the application can use
<a href="pragma.html#pragma_analysis_limit">PRAGMA analysis_limit=<i>N</i></a> for some small
value of <i>N</i> (a few hundred or a few thousand) to limit the depth
of analyze.
</p>
<p>The details of optimizations performed by this pragma are expected
to change and improve over time. Applications should anticipate that
this pragma will perform new optimizations in future releases.</p>
<p>The optional MASK argument is a bitmask of optimizations to perform:
<ol>
<li value='1'><p>
Debugging mode. Do not actually perform any optimizations
but instead return one line of text for each optimization
that would have been done. Off by default.
<li value='2'><p>
Run <a href="lang_analyze.html">ANALYZE</a> on tables that might benefit. On by default.
See below for additional information.
<li value='4'><p>
<em>(Not yet implemented)</em>
Record usage and performance
information from the current session in the
database file so that it will be available to "optimize"
pragmas run by future database connections.
<li value='8'><p>
<em>(Not yet implemented)</em>
Create indexes that might have been helpful to recent queries.
</ol>
<p>The default MASK is and always shall be 0xfffe. The 0xfffe mask means
perform all of the optimizations listed above except Debug Mode. If new
optimizations are added in the future that should be off by default, those
new optimizations will be given a mask of 0x10000 or larger.</p>
<p>To see all optimizations that would have been done without actually
doing them, run "PRAGMA optimize(-1)". To use only the ANALYZE
optimization, run "PRAGMA optimize(0x02)".</p>
<p><b>Determination Of When To Run Analyze</b></p>
<p> In the current implementation, a table is analyzed if and only if
all of the following are true:
<ul>
<li><p>
MASK bit 0x02 is set.
<li><p>
The query planner used <a href="fileformat2.html#stat1tab">sqlite_stat1</a>-style statistics for one or
more indexes of the table at some point during the lifetime of
the current connection.
<li><p>
One or more indexes of the table are currently unanalyzed <em>or</em>
the number of rows in the table has increased by 25 times or more
since the last time ANALYZE was run.
</ul>
<p> The rules for when tables are analyzed are likely to change in
future releases.
<a name="pragma_page_count"></a>
<h _id=pragma_page_count style="display:none"> PRAGMA page_count</h><hr>
<p><b>PRAGMA </b><i>schema.</i><b>page_count;</b></p>
<p>Return the total number of pages in the database file.</p>
<a name="pragma_page_size"></a>
<h _id=pragma_page_size style="display:none"> PRAGMA page_size</h><hr>
<p><b>PRAGMA </b><i>schema.</i><b>page_size;
<br>PRAGMA </b><i>schema.</i><b>page_size = </b><i>bytes</i><b>;</b></p>
<p>Query or set the page size of the database. The page
size must be a power of two between 512 and 65536 inclusive.
</p>
<p>When a new database is created, SQLite assigns a page size to
the database based on platform and filesystem. For many years,
the default page size was almost always 1024 bytes, but beginning
with SQLite <a href="releaselog/3_12_0.html">version 3.12.0</a> (2016-03-29),
the default page size increased to 4096.
The default page size is recommended for most applications.
<p>Specifying a new page size does not change the page size
immediately. Instead, the new page size is remembered and is used
to set the page size when the database is first created, if it does
not already exist when the page_size pragma is issued, or at the
next <a href="lang_vacuum.html">VACUUM</a> command that is run on the same database connection
while not in <a href="wal.html">WAL mode</a>.</p>
<p>The <a href="compile.html#default_page_size">SQLITE_DEFAULT_PAGE_SIZE</a> compile-time option can be used
to change the default page size assigned to new databases.
<a name="pragma_parser_trace"></a>
<h _id=pragma_parser_trace style="display:none"> PRAGMA parser_trace</h><hr>
<p><b>PRAGMA parser_trace = </b><i>boolean</i><b>; </b></p>
<p>If SQLite has been compiled with the <a href="compile.html#debug">SQLITE_DEBUG</a> compile-time
option, then the parser_trace pragma can be used to turn on tracing
for the SQL parser used internally by SQLite.
This feature is used for debugging SQLite itself.</p>
<p style='background-color: #f0e0ff;'>
This pragma is intended for use when debugging SQLite itself. It
is only available when the <a href="compile.html#debug">SQLITE_DEBUG</a> compile-time option
is used.</p>
<a name="pragma_pragma_list"></a>
<h _id=pragma_pragma_list style="display:none"> PRAGMA pragma_list</h><hr>
<p><b>PRAGMA pragma_list;</b>
<p>This pragma returns a list of PRAGMA commands
known to the database connection.
<a name="pragma_query_only"></a>
<h _id=pragma_query_only style="display:none"> PRAGMA query_only</h><hr>
<p><b>PRAGMA query_only;
<br>PRAGMA query_only = </b><i>boolean</i><b>;</b></p>
<p>The query_only pragma prevents data changes on database files when
enabled. When this pragma is enabled, any attempt to CREATE, DELETE,
DROP, INSERT, or UPDATE will result in an <a href="rescode.html#readonly">SQLITE_READONLY</a> error.
However, the database is not truly read-only. You can still run
a <a href="wal.html#ckpt">checkpoint</a> or a <a href="lang_transaction.html">COMMIT</a> and the return value of the
<a href="c3ref/db_readonly.html">sqlite3_db_readonly()</a> routine is not affected.
</p>
<a name="pragma_quick_check"></a>
<h _id=pragma_quick_check style="display:none"> PRAGMA quick_check</h><hr>
<p><b>PRAGMA </b><i>schema.</i><b>quick_check;
<br>PRAGMA </b><i>schema.</i><b>quick_check(</b><i>N</i><b>)</b>
<br>PRAGMA </b><i>schema.</i><b>quick_check(</b><i>TABLENAME</i><b>)</b></p>
<p>The pragma is like <a href="pragma.html#pragma_integrity_check">integrity_check</a> except that it does not verify
UNIQUE constraints and does not verify
that index content matches table content. By skipping UNIQUE
and index consistency checks, quick_check is able to run faster.
PRAGMA quick_check runs in O(N) time whereas <a href="pragma.html#pragma_integrity_check">PRAGMA integrity_check</a>
requires O(NlogN) time where N is the total number of rows in the
database. Otherwise the two pragmas are the same.
</p>
<a name="pragma_read_uncommitted"></a>
<h _id=pragma_read_uncommitted style="display:none"> PRAGMA read_uncommitted</h><hr>
<p><b>PRAGMA read_uncommitted;
<br>PRAGMA read_uncommitted = </b><i>boolean</i><b>;</b></p>
<p>Query, set, or clear READ UNCOMMITTED isolation. The default isolation
level for SQLite is SERIALIZABLE. Any process or thread can select
READ UNCOMMITTED isolation, but SERIALIZABLE will still be used except
between connections that share a common page and schema cache.
Cache sharing is enabled using the <a href="c3ref/enable_shared_cache.html">sqlite3_enable_shared_cache()</a> API.
Cache sharing is disabled by default.
</p>
<p>See <a href="sharedcache.html">SQLite Shared-Cache Mode</a> for additional information.</p>
<a name="pragma_recursive_triggers"></a>
<h _id=pragma_recursive_triggers style="display:none"> PRAGMA recursive_triggers</h><hr>
<p><b>PRAGMA recursive_triggers;
<br>PRAGMA recursive_triggers = </b><i>boolean</i><b>;</b></p>
<p>Query, set, or clear the recursive trigger capability.
<p>Changing the recursive_triggers setting affects the execution of
all statements prepared
using the database connection, including those prepared before the
setting was changed. Any existing statements prepared using the legacy
<a href="c3ref/prepare.html">sqlite3_prepare()</a> interface may fail with an <a href="rescode.html#schema">SQLITE_SCHEMA</a> error
after the recursive_triggers setting is changed.
<p>Prior to SQLite <a href="releaselog/3_6_18.html">version 3.6.18</a> (2009-09-11),
recursive triggers were not supported.
The behavior of SQLite was always as if this pragma was
set to OFF. Support for recursive triggers was added in version 3.6.18
but was initially turned OFF by default, for compatibility. Recursive
triggers may be turned on by default in future versions of SQLite.
</p>
<p>The depth of recursion for triggers has a hard upper limit set by
the <a href="limits.html#max_trigger_depth">SQLITE_MAX_TRIGGER_DEPTH</a> compile-time option and a run-time
limit set by <a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimittriggerdepth">SQLITE_LIMIT_TRIGGER_DEPTH</a>,...).</p>
<a name="pragma_reverse_unordered_selects"></a>
<h _id=pragma_reverse_unordered_selects style="display:none"> PRAGMA reverse_unordered_selects</h><hr>
<p><b>PRAGMA reverse_unordered_selects;
<br>PRAGMA reverse_unordered_selects = </b><i>boolean</i><b>;</b></p>
<p>When enabled, this PRAGMA causes many <a href="lang_select.html">SELECT</a> statements without
an ORDER BY clause to emit their results in the reverse order from what
they normally would. This can help debug applications that are
making invalid assumptions about the result order.
The reverse_unordered_selects pragma works for most SELECT statements,
however the query planner may sometimes choose an algorithm that is
not easily reversed, in which case the output will appear in the same
order regardless of the reverse_unordered_selects setting.
<p>SQLite makes no
guarantees about the order of results if a SELECT omits the ORDER BY
clause. Even so, the order of results does not change from one
run to the next, and so many applications mistakenly come to depend
on the arbitrary output order whatever that order happens to be. However,
sometimes new versions of SQLite will contain optimizer enhancements
that will cause the output order of queries without ORDER BY clauses
to shift. When that happens, applications that depend on a certain
output order might malfunction. By running the application multiple
times with this pragma both disabled and enabled, cases where the
application makes faulty assumptions about output order can be
identified and fixed early, reducing problems
that might be caused by linking against a different version of SQLite.
</p>
<a name="pragma_schema_version"></a>
<h _id=pragma_schema_version style="display:none"> PRAGMA schema_version</h><hr>
<p><b>PRAGMA </b><i>schema.</i><b>schema_version;
<br>PRAGMA </b><i>schema.</i><b>schema_version = </b><i>integer </i>;
<p> The schema_version pragma will get or set
the value of the schema-version integer at offset 40 in the
<a href="fileformat2.html#database_header">database header</a>.
<p> SQLite automatically increments the schema-version whenever the
schema changes. As each SQL statement runs, the schema version is
checked to ensure that the schema has not changed since the SQL
statement was <a href="c3ref/prepare.html">prepared</a>.
Subverting this mechanism by using "PRAGMA schema_version=N"
to change the value of the schema_version
may cause SQL statement to run using an obsolete schema,
which can lead to incorrect answers and/or
<a href="howtocorrupt.html#cfgerr">database corruption</a>.
It is always safe to read the schema_version, but changing the
schema_version can cause problems. For this reason, attempts
to change the value of schema_version are a silent no-op when
<a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfigdefensive">defensive mode</a> is enabled for a
database connection.
<p><span style='background-color: #ffff60;'>
<b>Warning:</b>
Misuse of this pragma can result in <a href="howtocorrupt.html#cfgerr">database corruption</a>.
</span></p>
<p> For the purposes of this pragma, the <a href="lang_vacuum.html">VACUUM</a> command is considered
a schema change, since <a href="lang_vacuum.html">VACUUM</a> will usually alter the "rootpage"
values for entries in the <a href="schematab.html">sqlite_schema table</a>.
<p> See also the <a href="pragma.html#pragma_application_id">application_id pragma</a> and <a href="pragma.html#pragma_user_version">user_version pragma</a>.
<a name="pragma_secure_delete"></a>
<h _id=pragma_secure_delete style="display:none"> PRAGMA secure_delete</h><hr>
<p><b>PRAGMA </b><i>schema.</i><b>secure_delete;
<br>PRAGMA </b><i>schema.</i><b>secure_delete = </b><i>boolean</i>|<b>FAST</b></p>
<p>Query or change the secure-delete setting. When secure_delete is
on, SQLite overwrites deleted content with zeros. The default
setting for secure_delete is determined by the <a href="compile.html#secure_delete">SQLITE_SECURE_DELETE</a>
compile-time option and is normally off. The off setting for
secure_delete improves performance by reducing the number of CPU cycles
and the amount of disk I/O. Applications that wish to avoid leaving
forensic traces after content is deleted or updated should enable the
secure_delete pragma prior to performing the delete or update, or else
run <a href="lang_vacuum.html">VACUUM</a> after the delete or update.
<p>The "fast" setting for secure_delete (added circa 2017-08-01)
is an intermediate setting in between "on" and "off".
When secure_delete is set to "fast",
SQLite will overwrite deleted content with zeros only if doing so
does not increase the amount of I/O. In other words, the "fast"
setting uses more CPU cycles but does not use more I/O.
This has the effect of purging all old content from <a href="fileformat2.html#btree">b-tree pages</a>,
but leaving forensic traces on <a href="fileformat2.html#freelist">freelist pages</a>.
<p>
When there are <a href="lang_attach.html">attached databases</a> and no database
is specified in the pragma, all databases have their secure-delete
setting altered.
The secure-delete setting for newly attached databases is the setting
of the main database at the time the ATTACH command is evaluated.
<p>
When multiple database connections share the same cache, changing
the secure-delete flag on one database connection changes it for them
all.
</p>
<p><b>Limitation:</b>
The secure_delete pragma only causes deleted content to be scrubbed
from ordinary tables. If <a href="vtab.html">virtual tables</a> store content in
<a href="vtab.html#xshadowname">shadow tables</a>, then deleting content from the virtual table does
not necessarily remove forensic traces from the shadow tables.
In particular, the <a href="fts3.html">FTS3</a> and <a href="fts5.html">FTS5</a> virtual tables that come
bundled with SQLite might leave forensic traces in their shadow tables
even if the secure_delete pragma is enabled.
</p>
<a name="pragma_short_column_names"></a>
<h _id=pragma_short_column_names style="display:none"> PRAGMA short_column_names</h><hr>
<p><b>PRAGMA short_column_names;
<br>PRAGMA short_column_names = </b><i>boolean</i><b>;</b></p>
<p>Query or change the short-column-names flag. This flag affects
the way SQLite names columns of data returned by <a href="lang_select.html">SELECT</a> statements.
See the <a href="pragma.html#pragma_full_column_names">full_column_names</a> pragma for full details.
</p>
<p style='background-color: #ffd0d0;'>
<b>This pragma is deprecated</b> and exists
for backwards compatibility only. New applications
should avoid using this pragma. Older applications should discontinue
use of this pragma at the earliest opportunity. This pragma may be omitted
from the build when SQLite is compiled using <a href="compile.html#omit_deprecated">SQLITE_OMIT_DEPRECATED</a>.
</p>
<a name="pragma_shrink_memory"></a>
<h _id=pragma_shrink_memory style="display:none"> PRAGMA shrink_memory</h><hr>
<p><b>PRAGMA shrink_memory</b></p>
<p>This pragma causes the database connection on which it is invoked
to free up as much memory as it can, by calling
<a href="c3ref/db_release_memory.html">sqlite3_db_release_memory()</a>.
</p>
<a name="pragma_soft_heap_limit"></a>
<h _id=pragma_soft_heap_limit style="display:none"> PRAGMA soft_heap_limit</h><hr>
<p><b>PRAGMA soft_heap_limit<br>
PRAGMA soft_heap_limit=</b><i>N</i></p>
<p>This pragma invokes the <a href="c3ref/hard_heap_limit64.html">sqlite3_soft_heap_limit64()</a> interface with
the argument N, if N is specified and is a non-negative integer.
The soft_heap_limit pragma always returns the same integer
that would be returned by the <a href="c3ref/hard_heap_limit64.html">sqlite3_soft_heap_limit64</a>(-1) C-language
function.
</p>
<p>See also the <a href="pragma.html#pragma_hard_heap_limit">hard_heap_limit pragma</a>.
<a name="pragma_stats"></a>
<h _id=pragma_stats style="display:none"> PRAGMA stats</h><hr>
<p><b>PRAGMA stats;</b> </p>
<p>This pragma returns auxiliary information about tables and
indices. The returned information is used during testing to help
verify that the query planner is operating correctly. The format
and meaning of this pragma will likely change from one release
to the next. Because of its volatility, the behavior and output
format of this pragma are deliberately undocumented.</p>
<p style='background-color: #f0e0ff;'>
The intended use of this pragma is only for testing and validation of
SQLite. This pragma is subject to change without notice and is not
recommended for use by application programs.</p>
<a name="pragma_synchronous"></a>
<h _id=pragma_synchronous style="display:none"> PRAGMA synchronous</h><hr>
<p><b>PRAGMA </b><i>schema.</i><b>synchronous;
<br>PRAGMA </b><i>schema.</i><b>synchronous = </b>
<i>0 | OFF | 1 | NORMAL | 2 | FULL | 3 | EXTRA</i><b>;</b></p>
<p>Query or change the setting of the "synchronous" flag.
The first (query) form will return the synchronous setting as an
integer. The second form changes the synchronous setting.
The meanings of the various synchronous settings are as follows:</p>
<dl>
<dt><b>EXTRA</b> (3)</dt>
<dd>
EXTRA synchronous is like FULL with the addition that the directory
containing a <a href="lockingv3.html#rollback">rollback journal</a> is synced after that journal is unlinked
to commit a transaction in DELETE mode. EXTRA provides additional
durability if the commit is followed closely by a power loss.</dd>
<dt><b>FULL</b> (2)</dt>
<dd>
When synchronous is FULL (2), the SQLite database engine will
use the xSync method of the <a href="vfs.html">VFS</a> to ensure that all content is safely
written to the disk surface prior to continuing.
This ensures that an operating system crash or power failure will
not corrupt the database.
FULL synchronous is very safe, but it is also slower. FULL is the
most commonly used synchronous setting when not in <a href="wal.html">WAL mode</a>.</dd>
<dt><b>NORMAL</b> (1)</dt>
<dd>
When synchronous is NORMAL (1), the SQLite database
engine will still sync at the most critical moments, but less often
than in FULL mode. There is a very small (though non-zero) chance that
a power failure at just the wrong time could corrupt the database in
<a href="pragma.html#pragma_journal_mode">journal_mode</a>=DELETE on an older filesystem.
<a href="wal.html">WAL mode</a> is safe from corruption with synchronous=NORMAL, and probably
DELETE mode is safe too on modern filesystems. WAL mode is always consistent
with synchronous=NORMAL, but WAL mode does lose durability. A transaction
committed in WAL mode with synchronous=NORMAL might roll back following
a power loss or system crash. Transactions are durable across application
crashes regardless of the synchronous setting or journal mode.
The synchronous=NORMAL setting is a good choice for most applications
running in <a href="wal.html">WAL mode</a>.</dd>
<dt><b>OFF</b> (0)</dt>
<dd>
With synchronous OFF (0), SQLite continues without syncing
as soon as it has handed data off to the operating system.
If the application running SQLite crashes, the data will be safe, but
the database <a href="howtocorrupt.html#cfgerr">might become corrupted</a> if the operating system
crashes or the computer loses power before that data has been written
to the disk surface. On the other hand, commits can be orders of
magnitude faster with synchronous OFF.
</dd></dl>
</p>
<p>In <a href="wal.html">WAL</a> mode when synchronous is NORMAL (1), the WAL file is
synchronized before each <a href="wal.html#ckpt">checkpoint</a> and the database file is
synchronized after each completed <a href="wal.html#ckpt">checkpoint</a> and the WAL file
header is synchronized when a WAL file begins to be reused after
a checkpoint, but no sync operations occur during most transactions.
With synchronous=FULL in WAL mode, an additional
sync operation of the WAL file happens after each transaction commit.
The extra WAL sync following each transaction helps ensure that
transactions are durable across a power loss. Transactions are
consistent with or without the extra syncs provided by
synchronous=FULL.
If durability is not a concern, then synchronous=NORMAL is normally
all one needs in WAL mode.</p>
<p>The TEMP schema always has synchronous=OFF since the content of
of TEMP is ephemeral and is not expected to survive a power outage.
Attempts to change the synchronous setting for TEMP are
silently ignored.
<p>See also the <a href="pragma.html#pragma_fullfsync">fullfsync</a> and <a href="pragma.html#pragma_checkpoint_fullfsync">checkpoint_fullfsync</a> pragmas.</p>
<a name="pragma_table_info"></a>
<h _id=pragma_table_info style="display:none"> PRAGMA table_info</h><hr>
<p><b>PRAGMA </b><i>schema.</i><b>table_info(</b><i>table-name</i><b>);</b></p>
<p>This pragma returns one row for each normal column
in the named table.
Columns in the result set include: "name" (its name); "type"
(data type if given, else ''); "notnull" (whether or not the column
can be NULL); "dflt_value" (the default value for the column);
and "pk" (either zero for columns that are not part of the primary key,
or the 1-based index of the column within the primary key).</p>
<p>The "cid" column should not be taken to mean more than
"rank within the current result set".</p>
<p>The table named in the table_info pragma can also be a view.</p>
<p>This pragma does not show information about <a href="gencol.html">generated columns</a> or
<a href="vtab.html#hiddencol">hidden columns</a>. Use <a href="pragma.html#pragma_table_xinfo">PRAGMA table_xinfo</a> to get a more complete list
of columns that includes generated and hidden columns.
<a name="pragma_table_list"></a>
<h _id=pragma_table_list style="display:none"> PRAGMA table_list</h><hr>
<p> <b>PRAGMA table_list;
<br>PRAGMA </b><i>schema.</i><b>table_list;
<br>PRAGMA table_list(</b><i>table-name</i><b>);</b></p>
<p>This pragma returns information about the tables and views in the schema,
one table per row of output. The table_list pragma first appeared
in SQLite version 3.37.0 (2021-11-27). As of its initial release
the columns returned by the table_list pragma include those listed below.
Future versions of SQLite will probably add additional columns of
output.
<p>
<ol>
<li> <b>schema</b>: the schema in which the table or view appears
(for example "main" or "temp").
<li> <b>name</b>: the name of the table or view.
<li> <b>type</b>: the type of object - one of "table", "view",
"shadow" (for <a href="vtab.html#xshadowname">shadow tables</a>), or "virtual" for
<a href="vtab.html">virtual tables</a>.
<li> <b>ncol</b>: the number of columns in the table, including
<a href="gencol.html">generated columns</a> and <a href="vtab.html#hiddencol">hidden columns</a>.
<li> <b>wr</b>: 1 if the table is a <a href="withoutrowid.html">WITHOUT ROWID</a> table or 0 if is not.
<li> <b>strict</b>: 1 if the table is a <a href="stricttables.html">STRICT table</a> or 0 if it is not.
<li> <i>Additional columns will likely be added in future releases.</i>
</ol>
<p>
The default behavior is to show all tables in all schemas. If the
<i>schema.</i> name appears before the pragma, then only tables in that
one schema are shown. If a <i>table-name</i> argument is supplied, then
only information about that one table is returned.
<a name="pragma_table_xinfo"></a>
<h _id=pragma_table_xinfo style="display:none"> PRAGMA table_xinfo</h><hr>
<p><b>PRAGMA </b><i>schema.</i><b>table_xinfo(</b><i>table-name</i><b>);</b></p>
<p>This pragma returns one row for each column in the named table,
including <a href="gencol.html">generated columns</a> and <a href="vtab.html#hiddencol">hidden columns</a>.
The output has the same columns as for <a href="pragma.html#pragma_table_info">PRAGMA table_info</a> plus
a column, "hidden", whose value signifies a normal column (0),
a dynamic or stored generated column (2 or 3),
or a hidden column in a virtual table (1). The rows for which
this field is non-zero are those omitted for <a href="pragma.html#pragma_table_info">PRAGMA table_info</a>.
<a name="pragma_temp_store"></a>
<h _id=pragma_temp_store style="display:none"> PRAGMA temp_store</h><hr>
<p><b>PRAGMA temp_store;
<br>PRAGMA temp_store = </b>
<i>0 | DEFAULT | 1 | FILE | 2 | MEMORY</i><b>;</b></p>
<p>Query or change the setting of the "<b>temp_store</b>" parameter.
When temp_store is DEFAULT (0), the compile-time C preprocessor macro
<a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> is used to determine where temporary tables and indices
are stored. When
temp_store is MEMORY (2) <a href="inmemorydb.html#temp_db">temporary tables</a> and indices are kept
as if they were in pure <a href="inmemorydb.html">in-memory databases</a>.
When temp_store is FILE (1) <a href="inmemorydb.html#temp_db">temporary tables</a> and indices are stored
in a file. The <a href="pragma.html#pragma_temp_store_directory">temp_store_directory</a> pragma can be used to specify
the directory containing temporary files when
<b>FILE</b> is specified. When the temp_store setting is changed,
all existing temporary tables, indices, triggers, and views are
immediately deleted.</p>
<p>It is possible for the library compile-time C preprocessor symbol
<a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> to override this pragma setting.
The following table summarizes
the interaction of the <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> preprocessor macro and the
temp_store pragma:</p>
<blockquote>
<table cellpadding="2" border="1">
<tr><th valign="bottom"><a href="compile.html#temp_store">SQLITE_TEMP_STORE</a></th>
<th valign="bottom">PRAGMA<br>temp_store</th>
<th>Storage used for<br>TEMP tables and indices</th></tr>
<tr><td align="center">0</td>
<td align="center"><em>any</em></td>
<td align="center">file</td></tr>
<tr><td align="center">1</td>
<td align="center">0</td>
<td align="center">file</td></tr>
<tr><td align="center">1</td>
<td align="center">1</td>
<td align="center">file</td></tr>
<tr><td align="center">1</td>
<td align="center">2</td>
<td align="center">memory</td></tr>
<tr><td align="center">2</td>
<td align="center">0</td>
<td align="center">memory</td></tr>
<tr><td align="center">2</td>
<td align="center">1</td>
<td align="center">file</td></tr>
<tr><td align="center">2</td>
<td align="center">2</td>
<td align="center">memory</td></tr>
<tr><td align="center">3</td>
<td align="center"><em>any</em></td>
<td align="center">memory</td></tr>
</table>
</blockquote>
<a name="pragma_temp_store_directory"></a>
<h _id=pragma_temp_store_directory style="display:none"> PRAGMA temp_store_directory</h><hr>
<p><b>PRAGMA temp_store_directory;
<br>PRAGMA temp_store_directory = '</b><i>directory-name</i><b>';</b></p>
<p>Query or change the value of the <a href="c3ref/temp_directory.html">sqlite3_temp_directory</a> global
variable, which many operating-system interface backends use to
determine where to store <a href="inmemorydb.html#temp_db">temporary tables</a> and indices.</p>
<p>When the temp_store_directory setting is changed, all existing temporary
tables, indices, triggers, and viewers in the database connection that
issued the pragma are immediately deleted. In
practice, temp_store_directory should be set immediately after the first
database connection for a process is opened. If the temp_store_directory
is changed for one database connection while other database connections
are open in the same process, then the behavior is undefined and
probably undesirable.</p>
<p>Changing the temp_store_directory setting is <u>not</u> threadsafe.
Never change the temp_store_directory setting if another thread
within the application is running any SQLite interface at the same time.
Doing so results in undefined behavior. Changing the temp_store_directory
setting writes to the <a href="c3ref/temp_directory.html">sqlite3_temp_directory</a> global
variable and that global variable is not protected by a mutex.</p>
<p>The value <i>directory-name</i> should be enclosed in single quotes.
To revert the directory to the default, set the <i>directory-name</i> to
an empty string, e.g., <i>PRAGMA temp_store_directory = ''</i>. An
error is raised if <i>directory-name</i> is not found or is not
writable. </p>
<p>The default directory for temporary files depends on the OS. Some
OS interfaces may choose to ignore this variable and place temporary
files in some other directory different from the directory specified
here. In that sense, this pragma is only advisory.</p>
<p style='background-color: #ffd0d0;'>
<b>This pragma is deprecated</b> and exists
for backwards compatibility only. New applications
should avoid using this pragma. Older applications should discontinue
use of this pragma at the earliest opportunity. This pragma may be omitted
from the build when SQLite is compiled using <a href="compile.html#omit_deprecated">SQLITE_OMIT_DEPRECATED</a>.
</p>
<a name="pragma_threads"></a>
<h _id=pragma_threads style="display:none"> PRAGMA threads</h><hr>
<p><b>PRAGMA threads;
<br>PRAGMA threads = </b><i>N</i><b>;</b></p>
<p>Query or change the value of the
<a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimitworkerthreads">SQLITE_LIMIT_WORKER_THREADS</a>,...) limit for
the current database connection. This limit sets an upper bound
on the number of auxiliary threads that a <a href="c3ref/stmt.html">prepared statement</a> is
allowed to launch to assist with a query. The default limit is 0
unless it is changed using the <a href="compile.html#default_worker_threads">SQLITE_DEFAULT_WORKER_THREADS</a>
compile-time option. When the limit is zero, that means no
auxiliary threads will be launched.</p>
<p>This pragma is a thin wrapper around the
<a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimitworkerthreads">SQLITE_LIMIT_WORKER_THREADS</a>,...) interface.
</p>
<a name="pragma_trusted_schema"></a>
<h _id=pragma_trusted_schema style="display:none"> PRAGMA trusted_schema</h><hr>
<p><b>PRAGMA trusted_schema;
<br>PRAGMA trusted_schema = </b><i>boolean</i><b>;</b></p>
<p>The trusted_schema setting is a per-connection boolean that
determines whether or not SQL functions and virtual tables that
have not been security audited are allowed to be run by views,
triggers, or in expressions of the schema such as <a href="lang_createtable.html#ckconst">CHECK constraints</a>,
<a href="lang_createtable.html#dfltval">DEFAULT clauses</a>, <a href="gencol.html">generated columns</a>, <a href="expridx.html">expression indexes</a>, and/or
<a href="partialindex.html">partial indexes</a>. This setting can also be controlled using
the <a href="c3ref/db_config.html">sqlite3_db_config</a>(db,<a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfigtrustedschema">SQLITE_DBCONFIG_TRUSTED_SCHEMA</a>,...)
C-language interface.
<p>In order to maintain backwards compatibility, this setting is
ON by default. There are advantages to turning it off, and most
applications will be unaffected if it is turned off. For that reason,
all applications are encouraged to switch this setting off on every
database connection as soon as that connection is opened.
<p>The <a href="compile.html#trusted_schema">-DSQLITE_TRUSTED_SCHEMA=0</a> compile-time option will cause
this setting to default to OFF.
<a name="pragma_user_version"></a>
<h _id=pragma_user_version style="display:none"> PRAGMA user_version</h><hr>
<p><b>PRAGMA </b><i>schema.</i><b>user_version;
<br>PRAGMA </b><i>schema.</i><b>user_version = </b><i>integer </i><b>;</b>
<p> The user_version pragma will get or set
the value of the user-version integer at offset 60 in the
<a href="fileformat2.html#database_header">database header</a>. The user-version is an integer that is
available to applications to use however they want. SQLite
makes no use of the user-version itself.
<p> See also the <a href="pragma.html#pragma_application_id">application_id pragma</a> and <a href="pragma.html#pragma_schema_version">schema_version pragma</a>.
<a name="pragma_vdbe_addoptrace"></a>
<h _id=pragma_vdbe_addoptrace style="display:none"> PRAGMA vdbe_addoptrace</h><hr>
<p><b>PRAGMA vdbe_addoptrace = </b><i>boolean</i><b>;</b></p>
<p>If SQLite has been compiled with the <a href="compile.html#debug">SQLITE_DEBUG</a> compile-time
option, then the vdbe_addoptrace pragma can be used to cause a complete
VDBE opcodes to be displayed as they are created during code generation.
This feature is used for debugging SQLite itself. See the
<a href="vdbe.html#trace">VDBE documentation</a> for more
information.</p>
<p style='background-color: #f0e0ff;'>
This pragma is intended for use when debugging SQLite itself. It
is only available when the <a href="compile.html#debug">SQLITE_DEBUG</a> compile-time option
is used.</p>
<a name="pragma_vdbe_debug"></a>
<h _id=pragma_vdbe_debug style="display:none"> PRAGMA vdbe_debug</h><hr>
<p><b>PRAGMA vdbe_debug = </b><i>boolean</i><b>;</b></p>
<p>If SQLite has been compiled with the <a href="compile.html#debug">SQLITE_DEBUG</a> compile-time
option, then the vdbe_debug pragma is a shorthand for three other
debug-only pragmas: vdbe_addoptrace, vdbe_listing, and vdbe_trace.
This feature is used for debugging SQLite itself. See the
<a href="vdbe.html#trace">VDBE documentation</a> for more
information.</p>
<p style='background-color: #f0e0ff;'>
This pragma is intended for use when debugging SQLite itself. It
is only available when the <a href="compile.html#debug">SQLITE_DEBUG</a> compile-time option
is used.</p>
<a name="pragma_vdbe_listing"></a>
<h _id=pragma_vdbe_listing style="display:none"> PRAGMA vdbe_listing</h><hr>
<p><b>PRAGMA vdbe_listing = </b><i>boolean</i><b>;</b></p>
<p>If SQLite has been compiled with the <a href="compile.html#debug">SQLITE_DEBUG</a> compile-time
option, then the vdbe_listing pragma can be used to cause a complete
listing of the virtual machine opcodes to appear on standard output
as each statement is evaluated.
With listing is on, the entire content of a program is printed
just prior to beginning execution. The statement
executes normally after the listing is printed.
This feature is used for debugging SQLite itself. See the
<a href="vdbe.html#trace">VDBE documentation</a> for more
information.</p>
<p style='background-color: #f0e0ff;'>
This pragma is intended for use when debugging SQLite itself. It
is only available when the <a href="compile.html#debug">SQLITE_DEBUG</a> compile-time option
is used.</p>
<a name="pragma_vdbe_trace"></a>
<h _id=pragma_vdbe_trace style="display:none"> PRAGMA vdbe_trace</h><hr>
<p><b>PRAGMA vdbe_trace = </b><i>boolean</i><b>;</b></p>
<p>If SQLite has been compiled with the <a href="compile.html#debug">SQLITE_DEBUG</a> compile-time
option, then the vdbe_trace pragma can be used to cause virtual machine
opcodes to be printed on standard output as they are evaluated.
This feature is used for debugging SQLite. See the
<a href="vdbe.html#trace">VDBE documentation</a> for more
information.</p>
<p style='background-color: #f0e0ff;'>
This pragma is intended for use when debugging SQLite itself. It
is only available when the <a href="compile.html#debug">SQLITE_DEBUG</a> compile-time option
is used.</p>
<a name="pragma_wal_autocheckpoint"></a>
<h _id=pragma_wal_autocheckpoint style="display:none"> PRAGMA wal_autocheckpoint</h><hr>
<p><b>PRAGMA wal_autocheckpoint;<br>
PRAGMA wal_autocheckpoint=</b><i>N</i><b>;</b></p>
<p>This pragma queries or sets the <a href="wal.html">write-ahead log</a>
<a href="wal.html#ckpt">auto-checkpoint</a> interval.
When the <a href="wal.html">write-ahead log</a> is enabled (via the
<a href="pragma.html#pragma_journal_mode">journal_mode pragma</a>) a checkpoint will be run automatically whenever
the write-ahead log equals or exceeds <i>N</i> pages in length.
Setting the auto-checkpoint size to zero or a negative value
turns auto-checkpointing off.</p>
<p>This pragma is a wrapper around the
<a href="c3ref/wal_autocheckpoint.html">sqlite3_wal_autocheckpoint()</a> C interface.
All automatic checkpoints are <a href="c3ref/wal_checkpoint_v2.html">PASSIVE</a>.</p>
<p>Autocheckpointing is enabled by default with an interval
of 1000 or <a href="compile.html#default_wal_autocheckpoint">SQLITE_DEFAULT_WAL_AUTOCHECKPOINT</a>.</p>
<a name="pragma_wal_checkpoint"></a>
<h _id=pragma_wal_checkpoint style="display:none"> PRAGMA wal_checkpoint</h><hr>
<p><b>PRAGMA </b><i>schema.</i><b>wal_checkpoint;</b><br>
<b>PRAGMA </b><i>schema.</i><b>wal_checkpoint(PASSIVE);</b><br>
<b>PRAGMA </b><i>schema.</i><b>wal_checkpoint(FULL);</b><br>
<b>PRAGMA </b><i>schema.</i><b>wal_checkpoint(RESTART);</b><br>
<b>PRAGMA </b><i>schema.</i><b>wal_checkpoint(TRUNCATE);</b>
</p>
<p>If the <a href="wal.html">write-ahead log</a> is enabled (via the <a href="pragma.html#pragma_journal_mode">journal_mode pragma</a>),
this pragma causes a <a href="wal.html#ckpt">checkpoint</a> operation to run on database
<i>database</i>, or on all attached databases if <i>database</i>
is omitted. If <a href="wal.html">write-ahead log</a> mode is disabled, this pragma is a
harmless no-op.</p>
<p>Invoking this
pragma without an argument is equivalent to calling the
<a href="c3ref/wal_checkpoint.html">sqlite3_wal_checkpoint()</a> C interface.</p>
Invoking this pragma with an argument is equivalent to calling the
<a href="c3ref/wal_checkpoint_v2.html">sqlite3_wal_checkpoint_v2()</a> C interface with a
<a href="c3ref/c_checkpoint_full.html">3rd parameter</a>
corresponding to the argument:
<dl>
<dt>PASSIVE<dd>
Checkpoint as many frames as possible without waiting for any database
readers or writers to finish. Sync the db file if all frames in the log
are checkpointed. This mode is the same as calling the
<a href="c3ref/wal_checkpoint.html">sqlite3_wal_checkpoint()</a> C interface. The
<a href="c3ref/busy_handler.html">busy-handler callback</a> is never invoked in
this mode.
<dt>FULL<dd>
This mode blocks
(invokes the <a href="c3ref/busy_handler.html">busy-handler callback</a>)
until there is no
database writer and all readers are reading from the most recent database
snapshot. It then checkpoints all frames in the log file and syncs the
database file. FULL blocks concurrent writers while it is
running, but readers can proceed.
<dt>RESTART<dd>
This mode works the same way as FULL with the addition that after
checkpointing the log file it blocks (calls the
<a href="c3ref/busy_handler.html">busy-handler callback</a>)
until all readers are finished with the log file. This ensures
that the next client to write to the database file restarts the log file
from the beginning. RESTART blocks concurrent writers while it is
running, but allowed readers to proceed.
<dt>TRUNCATE<dd>
This mode works the same way as RESTART with the
addition that the WAL file is truncated to zero bytes upon successful
completion.
</dl>
<p>The wal_checkpoint pragma returns a single row with three
integer columns. The first column is usually 0 but will be
1 if a RESTART or FULL or TRUNCATE checkpoint was blocked from completing,
for example because another thread or process was actively
using the database. In other words, the first column is 0 if the
equivalent call to <a href="c3ref/wal_checkpoint_v2.html">sqlite3_wal_checkpoint_v2()</a> would have returned
<a href="rescode.html#ok">SQLITE_OK</a> or 1 if the equivalent call would have returned <a href="rescode.html#busy">SQLITE_BUSY</a>.
The second column is the number of modified pages that have been
written to the write-ahead log file.
The third column is the number of pages in the write-ahead log file
that have been successfully moved back into the database file at
the conclusion of the checkpoint.
The second and third column are -1 if there is no
write-ahead log, for example if this pragma is invoked on a database
connection that is not in <a href="wal.html">WAL mode</a>.</p>
<a name="pragma_writable_schema"></a>
<h _id=pragma_writable_schema style="display:none"> PRAGMA writable_schema</h><hr>
<p><b>PRAGMA writable_schema = </b><i>boolean</i><b>;</b><br>
<b>PRAGMA writable_schema = RESET</b></p>
<p>When this pragma is on, and the <a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfigdefensive">SQLITE_DBCONFIG_DEFENSIVE</a> flag
is off, then the <a href="schematab.html">sqlite_schema</a> table
can be changed using ordinary <a href="lang_update.html">UPDATE</a>, <a href="lang_insert.html">INSERT</a>, and <a href="lang_delete.html">DELETE</a>
statements. If the argument is "RESET" then schema writing is
disabled (as with "PRAGMA writable_schema=OFF") and, in addition, the
schema is reloaded. <span style='background-color: #ffff60;'><b>Warning:</b>
misuse of this pragma can easily result in
a <a href="howtocorrupt.html#cfgerr">corrupt database file</a>.</span>
<hr>
|
