| name | sys |
| description | MSSQL system catalog views technical specification for implementing sys.* emulation in mssqlite. Covers schema definitions, column types, relationships, built-in data, and SQLite backing-table design for all essential catalog views. |
MSSQL System Catalog Views - Implementation Specification
This skill provides the complete technical specification needed to implement system catalog view emulation in the mssqlite TDS server. For each view: exact column definitions, data types, relationships to other views, seed data, and the SQLite backing-table DDL.
Source: sql-docs/docs/relational-databases/system-catalog-views/
Implementation status — @mssqlite/catalog
This spec is implemented in packages/catalog:
- Phase 1 + 2 + the high-value phase 3 surface are done — schemas, types (34
seed rows), objects, columns, tables/views/procedures views, databases,
indexes, index_columns, key_constraints, foreign_keys +
foreign_key_columns, check/default constraints, database/server
principals, identity_columns (+
_extra backing table), plus
INFORMATION_SCHEMA.TABLES, .COLUMNS, .ROUTINES, .VIEWS,
.TABLE_CONSTRAINTS, .KEY_COLUMN_USAGE, and
.REFERENTIAL_CONSTRAINTS views.
- Query interception is unnecessary — instead of routing
sys.*
queries (§ Architecture Overview above), the transpiler flattens
sys.tables to the literal SQLite object name "sys.tables", which is
exactly what the catalog creates. Plain SQLite execution then serves
catalog queries, including joins with user tables.
- DDL maintenance hooks (§18) are called by the engine as DDL executes;
drop cleanup removes child constraint objects as specified.
- Object ids allocate from 100000001 via
sys._next_id (§19).
- System functions (§17): OBJECT_ID/OBJECT_NAME/SCHEMA_ID/SCHEMA_NAME/
TYPE_ID/TYPE_NAME/DB_ID/DB_NAME rewrite to catalog subqueries at
transpile time; SERVERPROPERTY/@@VERSION/@@SPID come from engine UDFs
and globals.
sys.sql_modules stores view, stored-procedure, user-function, and DML-trigger
definitions (whole batch source); the engine reloads procedures, scalar
(FN) and inline table-valued (IF) functions, and table-parented TR
triggers from it on server start, and
OBJECT_DEFINITION() rewrites to a subquery over it. It includes the SQL
Server 2019 is_inlineable and inline_type flags.sys.sequences joins SO / SEQUENCE_OBJECT rows to the internal
sys.sequence_state backing table and exposes type ids, precision/scale,
start/increment/bounds, cycle/cache settings, current/exhausted state, and
last-used value. Numeric state is stored losslessly as decimal text and cast
by the view for ordinary catalog queries.- Rowversion/TIMESTAMP columns use system/user type id 189, max_length 8,
identity/computed flags 0, and default to non-null catalog metadata unless
explicitly declared NULL. The database-wide last allocated value is stored
losslessly as decimal text in the singleton internal
sys.rowversion_state
table; it is surfaced by @@DBTS, not as a public catalog view. Result
metadata maps non-null rows to BIGBINARY(8) and nullable rows to
BIGVARBINARY(8).
- Opaque CLR built-ins are seeded as assembly types: hierarchyid
(user_type_id 128, system_type_id 240, max_length 892), geometry (129, 240, -1), and geography (130, 240, -1). Their columns retain distinct
user type ids while result metadata uses native UDT_INFO. sql_variant 98
and untyped XML 241 likewise retain their declared catalog and wire types.
sys.computed_columns joins computed sys.columns rows to
sys.computed_columns_extra, exposing normalized definition text,
uses_database_collation, and is_persisted. CREATE/ALTER/DROP maintain
both stores; the catalog's inferred type fields drive result metadata.- Common catalog procedures are implemented directly over these stores:
sp_help, sp_helptext, sp_columns, sp_tables, sp_who, sp_helpdb,
and sp_spaceused return SQL Server/ODBC-shaped typed result sets through
both EXEC and RPC. sp_rename keeps supported object, column, and user-index
names synchronized with SQLite under a savepoint; module source is retained
verbatim, matching SQL Server's warning that stored definitions and dependent
references are not rewritten.
sys.default_constraints is a derived view that inherits all
sys.objects columns and joins parent_column_id, expression definition,
and is_system_named from sys.default_constraints_extra.sys.dm_exec_sessions and sys.dm_exec_requests are mutable backing tables:
session creation/login synchronizes identity, an outer engine batch inserts a
request before parsing so it can observe itself, completion returns the
session to sleeping, and socket close deletes both rows.- Not yet populated: extended properties.
- The live differential audit still finds result-descriptor gaps even where
catalog values are correct: selected sysname fields can surface as
nvarchar(max), and
sys.columns.max_length can surface as nullable int rather
than non-null smallint. The open acceptance brief is
todo/catalog-result-metadata.md.
Implemented high-value view contracts
All catalog-name columns read the singleton current-database context; schema
and object names join through sys.schemas/sys.objects. Reserved ISO fields
are present as NULL rather than omitted.
| View | Complete exposed schema |
|---|
INFORMATION_SCHEMA.VIEWS | TABLE_CATALOG/SCHEMA/NAME nvarchar(128), VIEW_DEFINITION nvarchar(4000) NULL, CHECK_OPTION varchar(7), IS_UPDATABLE varchar(2) |
INFORMATION_SCHEMA.TABLE_CONSTRAINTS | CONSTRAINT_CATALOG/SCHEMA/NAME, TABLE_CATALOG/SCHEMA/NAME as nvarchar(128), CONSTRAINT_TYPE varchar(11), IS_DEFERRABLE varchar(2), INITIALLY_DEFERRED varchar(2) |
INFORMATION_SCHEMA.KEY_COLUMN_USAGE | catalog/schema/name triples for constraint and table plus COLUMN_NAME nvarchar(128) and ORDINAL_POSITION int; PK/UQ rows join key indexes and FK rows join sys.foreign_key_columns |
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS | constraint and referenced-unique catalog/schema/name triples as nvarchar(128), MATCH_OPTION varchar(7), UPDATE_RULE/DELETE_RULE varchar(11); the referenced name resolves through key_index_id |
INFORMATION_SCHEMA.ROUTINES | all SQL Server columns: SPECIFIC_*, ROUTINE_*, MODULE_*, UDT_*, DATA_TYPE, character/collation/character-set fields, numeric precision/radix/scale, datetime/interval fields, TYPE_UDT_*, SCOPE_*, MAXIMUM_CARDINALITY, DTD_IDENTIFIER, body/definition/external fields, determinism/data-access/null-call fields, SQL path/schema-level/dynamic-result/cast/invocation fields, and CREATED/LAST_ALTERED; scalar return details come from sys.routine_metadata, IF returns table, and procedure return fields are NULL |
sys.sql_modules has object_id int, definition nvarchar(max) NULL, eight
SQL Server option columns (uses_ansi_nulls, uses_quoted_identifier,
is_schema_bound, uses_database_collation, is_recompiled,
null_on_null_input, execute_as_principal_id NULL,
uses_native_compilation) plus is_inlineable bit and inline_type bit.
The minimal live DMV schemas are intentionally the commonly consumed subset:
| View | Columns |
|---|
sys.dm_exec_sessions | session_id smallint, login_time datetime, nullable host_name/program_name nvarchar(128), nullable host_process_id/client_version int, nullable client_interface_name nvarchar(32), login_name nvarchar(128), status nvarchar(30), database_id smallint, open_transaction_count int, nullable request start/end datetimes, row_count bigint, prev_error int, original_login_name nvarchar(128) |
sys.dm_exec_requests | session_id smallint, request_id int, start_time datetime, status nvarchar(30), command nvarchar(32), database_id smallint, user_id int, blocking_session_id smallint, nullable wait_type nvarchar(60), wait_time int, wait_resource nvarchar(256), open_transaction_count int, percent_complete real, cpu_time/total_elapsed_time int, reads/writes/logical_reads/row_count bigint |
Architecture Overview
MSSQL exposes database metadata through a hierarchy of catalog views in the sys schema. These are read-only views backed by internal system tables. For mssqlite emulation, we implement them as real SQLite tables that are populated/maintained as DDL executes, plus SQLite views for derived catalog views.
View Hierarchy
sys.objects (base: all schema-scoped objects)
├── sys.tables (type = 'U')
├── sys.views (type = 'V')
├── sys.procedures (type = 'P')
├── DML triggers (type = 'TR', parent_object_id = target table)
└── sys.foreign_keys (type = 'F')
└── sys.foreign_key_columns
sys.columns (all columns of all objects)
├── sys.identity_columns (is_identity = 1)
└── sys.computed_columns (is_computed = 1)
sys.types (system + user-defined types)
sys.schemas (database schemas)
sys.databases (server-level: all databases)
sys.indexes (indexes and heaps)
sys.index_columns (columns in indexes)
sys.key_constraints (PK/UQ constraints)
sys.check_constraints (CHECK constraints)
sys.default_constraints (DEFAULT constraints)
sys.database_principals (database-level security principals)
sys.server_principals (server-level security principals)
Query Interception Strategy
When a client sends a query referencing sys.* views, the server must:
- Parse the SQL to detect
sys. prefixed table references
- Route the query to the catalog backing tables instead of SQLite user tables
- Return results with correct TDS column metadata matching MSSQL types
1. sys.schemas
Smallest/simplest view. Must be populated first as other views reference schema_id.
Column Definition
| Column | MSSQL Type | SQLite Type | Description |
|---|
| name | sysname (nvarchar(128)) | TEXT NOT NULL | Schema name, unique per database |
| schema_id | int | INTEGER PRIMARY KEY | Schema ID, unique per database |
| principal_id | int | INTEGER NOT NULL | ID of the principal that owns this schema |
SQLite Backing Table
CREATE TABLE IF NOT EXISTS [sys.schemas] (
schema_id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
principal_id INTEGER NOT NULL DEFAULT 1
);
Seed Data (built-in schemas)
| schema_id | name | principal_id |
|---|
| 1 | dbo | 1 |
| 2 | guest | 2 |
| 3 | INFORMATION_SCHEMA | 3 |
| 4 | sys | 4 |
2. sys.types
All system data types. Must be populated before sys.columns.
Column Definition
| Column | MSSQL Type | SQLite Type | Description |
|---|
| name | sysname | TEXT NOT NULL | Type name, unique within schema |
| system_type_id | tinyint | INTEGER NOT NULL | Internal system type ID |
| user_type_id | int | INTEGER PRIMARY KEY | Type ID, unique per database |
| schema_id | int | INTEGER NOT NULL | Schema (always 4=sys for system types) |
| principal_id | int | INTEGER | Individual owner if different from schema owner; NULL for system types |
| max_length | smallint | INTEGER NOT NULL | Max length in bytes (-1 = max types) |
| precision | tinyint | INTEGER NOT NULL DEFAULT 0 | Max precision if numeric, else 0 |
| scale | tinyint | INTEGER NOT NULL DEFAULT 0 | Max scale if numeric, else 0 |
| collation_name | sysname | TEXT | Collation name for character types, NULL otherwise |
| is_nullable | bit | INTEGER NOT NULL DEFAULT 1 | Whether type is nullable |
| is_user_defined | bit | INTEGER NOT NULL DEFAULT 0 | 1=user-defined, 0=system type |
| is_assembly_type | bit | INTEGER NOT NULL DEFAULT 0 | 1=CLR assembly type |
| default_object_id | int | INTEGER NOT NULL DEFAULT 0 | Bound default object ID |
| rule_object_id | int | INTEGER NOT NULL DEFAULT 0 | Bound rule object ID |
| is_table_type | bit | INTEGER NOT NULL DEFAULT 0 | 1=table type |
SQLite Backing Table
CREATE TABLE IF NOT EXISTS [sys.types] (
user_type_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
system_type_id INTEGER NOT NULL,
schema_id INTEGER NOT NULL DEFAULT 4,
principal_id INTEGER,
max_length INTEGER NOT NULL,
precision INTEGER NOT NULL DEFAULT 0,
scale INTEGER NOT NULL DEFAULT 0,
collation_name TEXT,
is_nullable INTEGER NOT NULL DEFAULT 1,
is_user_defined INTEGER NOT NULL DEFAULT 0,
is_assembly_type INTEGER NOT NULL DEFAULT 0,
default_object_id INTEGER NOT NULL DEFAULT 0,
rule_object_id INTEGER NOT NULL DEFAULT 0,
is_table_type INTEGER NOT NULL DEFAULT 0
);
Seed Data (all built-in system types)
system_type_id is the key identifier used by sys.columns. user_type_id equals system_type_id for built-in types.
| user_type_id | name | system_type_id | max_length | precision | scale | collation_name |
|---|
| 34 | image | 34 | 16 | 0 | 0 | NULL |
| 35 | text | 35 | 16 | 0 | 0 | SQL_Latin1_General_CP1_CI_AS |
| 36 | uniqueidentifier | 36 | 16 | 0 | 0 | NULL |
| 40 | date | 40 | 3 | 10 | 0 | NULL |
| 41 | time | 41 | 5 | 16 | 7 | NULL |
| 42 | datetime2 | 42 | 8 | 27 | 7 | NULL |
| 43 | datetimeoffset | 43 | 10 | 34 | 7 | NULL |
| 48 | tinyint | 48 | 1 | 3 | 0 | NULL |
| 52 | smallint | 52 | 2 | 5 | 0 | NULL |
| 56 | int | 56 | 4 | 10 | 0 | NULL |
| 58 | smalldatetime | 58 | 4 | 16 | 0 | NULL |
| 59 | real | 59 | 4 | 24 | 0 | NULL |
| 60 | money | 60 | 8 | 19 | 4 | NULL |
| 61 | datetime | 61 | 8 | 23 | 3 | NULL |
| 62 | float | 62 | 8 | 53 | 0 | NULL |
| 98 | sql_variant | 98 | 8016 | 0 | 0 | NULL |
| 99 | ntext | 99 | 16 | 0 |
Note: sysname is an alias for nvarchar(128) — user_type_id=256, system_type_id=231 (nvarchar). It is the only built-in user-defined type (is_user_defined=0 but user_type_id != system_type_id).
3. sys.objects
Base catalog view for all schema-scoped objects. sys.tables, sys.views, sys.procedures, sys.foreign_keys, sys.key_constraints, sys.check_constraints, and sys.default_constraints are all filtered views of sys.objects.
Column Definition
| Column | MSSQL Type | SQLite Type | Description |
|---|
| name | sysname | TEXT NOT NULL | Object name |
| object_id | int | INTEGER PRIMARY KEY | Object ID, unique per database |
| principal_id | int | INTEGER | Individual owner if different from schema owner |
| schema_id | int | INTEGER NOT NULL | Schema containing this object |
| parent_object_id | int | INTEGER NOT NULL DEFAULT 0 | Parent object ID (0=no parent) |
| type | char(2) | TEXT NOT NULL | Object type code (see table below) |
| type_desc | nvarchar(60) | TEXT NOT NULL | Object type description |
| create_date | datetime | TEXT NOT NULL | Creation date (ISO 8601) |
| modify_date | datetime | TEXT NOT NULL | Last modification date |
| is_ms_shipped | bit | INTEGER NOT NULL DEFAULT 0 | 1=system object |
| is_published | bit | INTEGER NOT NULL DEFAULT 0 | Published for replication |
| is_schema_published | bit | INTEGER NOT NULL DEFAULT 0 | Schema published |
Object Type Codes
| type | type_desc | Description |
|---|
| U | USER_TABLE | User table |
| V | VIEW | View |
| P | SQL_STORED_PROCEDURE | Stored procedure |
| FN | SQL_SCALAR_FUNCTION | Scalar function |
| IF | SQL_INLINE_TABLE_VALUED_FUNCTION | Inline TVF |
| TF | SQL_TABLE_VALUED_FUNCTION | Table-valued function |
| S | SYSTEM_TABLE | System base table |
| IT | INTERNAL_TABLE | Internal table |
| PK | PRIMARY_KEY_CONSTRAINT | Primary key constraint |
| UQ | UNIQUE_CONSTRAINT | Unique constraint |
| F | FOREIGN_KEY_CONSTRAINT | Foreign key constraint |
| C | CHECK_CONSTRAINT | Check constraint |
| D | DEFAULT_CONSTRAINT | Default constraint |
| TR | SQL_TRIGGER | DML trigger |
| SQ | SERVICE_QUEUE | Service queue |
| SN | SYNONYM | Synonym |
| SO | SEQUENCE_OBJECT | Sequence |
SQLite Backing Table
CREATE TABLE IF NOT EXISTS [sys.objects] (
object_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
principal_id INTEGER,
schema_id INTEGER NOT NULL DEFAULT 1,
parent_object_id INTEGER NOT NULL DEFAULT 0,
type TEXT NOT NULL,
type_desc TEXT NOT NULL,
create_date TEXT NOT NULL DEFAULT (datetime('now')),
modify_date TEXT NOT NULL DEFAULT (datetime('now')),
is_ms_shipped INTEGER NOT NULL DEFAULT 0,
is_published INTEGER NOT NULL DEFAULT 0,
is_schema_published INTEGER NOT NULL DEFAULT 0
);
Derived Views (SQLite views over sys.objects)
-- sys.tables: objects WHERE type = 'U'
CREATE VIEW IF NOT EXISTS [sys.tables] AS
SELECT o.*,
0 AS lob_data_space_id,
0 AS filestream_data_space_id,
0 AS max_column_id_used,
0 AS lock_on_bulk_load,
1 AS uses_ansi_nulls,
0 AS is_replicated,
0 AS has_replication_filter,
0 AS is_merge_published,
0 AS is_sync_tran_subscribed,
0 AS has_unchecked_assembly_data,
0 AS text_in_row_limit,
0 AS large_value_types_out_of_row,
0 AS is_tracked_by_cdc,
0 AS lock_escalation,
'TABLE' AS lock_escalation_desc,
0 AS is_filetable,
0 AS is_memory_optimized,
0 AS durability,
'SCHEMA_AND_DATA' AS durability_desc,
0 AS temporal_type,
'NON_TEMPORAL_TABLE' AS temporal_type_desc,
NULL AS history_table_id,
0 AS is_remote_data_archive_enabled,
0 AS is_external,
0 AS is_node,
0 AS is_edge
FROM [sys.objects] o WHERE o.type = 'U';
-- sys.views: objects WHERE type = 'V'
CREATE VIEW IF NOT EXISTS [sys.views] AS
SELECT o.*,
0 AS is_date_correlation_view,
0 AS is_tracked_by_cdc,
0 AS has_snapshot_definition,
0 AS has_opaque_metadata,
0 AS has_unchecked_assembly_data,
0 AS with_check_option,
0 AS is_replicated
FROM [sys.objects] o WHERE o.type = 'V';
4. sys.columns
One row per column for every object that has columns (tables, views, TVFs).
Column Definition
| Column | MSSQL Type | SQLite Type | Description |
|---|
| object_id | int | INTEGER NOT NULL | ID of the owning object |
| name | sysname | TEXT NOT NULL | Column name, unique within object |
| column_id | int | INTEGER NOT NULL | Column ordinal (1-based, may have gaps) |
| system_type_id | tinyint | INTEGER NOT NULL | System type ID (→ sys.types) |
| user_type_id | int | INTEGER NOT NULL | User type ID (→ sys.types) |
| max_length | smallint | INTEGER NOT NULL | Max length in bytes (-1 for max types) |
| precision | tinyint | INTEGER NOT NULL DEFAULT 0 | Precision if numeric |
| scale | tinyint | INTEGER NOT NULL DEFAULT 0 | Scale if numeric |
| collation_name | sysname | TEXT | Collation for character types |
| is_nullable | bit | INTEGER NOT NULL DEFAULT 1 | 1=nullable |
| is_ansi_padded | bit | INTEGER NOT NULL DEFAULT 0 | ANSI padding behavior |
| is_rowguidcol | bit | INTEGER NOT NULL DEFAULT 0 | ROWGUIDCOL |
| is_identity | bit | INTEGER NOT NULL DEFAULT 0 | Identity column |
| is_computed | bit | INTEGER NOT NULL DEFAULT 0 | Computed column |
| is_filestream | bit | INTEGER NOT NULL DEFAULT 0 | FILESTREAM column |
| is_replicated | bit | INTEGER NOT NULL DEFAULT 0 | Replicated |
| is_non_sql_subscribed | bit | INTEGER NOT NULL DEFAULT 0 | Non-SQL subscriber |
| is_merge_published | bit | INTEGER NOT NULL DEFAULT 0 | Merge published |
| is_dts_replicated | bit | INTEGER NOT NULL DEFAULT 0 | SSIS replicated |
| is_xml_document |
SQLite Backing Table
CREATE TABLE IF NOT EXISTS [sys.columns] (
object_id INTEGER NOT NULL,
name TEXT NOT NULL,
column_id INTEGER NOT NULL,
system_type_id INTEGER NOT NULL,
user_type_id INTEGER NOT NULL,
max_length INTEGER NOT NULL,
precision INTEGER NOT NULL DEFAULT 0,
scale INTEGER NOT NULL DEFAULT 0,
collation_name TEXT,
is_nullable INTEGER NOT NULL DEFAULT 1,
is_ansi_padded INTEGER NOT NULL DEFAULT 0,
is_rowguidcol INTEGER NOT NULL DEFAULT 0,
is_identity INTEGER NOT NULL DEFAULT 0,
is_computed INTEGER NOT NULL DEFAULT 0,
is_filestream INTEGER NOT NULL DEFAULT 0,
is_replicated INTEGER NOT NULL DEFAULT 0,
is_non_sql_subscribed INTEGER NOT NULL DEFAULT 0,
is_merge_published INTEGER NOT NULL DEFAULT 0,
is_dts_replicated INTEGER NOT NULL DEFAULT 0,
is_xml_document INTEGER NOT NULL DEFAULT 0,
xml_collection_id INTEGER NOT NULL DEFAULT 0,
default_object_id INTEGER NOT NULL DEFAULT 0,
rule_object_id INTEGER NOT NULL DEFAULT 0,
is_sparse INTEGER NOT NULL DEFAULT 0,
is_column_set INTEGER NOT NULL DEFAULT 0,
generated_always_type INTEGER NOT NULL DEFAULT 0,
generated_always_type_desc TEXT NOT NULL DEFAULT 'NOT_APPLICABLE',
is_hidden INTEGER NOT NULL DEFAULT 0,
is_masked INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (object_id, column_id)
);
Column Type Mapping: SQL Type → sys.columns Fields
When a user creates a table, each column's SQL type declaration maps to sys.columns fields:
| SQL Declaration | system_type_id | max_length | precision | scale |
|---|
| tinyint | 48 | 1 | 3 | 0 |
| smallint | 52 | 2 | 5 | 0 |
| int | 56 | 4 | 10 | 0 |
| bigint | 127 | 8 | 19 | 0 |
| bit | 104 | 1 | 1 | 0 |
| decimal(p,s) | 106 | 5/9/13/17† | p | s |
| numeric(p,s) | 108 | 5/9/13/17† | p | s |
| money | 60 | 8 | 19 | 4 |
| smallmoney | 122 | 4 | 10 | 4 |
| float | 62 | 8 | 53 | 0 |
| real | 59 | 4 | 24 | 0 |
| date | 40 | 3 | 10 | 0 |
| time(n) | 41 | 3/4/5‡ | 8-16 | n |
| datetime | 61 | 8 | 23 | 3 |
| smalldatetime | 58 | 4 | 16 | 0 |
| datetime2(n) | 42 | 6/7/8‡ | 19-27 | n |
| datetimeoffset(n) | 43 | 8/9/10‡ | 26-34 | n |
| char(n) | 175 | n | 0 | 0 |
| varchar(n) | 167 | n | 0 | 0 |
| varchar(max) | 167 | -1 | 0 | 0 |
| nchar(n) | 239 | 2*n | 0 | 0 |
| nvarchar(n) | 231 | 2*n | 0 | 0 |
| nvarchar(max) | 231 |
† decimal/numeric max_length: 5 (p 1-9), 9 (p 10-19), 13 (p 20-28), 17 (p 29-38)
‡ time max_length: 3 (n 0-2), 4 (n 3-4), 5 (n 5-7); datetime2: 6 (n<3), 7 (n 3-4), 8 (n 5-7); datetimeoffset: 8 (n 0-2), 9 (n 3-4), 10 (n 5-7)
5. sys.indexes
One row per index or heap per tabular object.
Column Definition
| Column | MSSQL Type | SQLite Type | Description |
|---|
| object_id | int | INTEGER NOT NULL | Owning object ID |
| name | sysname | TEXT | Index name (NULL for heap) |
| index_id | int | INTEGER NOT NULL | 0=heap, 1=clustered, >1=nonclustered |
| type | tinyint | INTEGER NOT NULL | 0=Heap, 1=Clustered, 2=Nonclustered |
| type_desc | nvarchar(60) | TEXT NOT NULL | HEAP/CLUSTERED/NONCLUSTERED |
| is_unique | bit | INTEGER NOT NULL DEFAULT 0 | 1=unique |
| data_space_id | int | INTEGER NOT NULL DEFAULT 1 | Filegroup ID |
| ignore_dup_key | bit | INTEGER NOT NULL DEFAULT 0 | IGNORE_DUP_KEY setting |
| is_primary_key | bit | INTEGER NOT NULL DEFAULT 0 | Part of PK constraint |
| is_unique_constraint | bit | INTEGER NOT NULL DEFAULT 0 | Part of UNIQUE constraint |
| fill_factor | tinyint | INTEGER NOT NULL DEFAULT 0 | Fill factor percentage |
| is_padded | bit | INTEGER NOT NULL DEFAULT 0 | PADINDEX setting |
| is_disabled | bit | INTEGER NOT NULL DEFAULT 0 | Index disabled |
| is_hypothetical | bit | INTEGER NOT NULL DEFAULT 0 | Hypothetical index |
| allow_row_locks | bit | INTEGER NOT NULL DEFAULT 1 | Row locks allowed |
| allow_page_locks | bit | INTEGER NOT NULL DEFAULT 1 | Page locks allowed |
| has_filter | bit | INTEGER NOT NULL DEFAULT 0 | Filtered index |
| filter_definition | nvarchar(max) | TEXT | Filter expression |
SQLite Backing Table
CREATE TABLE IF NOT EXISTS [sys.indexes] (
object_id INTEGER NOT NULL,
name TEXT,
index_id INTEGER NOT NULL,
type INTEGER NOT NULL DEFAULT 0,
type_desc TEXT NOT NULL DEFAULT 'HEAP',
is_unique INTEGER NOT NULL DEFAULT 0,
data_space_id INTEGER NOT NULL DEFAULT 1,
ignore_dup_key INTEGER NOT NULL DEFAULT 0,
is_primary_key INTEGER NOT NULL DEFAULT 0,
is_unique_constraint INTEGER NOT NULL DEFAULT 0,
fill_factor INTEGER NOT NULL DEFAULT 0,
is_padded INTEGER NOT NULL DEFAULT 0,
is_disabled INTEGER NOT NULL DEFAULT 0,
is_hypothetical INTEGER NOT NULL DEFAULT 0,
allow_row_locks INTEGER NOT NULL DEFAULT 1,
allow_page_locks INTEGER NOT NULL DEFAULT 1,
has_filter INTEGER NOT NULL DEFAULT 0,
filter_definition TEXT,
PRIMARY KEY (object_id, index_id)
);
6. sys.index_columns
One row per column that is part of an index.
Column Definition
| Column | MSSQL Type | SQLite Type | Description |
|---|
| object_id | int | INTEGER NOT NULL | Object containing the index |
| index_id | int | INTEGER NOT NULL | Index ID |
| index_column_id | int | INTEGER NOT NULL | Column ordinal within index (1-based) |
| column_id | int | INTEGER NOT NULL | Column ID in the object (0=RID in nonclustered) |
| key_ordinal | tinyint | INTEGER NOT NULL DEFAULT 0 | Ordinal in key columns (0=not a key) |
| partition_ordinal | tinyint | INTEGER NOT NULL DEFAULT 0 | Ordinal in partitioning columns |
| is_descending_key | bit | INTEGER NOT NULL DEFAULT 0 | 1=descending sort |
| is_included_column | bit | INTEGER NOT NULL DEFAULT 0 | 1=included (non-key) column |
SQLite Backing Table
CREATE TABLE IF NOT EXISTS [sys.index_columns] (
object_id INTEGER NOT NULL,
index_id INTEGER NOT NULL,
index_column_id INTEGER NOT NULL,
column_id INTEGER NOT NULL,
key_ordinal INTEGER NOT NULL DEFAULT 0,
partition_ordinal INTEGER NOT NULL DEFAULT 0,
is_descending_key INTEGER NOT NULL DEFAULT 0,
is_included_column INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (object_id, index_id, index_column_id)
);
7. sys.foreign_keys
Inherits from sys.objects (type='F') with additional FK-specific columns.
Additional Columns (beyond sys.objects)
| Column | MSSQL Type | SQLite Type | Description |
|---|
| referenced_object_id | int | INTEGER NOT NULL | Referenced (parent) table object_id |
| key_index_id | int | INTEGER NOT NULL | Index ID in the referenced table |
| is_disabled | bit | INTEGER NOT NULL DEFAULT 0 | FK disabled |
| is_not_for_replication | bit | INTEGER NOT NULL DEFAULT 0 | NOT FOR REPLICATION |
| is_not_trusted | bit | INTEGER NOT NULL DEFAULT 0 | Not verified by system |
| delete_referential_action | tinyint | INTEGER NOT NULL DEFAULT 0 | 0=NO_ACTION, 1=CASCADE, 2=SET_NULL, 3=SET_DEFAULT |
| delete_referential_action_desc | nvarchar(60) | TEXT NOT NULL DEFAULT 'NO_ACTION' | Description |
| update_referential_action | tinyint | INTEGER NOT NULL DEFAULT 0 | 0=NO_ACTION, 1=CASCADE, 2=SET_NULL, 3=SET_DEFAULT |
| update_referential_action_desc | nvarchar(60) | TEXT NOT NULL DEFAULT 'NO_ACTION' | Description |
| is_system_named | bit | INTEGER NOT NULL DEFAULT 0 | 1=system-generated name |
SQLite Backing Table
CREATE TABLE IF NOT EXISTS [sys.foreign_keys] (
object_id INTEGER PRIMARY KEY, -- same as sys.objects.object_id
referenced_object_id INTEGER NOT NULL,
key_index_id INTEGER NOT NULL DEFAULT 0,
is_disabled INTEGER NOT NULL DEFAULT 0,
is_not_for_replication INTEGER NOT NULL DEFAULT 0,
is_not_trusted INTEGER NOT NULL DEFAULT 0,
delete_referential_action INTEGER NOT NULL DEFAULT 0,
delete_referential_action_desc TEXT NOT NULL DEFAULT 'NO_ACTION',
update_referential_action INTEGER NOT NULL DEFAULT 0,
update_referential_action_desc TEXT NOT NULL DEFAULT 'NO_ACTION',
is_system_named INTEGER NOT NULL DEFAULT 0
);
8. sys.foreign_key_columns
One row per column mapping in a foreign key constraint.
Column Definition
| Column | MSSQL Type | SQLite Type | Description |
|---|
| constraint_object_id | int | INTEGER NOT NULL | FK constraint object_id |
| constraint_column_id | int | INTEGER NOT NULL | Column ordinal in the FK (1-based) |
| parent_object_id | int | INTEGER NOT NULL | Referencing table object_id |
| parent_column_id | int | INTEGER NOT NULL | Referencing column ID |
| referenced_object_id | int | INTEGER NOT NULL | Referenced table object_id |
| referenced_column_id | int | INTEGER NOT NULL | Referenced column ID |
SQLite Backing Table
CREATE TABLE IF NOT EXISTS [sys.foreign_key_columns] (
constraint_object_id INTEGER NOT NULL,
constraint_column_id INTEGER NOT NULL,
parent_object_id INTEGER NOT NULL,
parent_column_id INTEGER NOT NULL,
referenced_object_id INTEGER NOT NULL,
referenced_column_id INTEGER NOT NULL,
PRIMARY KEY (constraint_object_id, constraint_column_id)
);
9. sys.key_constraints
Inherits from sys.objects (type='PK' or 'UQ'). One row per PRIMARY KEY or UNIQUE constraint.
Additional Columns
| Column | MSSQL Type | SQLite Type | Description |
|---|
| unique_index_id | int | INTEGER NOT NULL | Index ID for the backing unique index |
| is_system_named | bit | INTEGER NOT NULL DEFAULT 0 | 1=system-generated name |
SQLite Backing Table
CREATE TABLE IF NOT EXISTS [sys.key_constraints] (
object_id INTEGER PRIMARY KEY, -- same as sys.objects.object_id
unique_index_id INTEGER NOT NULL,
is_system_named INTEGER NOT NULL DEFAULT 0
);
10. sys.check_constraints
Inherits from sys.objects (type='C'). One row per CHECK constraint.
Additional Columns
| Column | MSSQL Type | SQLite Type | Description |
|---|
| is_disabled | bit | INTEGER NOT NULL DEFAULT 0 | Constraint disabled |
| is_not_for_replication | bit | INTEGER NOT NULL DEFAULT 0 | NOT FOR REPLICATION |
| is_not_trusted | bit | INTEGER NOT NULL DEFAULT 0 | Not verified |
| parent_column_id | int | INTEGER NOT NULL DEFAULT 0 | 0=table-level, else column ID |
| definition | nvarchar(max) | TEXT | SQL expression |
| uses_database_collation | bit | INTEGER NOT NULL DEFAULT 0 | Depends on DB collation |
| is_system_named | bit | INTEGER NOT NULL DEFAULT 0 | System-generated name |
SQLite Backing Table
CREATE TABLE IF NOT EXISTS [sys.check_constraints] (
object_id INTEGER PRIMARY KEY,
is_disabled INTEGER NOT NULL DEFAULT 0,
is_not_for_replication INTEGER NOT NULL DEFAULT 0,
is_not_trusted INTEGER NOT NULL DEFAULT 0,
parent_column_id INTEGER NOT NULL DEFAULT 0,
definition TEXT,
uses_database_collation INTEGER NOT NULL DEFAULT 0,
is_system_named INTEGER NOT NULL DEFAULT 0
);
11. sys.default_constraints
Inherits from sys.objects (type='D'). One row per DEFAULT constraint.
Additional Columns
| Column | MSSQL Type | SQLite Type | Description |
|---|
| parent_column_id | int | INTEGER NOT NULL | Column ID this default belongs to |
| definition | nvarchar(max) | TEXT | SQL expression for the default value |
| is_system_named | bit | INTEGER NOT NULL DEFAULT 0 | System-generated name |
SQLite Backing Table + Derived View
CREATE TABLE IF NOT EXISTS [sys.default_constraints_extra] (
object_id INTEGER PRIMARY KEY,
parent_column_id INTEGER NOT NULL,
definition TEXT,
is_system_named INTEGER NOT NULL DEFAULT 0
);
CREATE VIEW [sys.default_constraints] AS
SELECT o.*, d.parent_column_id, d.definition, d.is_system_named
FROM [sys.objects] o
JOIN [sys.default_constraints_extra] d ON d.object_id = o.object_id
WHERE o.type = 'D';
12. sys.databases
Server-level view. One row per database.
Column Definition (essential subset)
| Column | MSSQL Type | SQLite Type | Description |
|---|
| name | sysname | TEXT NOT NULL | Database name |
| database_id | int | INTEGER PRIMARY KEY | Database ID |
| create_date | datetime | TEXT NOT NULL | Creation date |
| compatibility_level | tinyint | INTEGER NOT NULL DEFAULT 150 | Compat level (150=SQL2019) |
| collation_name | sysname | TEXT | Database collation |
| user_access | tinyint | INTEGER NOT NULL DEFAULT 0 | 0=MULTI_USER |
| user_access_desc | nvarchar(60) | TEXT NOT NULL DEFAULT 'MULTI_USER' | |
| is_read_only | bit | INTEGER NOT NULL DEFAULT 0 | |
| state | tinyint | INTEGER NOT NULL DEFAULT 0 | 0=ONLINE |
| state_desc | nvarchar(60) | TEXT NOT NULL DEFAULT 'ONLINE' | |
| recovery_model | tinyint | INTEGER NOT NULL DEFAULT 3 | 3=SIMPLE |
| recovery_model_desc | nvarchar(60) | TEXT NOT NULL DEFAULT 'SIMPLE' | |
| is_auto_close_on | bit | INTEGER NOT NULL DEFAULT 0 | |
| is_auto_shrink_on | bit | INTEGER NOT NULL DEFAULT 0 | |
| is_fulltext_enabled | bit | INTEGER NOT NULL DEFAULT 0 | |
| is_ansi_null_default_on | bit | INTEGER NOT NULL DEFAULT 0 | |
| is_ansi_nulls_on | bit | INTEGER NOT NULL DEFAULT 1 | |
| is_ansi_padding_on | bit | INTEGER NOT NULL DEFAULT 1 | |
| is_ansi_warnings_on | bit | INTEGER NOT NULL DEFAULT 1 | |
| is_quoted_identifier_on | bit | INTEGER NOT NULL DEFAULT 1 | |
SQLite Backing Table
CREATE TABLE IF NOT EXISTS [sys.databases] (
database_id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
create_date TEXT NOT NULL DEFAULT (datetime('now')),
compatibility_level INTEGER NOT NULL DEFAULT 150,
collation_name TEXT DEFAULT 'SQL_Latin1_General_CP1_CI_AS',
user_access INTEGER NOT NULL DEFAULT 0,
user_access_desc TEXT NOT NULL DEFAULT 'MULTI_USER',
is_read_only INTEGER NOT NULL DEFAULT 0,
state INTEGER NOT NULL DEFAULT 0,
state_desc TEXT NOT NULL DEFAULT 'ONLINE',
recovery_model INTEGER NOT NULL DEFAULT 3,
recovery_model_desc TEXT NOT NULL DEFAULT 'SIMPLE',
is_auto_close_on INTEGER NOT NULL DEFAULT 0,
is_auto_shrink_on INTEGER NOT NULL DEFAULT 0,
is_fulltext_enabled INTEGER NOT NULL DEFAULT 0,
is_ansi_null_default_on INTEGER NOT NULL DEFAULT 0,
is_ansi_nulls_on INTEGER NOT NULL DEFAULT 1,
is_ansi_padding_on INTEGER NOT NULL DEFAULT 1,
is_ansi_warnings_on INTEGER NOT NULL DEFAULT 1,
is_quoted_identifier_on INTEGER NOT NULL DEFAULT 1
);
Seed Data
| database_id | name |
|---|
| 1 | master |
| 2 | tempdb |
| 3 | model |
| 4 | msdb |
The user's working database should be added dynamically (database_id=5+).
Mssqlite keeps this server-level table mirrored in each database-owned catalog.
The initial store also owns an internal sys._database_files manifest with
database_id, SQL name, backing location, and an engine-owned deletion flag;
it is an implementation table, not a SQL Server catalog surface.
13. sys.database_principals
Database-level security principals.
Column Definition (essential subset)
| Column | MSSQL Type | SQLite Type | Description |
|---|
| name | sysname | TEXT NOT NULL | Principal name |
| principal_id | int | INTEGER PRIMARY KEY | |
| type | char(1) | TEXT NOT NULL | S=SQL user, R=Database role, U=Windows user |
| type_desc | nvarchar(60) | TEXT NOT NULL | SQL_USER, DATABASE_ROLE, WINDOWS_USER |
| default_schema_name | sysname | TEXT | Default schema |
| create_date | datetime | TEXT NOT NULL | |
| modify_date | datetime | TEXT NOT NULL | |
| owning_principal_id | int | INTEGER | Owner principal ID |
| sid | varbinary(85) | BLOB | Security identifier |
| is_fixed_role | bit | INTEGER NOT NULL DEFAULT 0 | Built-in fixed role |
| authentication_type | int | INTEGER NOT NULL DEFAULT 1 | 0=NONE, 1=INSTANCE, 2=DATABASE, 3=WINDOWS |
| authentication_type_desc | nvarchar(60) | TEXT NOT NULL DEFAULT 'INSTANCE' | |
Seed Data (built-in database principals)
| principal_id | name | type | type_desc | default_schema_name | is_fixed_role |
|---|
| 0 | public | R | DATABASE_ROLE | NULL | 1 |
| 1 | dbo | S | SQL_USER | dbo | 0 |
| 2 | guest | S | SQL_USER | guest | 0 |
| 3 | INFORMATION_SCHEMA | S | SQL_USER | NULL | 0 |
| 4 | sys | S | SQL_USER | NULL | 0 |
| 16384 | db_owner | R | DATABASE_ROLE | NULL | 1 |
| 16385 | db_accessadmin | R | DATABASE_ROLE | NULL | 1 |
| 16386 | db_securityadmin | R | DATABASE_ROLE | NULL | 1 |
| 16387 | db_ddladmin | R | DATABASE_ROLE | NULL | 1 |
| 16389 | db_backupoperator | R | DATABASE_ROLE | NULL | 1 |
| 16390 | db_datareader | R | DATABASE_ROLE | NULL | 1 |
| 16391 | db_datawriter | R | DATABASE_ROLE | NULL | 1 |
| 16392 | db_denydatareader | R | DATABASE_ROLE | NULL | 1 |
| 16393 | db_denydatawriter | R | DATABASE_ROLE | NULL | 1 |
14. sys.server_principals
Server-level security principals.
Column Definition (essential subset)
| Column | MSSQL Type | SQLite Type | Description |
|---|
| name | sysname | TEXT NOT NULL | Principal name |
| principal_id | int | INTEGER PRIMARY KEY | |
| sid | varbinary(85) | BLOB | Security identifier |
| type | char(1) | TEXT NOT NULL | S=SQL login, R=Server role |
| type_desc | nvarchar(60) | TEXT NOT NULL | SQL_LOGIN, SERVER_ROLE |
| is_disabled | int | INTEGER NOT NULL DEFAULT 0 | Login disabled |
| create_date | datetime | TEXT NOT NULL | |
| modify_date | datetime | TEXT NOT NULL | |
| default_database_name | sysname | TEXT DEFAULT 'master' | |
| default_language_name | sysname | TEXT DEFAULT 'us_english' | |
| is_fixed_role | bit | INTEGER NOT NULL DEFAULT 0 | Built-in role |
Seed Data
| principal_id | name | type | type_desc | is_fixed_role |
|---|
| 1 | sa | S | SQL_LOGIN | 0 |
| 2 | public | R | SERVER_ROLE | 1 |
| 3 | sysadmin | R | SERVER_ROLE | 1 |
| 4 | securityadmin | R | SERVER_ROLE | 1 |
| 5 | serveradmin | R | SERVER_ROLE | 1 |
| 6 | setupadmin | R | SERVER_ROLE | 1 |
| 7 | processadmin | R | SERVER_ROLE | 1 |
| 8 | diskadmin | R | SERVER_ROLE | 1 |
| 9 | dbcreator | R | SERVER_ROLE | 1 |
| 10 | bulkadmin | R | SERVER_ROLE | 1 |
15. sys.identity_columns
A view over sys.columns filtered to is_identity=1, with additional columns.
Additional Columns (beyond sys.columns)
| Column | MSSQL Type | SQLite Type | Description |
|---|
| seed_value | sql_variant | TEXT | Identity seed value |
| increment_value | sql_variant | TEXT | Identity increment value |
| last_value | sql_variant | TEXT | Last generated value |
| is_not_for_replication | bit | INTEGER NOT NULL DEFAULT 0 | NOT FOR REPLICATION |
SQLite View
CREATE VIEW IF NOT EXISTS [sys.identity_columns] AS
SELECT c.*, ic.seed_value, ic.increment_value, ic.last_value, ic.is_not_for_replication
FROM [sys.columns] c
JOIN [sys.identity_columns_extra] ic
ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE c.is_identity = 1;
Backing table for extra fields:
CREATE TABLE IF NOT EXISTS [sys.identity_columns_extra] (
object_id INTEGER NOT NULL,
column_id INTEGER NOT NULL,
seed_value TEXT DEFAULT '1',
increment_value TEXT DEFAULT '1',
last_value TEXT,
is_not_for_replication INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (object_id, column_id)
);
The engine hydrates one shared allocator per row from this table. Seed,
increment, and last values remain canonical decimal TEXT so decimal(38,0)
definitions are exact; dirty last_value is written after the surrounding
SQLite transaction ends, including after rollback, because ordinary identity
allocation is rollback-independent.
16. sys.computed_columns
A view over sys.columns filtered to is_computed=1, with additional columns.
Additional Columns
| Column | MSSQL Type | SQLite Type | Description |
|---|
| definition | nvarchar(max) | TEXT | SQL expression |
| uses_database_collation | bit | INTEGER NOT NULL DEFAULT 0 | Depends on DB collation |
| is_persisted | bit | INTEGER NOT NULL DEFAULT 0 | Physically stored |
SQLite View
CREATE VIEW IF NOT EXISTS [sys.computed_columns] AS
SELECT c.*, cc.definition, cc.uses_database_collation, cc.is_persisted
FROM [sys.columns] c
JOIN [sys.computed_columns_extra] cc
ON c.object_id = cc.object_id AND c.column_id = cc.column_id
WHERE c.is_computed = 1;
Backing table:
CREATE TABLE IF NOT EXISTS [sys.computed_columns_extra] (
object_id INTEGER NOT NULL,
column_id INTEGER NOT NULL,
definition TEXT,
uses_database_collation INTEGER NOT NULL DEFAULT 0,
is_persisted INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (object_id, column_id)
);
17. Common System Functions That Query Catalog
These T-SQL functions must be emulated to support catalog queries:
| Function | Returns | Equivalent |
|---|
OBJECT_ID(name) | int | Lookup sys.objects by name |
OBJECT_NAME(id) | sysname | Lookup sys.objects by object_id |
SCHEMA_ID(name) | int | Lookup sys.schemas by name |
SCHEMA_NAME(id) | sysname | Lookup sys.schemas by schema_id |
TYPE_ID(name) | int | Lookup sys.types by name |
TYPE_NAME(id) | sysname | Lookup sys.types by user_type_id |
DB_ID(name) | int | Lookup sys.databases by name |
DB_NAME(id) | sysname | Lookup sys.databases by database_id |
COL_NAME(obj_id, col_id) | sysname | Lookup sys.columns |
COLUMNPROPERTY(obj_id, col, prop) | int | Column metadata lookup |
OBJECTPROPERTY(obj_id, prop) | int | Object metadata lookup |
@@SERVERNAME | sysname | Server name (configurable) |
@@VERSION | nvarchar | Version string |
@@SPID | smallint | Session process ID |
SERVERPROPERTY(prop) | sql_variant | Server property lookup |
18. DDL → Catalog Maintenance
When the server processes DDL statements, it must maintain catalog tables accordingly.
CREATE TABLE
- Insert row into
sys.objects (type='U', generate unique object_id)
- For each column, insert row into
sys.columns with correct type mapping
- If table has PRIMARY KEY: insert into
sys.objects (type='PK'), sys.key_constraints, sys.indexes (index_id=1, type=1), and sys.index_columns
- If table has UNIQUE constraints: insert into
sys.objects (type='UQ'), sys.key_constraints, sys.indexes (type=2), sys.index_columns
- If table has FOREIGN KEYs: insert into
sys.objects (type='F'), sys.foreign_keys, sys.foreign_key_columns
- If table has CHECK constraints: insert into
sys.objects (type='C'), sys.check_constraints
- If table has DEFAULT constraints: insert into
sys.objects (type='D'), sys.default_constraints
- If column has IDENTITY: mark
is_identity=1 in sys.columns, insert into sys.identity_columns_extra
- Always insert a heap entry:
sys.indexes (index_id=0, type=0) unless clustered PK exists
DROP TABLE
- Delete from
sys.index_columns where object_id matches
- Delete from
sys.indexes where object_id matches
- Delete from
sys.columns where object_id matches
- Delete from
sys.foreign_key_columns where parent_object_id or constraint_object_id matches
- Delete from
sys.foreign_keys where object_id matches
- Delete from
sys.key_constraints where object_id matches
- Delete from
sys.check_constraints where object_id matches
- Delete from
sys.default_constraints where object_id matches
- Delete from
sys.identity_columns_extra where object_id matches
- Delete from
sys.computed_columns_extra where object_id matches
- Delete from
sys.objects where object_id matches AND all child constraint objects
ALTER TABLE ADD COLUMN
- Insert new row into
sys.columns
- Update
max_column_id_used in sys.tables logic
ALTER TABLE ALTER COLUMN
- Validate dependent indexes, keys, checks, defaults, computed columns, and
identity/rowversion restrictions before catalog mutation.
- Update the existing
sys.columns row's system/user type ids, max_length,
precision, scale, collation_name, and is_nullable in place; object_id and
column_id must not change.
- Preserve identity and constraint child rows, and update the parent
sys.objects.modify_date in the same savepoint as the physical rebuild.
CREATE/DROP INDEX
- Insert/delete
sys.indexes row
- Insert/delete
sys.index_columns rows
19. Object ID Generation
MSSQL uses arbitrary positive integers for object_id. For mssqlite:
- Use a monotonically increasing counter starting at a high value (e.g., 100000001)
- Store the counter in a metadata table:
CREATE TABLE [sys._next_id] (next_id INTEGER NOT NULL)
- System-predefined objects can use lower IDs (1-999)
- Negative object_ids are reserved for system internal objects in MSSQL
20. TDS Column Metadata for Catalog Queries
When returning results from catalog queries, each column must have correct TDS metadata. Key mappings:
| sys.* Column Type | TDS Wire Type | Notes |
|---|
| sysname fields (name, etc.) | NVARCHAR(128) | max_length=256 (bytes), system_type_id=231 |
| int fields (object_id, etc.) | INTN(4) | system_type_id=56 |
| tinyint fields | INTN(1) | system_type_id=48 |
| smallint fields | INTN(2) | system_type_id=52 |
| bit fields | BITN | system_type_id=104 |
| nvarchar(60) fields (type_desc) | NVARCHAR(60) | max_length=120 (bytes) |
| nvarchar(max) fields (definition) | NVARCHAR(MAX) | max_length=-1, uses PLP |
| datetime fields | DATETIMEN(8) | system_type_id=61 |
| varbinary(85) fields (sid) | VARBINARY(85) | max_length=85, system_type_id=165 |
21. Priority Implementation Order
For practical client compatibility, implement in this order:
Phase 1 - Core (required for most ORMs/tools)
sys.schemas — needed by everythingsys.types — needed for column metadatasys.objects — central catalogsys.columns — column metadatasys.tables — filtered view of objectssys.databases — database listingsys.indexes — index metadata
Phase 2 - Constraints & Security
sys.key_constraints — PK/UQ constraintssys.index_columns — index column detailssys.foreign_keys + sys.foreign_key_columns — FK metadatasys.database_principals — security contextsys.server_principals — login context
Phase 3 - Extended Views
sys.check_constraints — CHECK constraintssys.default_constraints — DEFAULT constraintssys.identity_columns — identity metadatasys.computed_columns — computed column metadatasys.views — view metadata- System functions (OBJECT_ID, SCHEMA_NAME, etc.)
