SQL Server Schema

function

The function block describes a function in a database schema.

function "positive" {
  schema = schema.public
  lang   = SQL
  arg "v" {
    type = integer
  }
  ...
}

function attributes

Name	Required	Value
as	true	string
depends_on	false	List of object references
execute_as	false	The execution context of the function can be one of: Raw expression defined with sql("expr") string
inline	false	bool
lang	true	Function language can be one of: string enum (SQL, CRL)
null_call	false	enum (CALLED, RETURNS_NULL)
return	false	Function return type can be one of: Schema type Raw expression defined with sql("expr") Object reference to type_alias
schema	true	Object reference to schema
schema_bound	false	bool

function blocks

function.arg

function.arg attributes

Name	Required	Value
default	false	Function argument default value can be one of: bool Raw expression defined with sql("expr") string number
mode	false	Function argument mode can be one of: string enum (IN, INOUT, OUT)
readonly	false	bool
type	true	Function argument type can be one of: Schema type Raw expression defined with sql("expr") Object reference to type_alias Object reference to type_table

function.arg constraints

Constraint	Value
Required	false
Require Name	false
Repeatable	true

function.return_table

function.return_table blocks

function.return_table.check

function.return_table.check attributes

Name	Required	Value
expr	true	string

function.return_table.check constraints

Constraint	Value
Required	false
Require Name	false
Repeatable	true

function.return_table.column

function.return_table.column attributes

Name	Required	Value
as	false	string
collate	false	string
comment	false	string
default	false	Column default value can be one of: bool string number Raw expression defined with sql("expr")
null	false	bool
type	true	Column type can be one of: Schema type Raw expression defined with sql("expr") Object reference to type_alias

function.return_table.column blocks

function.return_table.column.as

function.return_table.column.as attributes

Name	Required	Value
expr	true	string
type	false	enum (PERSISTED)

function.return_table.column.identity

function.return_table.column.identity attributes

Name	Required	Value
increment	false	int
seed	false	int

function.return_table.column constraints

Constraint	Value
Required	true
Require Name (e.g., function.return_table.column "name" )	true
Mutually exclusive sets	[as (attribute), as (block)]

function.return_table.index

function.return_table.index attributes

Name	Required	Value
columns	false	Index columns can be one of: List of object reference to column List of object reference to table.column
include	false	Index included columns can be one of: List of object reference to column List of object reference to table.column
nonclustered	false	bool

function.return_table.index blocks

function.return_table.index.on

function.return_table.index.on attributes

Name	Required	Value
column	true	Index columns can be one of: Object reference to column Object reference to table.column
desc	false	bool

function.return_table.index.on constraints

Constraint	Value
Required	false
Require Name	false
Repeatable	true

function.return_table.index constraints

Constraint	Value
Required	false
Require Name (e.g., function.return_table.index "name" )	true
Mutually exclusive sets	[columns, on]
One of required sets	[columns, on]

function.return_table.primary_key

function.return_table.primary_key attributes

Name	Required	Value
columns	false	Primary key columns can be one of: List of object reference to column List of object reference to table.column
comment	false	string
fill_factor	false	int
include	false	Primary key included columns can be one of: List of object reference to column List of object reference to table.column
nonclustered	false	bool
type	false	Index key type can be one of: string enum (HASH)

function.return_table.primary_key blocks

function.return_table.primary_key.on

function.return_table.primary_key.on attributes

Name	Required	Value
column	true	Primary key columns can be one of: Object reference to column Object reference to table.column
desc	false	bool

function.return_table.primary_key.on constraints

Constraint	Value
Required	false
Require Name	false
Repeatable	true

function.return_table.primary_key constraints

Constraint	Value
Required	false
Require Name	false
Mutually exclusive sets	[columns, on]
One of required sets	[columns, on]

function.return_table.unique

The unique block defines a unique constraint on a table or view. It can be used to ensure that the values in a column or a set of columns are unique across the table.

Creating a unique constraint will automatically create a unique index on the specified columns.

function.return_table.unique attributes

Name	Required	Value
columns	false	Index columns can be one of: List of object reference to column List of object reference to table.column
fill_factor	false	int
nonclustered	false	bool

function.return_table.unique blocks

function.return_table.unique.on

function.return_table.unique.on attributes

Name	Required	Value
column	false	Constraint columns can be one of: Object reference to column Object reference to table.column
desc	false	bool

function.return_table.unique.on constraints

Constraint	Value
Required	false
Require Name	false
Repeatable	true
Mutually exclusive sets	[column, expr]

function.return_table.unique constraints

Constraint	Value
Required	false
Require Name	false
Repeatable	true
Mutually exclusive sets	[columns, on]
One of required sets	[columns, on]

function.return_table constraints

Constraint	Value
Required	false
Require Name (e.g., function.return_table "name" )	true

function constraints

Constraint	Value
Required	false
Require Name (e.g., function "name" )	true
Allow Qualifier (e.g., function "schema" "name" )	true

procedure

The procedure block describes a procedure in a database schema.

procedure "proc" {
  schema = schema.public
  lang   = SQL
  arg "a" {
    type = integer
  }
  ...
}

procedure attributes

Name	Required	Value
as	true	string
depends_on	false	List of object references
execute_as	false	The execution context of the procedure can be one of: Raw expression defined with sql("expr") string
lang	true	Procedure language can be one of: string enum (SQL, CRL)
schema	true	Object reference to schema
schema_bound	false	bool

procedure blocks

procedure.arg

procedure.arg attributes

Name	Required	Value
default	false	Procedure argument default value can be one of: bool Raw expression defined with sql("expr") string number
mode	false	Procedure argument mode can be one of: string enum (IN, INOUT, OUT)
readonly	false	bool
type	true	Procedure argument type can be one of: Schema type Raw expression defined with sql("expr") Object reference to type_alias Object reference to type_table

procedure.arg constraints

Constraint	Value
Required	false
Require Name (e.g., procedure.arg "name" )	true

procedure constraints

Constraint	Value
Required	false
Require Name (e.g., procedure "name" )	true
Allow Qualifier (e.g., procedure "schema" "name" )	true

schema

The schema block describes a database schema.

schema "public" {
  ...
}

schema attributes

Name	Required	Value
name	false	string

schema constraints

Constraint	Value
Required	false
Require Name (e.g., schema "name" )	true

sequence

The sequence block describes a sequence in a database schema.

sequence "name" {
  schema    = schema.public
  type      = int
  increment = 3
  min_value = 9
}

sequence attributes

Name	Required	Value
cache	false	int
cycle	false	bool
increment	false	int
max_value	false	int
min_value	false	int
schema	true	Object reference to schema
start	false	int
type	false	Sequence type can be one of: Schema type Object reference to type_alias

sequence constraints

Constraint	Value
Required	false
Require Name (e.g., sequence "name" )	true
Allow Qualifier (e.g., sequence "schema" "name" )	true

table

The table block describes a table in a database schema.

table "users" {
  schema = schema.public
  column "id" {
    type = int
  }
  ...
}

table attributes

Name	Required	Value
comment	false	string
depends_on	false	List of object references
graph	false	enum (NODE, EDGE)
memory_optimized	false	bool
schema	true	Object reference to schema
system_versioned	false	bool

table blocks

table.check

table.check attributes

Name	Required	Value
expr	true	string

table.check constraints

Constraint	Value
Required	false
Require Name	false
Repeatable	true

table.column

table.column attributes

Name	Required	Value
as	false	string
collate	false	string
comment	false	string
default	false	Column default value can be one of: bool string number Raw expression defined with sql("expr")
null	false	bool
type	true	Column type can be one of: Schema type Raw expression defined with sql("expr") Object reference to type_alias

table.column blocks

table.column.as

table.column.as attributes

Name	Required	Value
expr	true	string
type	false	enum (PERSISTED)

table.column.default

table.column.default attributes

Name	Required	Value
as	true	Column default value can be one of: bool string number Raw expression defined with sql("expr")

table.column.default constraints

Constraint	Value
Required	false
Require Name (e.g., table.column.default "name" )	true

table.column.generated_always

table.column.generated_always attributes

Name	Required	Value
as	true	enum (ROW_START, ROW_END, TRANSACTION_ID_START, TRANSACTION_ID_END, SEQUENCE_NUMBER_START, SEQUENCE_NUMBER_END)
hidden	false	bool

table.column.identity

table.column.identity attributes

Name	Required	Value
increment	false	int
seed	false	int

table.column constraints

Constraint	Value
Required	false
Require Name (e.g., table.column "name" )	true
Mutually exclusive sets	[as (attribute), as (block)]

table.connection

table.connection attributes

Name	Required	Value
on_delete	true	enum (NO_ACTION, CASCADE)

table.connection blocks

table.connection.clause

table.connection.clause attributes

Name	Required	Value
from	true	Object reference to table
to	true	Object reference to table

table.connection.clause constraints

Constraint	Value
Required	true
Require Name	false
Repeatable	true

table.connection constraints

Constraint	Value
Required	false
Require Name (e.g., table.connection "name" )	true

table.foreign_key

table.foreign_key attributes

Name	Required	Value
columns	true	Foreign key columns can be one of: List of object reference to column List of object reference to table.column
comment	false	string
on_delete	false	enum (NO_ACTION, RESTRICT, CASCADE, SET_NULL, SET_DEFAULT)
on_update	false	enum (NO_ACTION, RESTRICT, CASCADE, SET_NULL, SET_DEFAULT)
ref_columns	true	Foreign key reference columns can be one of: List of object reference to column List of object reference to table.column

table.foreign_key constraints

Constraint	Value
Required	false
Require Name (e.g., table.foreign_key "name" )	true

table.fulltext

table.fulltext attributes

Name	Required	Value
catalog	false	string
columns	false	List of object reference to column
filegroup	false	string
primary_key	false	bool
unique_key	false	Object reference to index

table.fulltext blocks

table.fulltext.on

table.fulltext.on attributes

Name	Required	Value
column	true	Object reference to column
language	false	string
semantic	false	bool
type	false	Object reference to column

table.fulltext.on constraints

Constraint	Value
Required	false
Require Name	false
Repeatable	true

table.fulltext constraints

Constraint	Value
Required	false
Require Name	false
Mutually exclusive sets	[primary_key, unique_key], [columns, on]
One of required sets	[primary_key, unique_key], [columns, on]

table.index

table.index attributes

Name	Required	Value
cells_per_object	false	int
columns	false	Index columns can be one of: List of object reference to column List of object reference to table.column
comment	false	string
fill_factor	false	int
include	false	Index included columns can be one of: List of object reference to column List of object reference to table.column
nonclustered	false	bool
tessellation_scheme	false	enum (GEOMETRY_GRID, GEOMETRY_AUTO_GRID, GEOGRAPHY_GRID, GEOGRAPHY_AUTO_GRID)
type	false	Index key type can be one of: string enum (COLUMNSTORE, HASH, HEAP, SPATIAL, XML)
unique	false	bool
where	false	string

table.index blocks

table.index.bounding_box

table.index.bounding_box attributes

Name	Required	Value
xmax	true	number
xmin	true	number
ymax	true	number
ymin	true	number

table.index.grids

table.index.grids attributes

Name	Required	Value
level_1	false	enum (LOW, MEDIUM, HIGH)
level_2	false	enum (LOW, MEDIUM, HIGH)
level_3	false	enum (LOW, MEDIUM, HIGH)
level_4	false	enum (LOW, MEDIUM, HIGH)

table.index.on

table.index.on attributes

Name	Required	Value
column	true	Index columns can be one of: Object reference to column Object reference to table.column
desc	false	bool

table.index.on constraints

Constraint	Value
Required	false
Require Name	false
Repeatable	true

table.index constraints

Constraint	Value
Required	false
Require Name (e.g., table.index "name" )	true
Mutually exclusive sets	[columns, on]
One of required sets	[columns, on]

table.period

table.period attributes

Name	Required	Value
end	true	Object reference to column
start	true	Object reference to column
type	true	enum (SYSTEM_TIME)

table.period constraints

Constraint	Value
Required	false
Require Name (e.g., table.period "name" )	true

table.primary_key

table.primary_key attributes

Name	Required	Value
columns	false	Primary key columns can be one of: List of object reference to column List of object reference to table.column
comment	false	string
fill_factor	false	int
include	false	Primary key included columns can be one of: List of object reference to column List of object reference to table.column
nonclustered	false	bool
type	false	Index key type can be one of: string enum (HASH)

table.primary_key blocks

table.primary_key.on

table.primary_key.on attributes

Name	Required	Value
column	true	Primary key columns can be one of: Object reference to column Object reference to table.column
desc	false	bool

table.primary_key.on constraints

Constraint	Value
Required	false
Require Name	false
Repeatable	true

table.primary_key constraints

Constraint	Value
Required	false
Require Name	false
Mutually exclusive sets	[columns, on]
One of required sets	[columns, on]

table.system_versioned

table.system_versioned attributes

Name	Required	Value
history_table	false	string
retention	false	int
retention_unit	false	enum (DAY, WEEK, MONTH, YEAR)

table.unique

The unique block defines a unique constraint on a table or view. It can be used to ensure that the values in a column or a set of columns are unique across the table.

Creating a unique constraint will automatically create a unique index on the specified columns.

table.unique attributes

Name	Required	Value
columns	false	Index columns can be one of: List of object reference to column List of object reference to table.column
fill_factor	false	int
nonclustered	false	bool

table.unique blocks

table.unique.on

table.unique.on attributes

Name	Required	Value
column	false	Constraint columns can be one of: Object reference to column Object reference to table.column
desc	false	bool

table.unique.on constraints

Constraint	Value
Required	false
Require Name	false
Repeatable	true
Mutually exclusive sets	[column, expr]

table.unique constraints

Constraint	Value
Required	false
Require Name	false
Mutually exclusive sets	[columns, on]
One of required sets	[columns, on]

table constraints

Constraint	Value
Required	false
Require Name (e.g., table "name" )	true
Allow Qualifier (e.g., table "schema" "name" )	true
Mutually exclusive sets	[graph, memory_optimized], [graph, system_versioned]

trigger

The trigger block describes a trigger on a table in a database schema.

trigger "trigger_orders_audit" {
  on = table.orders
  ...
}

trigger attributes

Name	Required	Value
as	true	string
execute_as	false	The execution context of the trigger can be one of: Raw expression defined with sql("expr") string
name	false	string
on	true	Trigger on can be one of: Object reference to table Object reference to view
schema_bound	false	bool

trigger blocks

trigger.after

trigger.after attributes

Name	Required	Value
delete	false	bool
insert	false	bool
update	false	bool

trigger.after constraints

Constraint	Value
Required	false
Require Name	false
One of required sets	[insert, delete, update]

trigger.instead_of

trigger.instead_of attributes

Name	Required	Value
delete	false	bool
insert	false	bool
update	false	bool

trigger.instead_of constraints

Constraint	Value
Required	false
Require Name	false
One of required sets	[insert, delete, update]

trigger constraints

Constraint	Value
Required	false
Require Name (e.g., trigger "name" )	true
Repeatable	true
Mutually exclusive sets	[after, instead_of]
One of required sets	[after, instead_of]

type_alias

type_alias attributes

Name	Required	Value
null	false	bool
schema	true	Object reference to schema
type	true	Base type can be one of: Schema type Raw expression defined with sql("expr")

type_alias constraints

Constraint	Value
Required	false
Require Name (e.g., type_alias "name" )	true
Allow Qualifier (e.g., type_alias "schema" "name" )	true

type_table

type_table attributes

Name	Required	Value
schema	true	Object reference to schema

type_table blocks

type_table.check

type_table.check attributes

Name	Required	Value
expr	true	string

type_table.check constraints

Constraint	Value
Required	false
Require Name	false
Repeatable	true

type_table.column

type_table.column attributes

Name	Required	Value
as	false	string
collate	false	string
comment	false	string
default	false	Column default value can be one of: bool string number Raw expression defined with sql("expr")
null	false	bool
type	true	Column type can be one of: Schema type Raw expression defined with sql("expr") Object reference to type_alias

type_table.column blocks

type_table.column.as

type_table.column.as attributes

Name	Required	Value
expr	true	string
type	false	enum (PERSISTED)

type_table.column.identity

type_table.column.identity attributes

Name	Required	Value
increment	false	int
seed	false	int

type_table.column constraints

Constraint	Value
Required	true
Require Name (e.g., type_table.column "name" )	true
Mutually exclusive sets	[as (attribute), as (block)]

type_table.index

type_table.index attributes

Name	Required	Value
columns	false	Index columns can be one of: List of object reference to column List of object reference to table.column
include	false	Index included columns can be one of: List of object reference to column List of object reference to table.column
nonclustered	false	bool

type_table.index blocks

type_table.index.on

type_table.index.on attributes

Name	Required	Value
column	true	Index columns can be one of: Object reference to column Object reference to table.column
desc	false	bool

type_table.index.on constraints

Constraint	Value
Required	false
Require Name	false
Repeatable	true

type_table.index constraints

Constraint	Value
Required	false
Require Name (e.g., type_table.index "name" )	true
Mutually exclusive sets	[columns, on]
One of required sets	[columns, on]

type_table.primary_key

type_table.primary_key attributes

Name	Required	Value
columns	false	Primary key columns can be one of: List of object reference to column List of object reference to table.column
comment	false	string
fill_factor	false	int
include	false	Primary key included columns can be one of: List of object reference to column List of object reference to table.column
nonclustered	false	bool
type	false	Index key type can be one of: string enum (HASH)

type_table.primary_key blocks

type_table.primary_key.on

type_table.primary_key.on attributes

Name	Required	Value
column	true	Primary key columns can be one of: Object reference to column Object reference to table.column
desc	false	bool

type_table.primary_key.on constraints

Constraint	Value
Required	false
Require Name	false
Repeatable	true

type_table.primary_key constraints

Constraint	Value
Required	false
Require Name	false
Mutually exclusive sets	[columns, on]
One of required sets	[columns, on]

type_table.unique

The unique block defines a unique constraint on a table or view. It can be used to ensure that the values in a column or a set of columns are unique across the table.

Creating a unique constraint will automatically create a unique index on the specified columns.

type_table.unique attributes

Name	Required	Value
columns	false	Index columns can be one of: List of object reference to column List of object reference to table.column
fill_factor	false	int
nonclustered	false	bool

type_table.unique blocks

type_table.unique.on

type_table.unique.on attributes

Name	Required	Value
column	false	Constraint columns can be one of: Object reference to column Object reference to table.column
desc	false	bool

type_table.unique.on constraints

Constraint	Value
Required	false
Require Name	false
Repeatable	true
Mutually exclusive sets	[column, expr]

type_table.unique constraints

Constraint	Value
Required	false
Require Name	false
Repeatable	true
Mutually exclusive sets	[columns, on]
One of required sets	[columns, on]

type_table constraints

Constraint	Value
Required	false
Require Name (e.g., type_table "name" )	true
Allow Qualifier (e.g., type_table "schema" "name" )	true

view

The view block describes a view in a database schema.

view "clean_users" {
  schema = schema.public
  column "id" {
    type = int
  }
  ...
}

view attributes

Name	Required	Value
as	true	string
check_option	false	enum (LOCAL, LOCAL, CASCADED)
comment	false	string
depends_on	false	List of object references
schema	true	Object reference to schema
schema_bound	false	bool

view blocks

view.column

view.column attributes

Name	Required	Value
null	false	bool
type	true	Column type can be one of: Schema type Raw expression defined with sql("expr") Object reference to type_alias

view.column constraints

Constraint	Value
Required	false
Require Name (e.g., view.column "name" )	true

view.fulltext

view.fulltext attributes

Name	Required	Value
catalog	false	string
columns	false	List of object reference to column
filegroup	false	string
primary_key	false	bool
unique_key	false	Object reference to index

view.fulltext blocks

view.fulltext.on

view.fulltext.on attributes

Name	Required	Value
column	true	Object reference to column
language	false	string
semantic	false	bool
type	false	Object reference to column

view.fulltext.on constraints

Constraint	Value
Required	false
Require Name	false
Repeatable	true

view.fulltext constraints

Constraint	Value
Required	false
Require Name	false
Mutually exclusive sets	[primary_key, unique_key], [columns, on]
One of required sets	[primary_key, unique_key], [columns, on]

view.index

view.index attributes

Name	Required	Value
cells_per_object	false	int
columns	false	Index columns can be one of: List of object reference to column List of object reference to table.column
comment	false	string
fill_factor	false	int
include	false	Index included columns can be one of: List of object reference to column List of object reference to table.column
nonclustered	false	bool
tessellation_scheme	false	enum (GEOMETRY_GRID, GEOMETRY_AUTO_GRID, GEOGRAPHY_GRID, GEOGRAPHY_AUTO_GRID)
type	false	Index key type can be one of: string enum (COLUMNSTORE, HASH, HEAP, SPATIAL, XML)
unique	false	bool
where	false	string

view.index blocks

view.index.bounding_box

view.index.bounding_box attributes

Name	Required	Value
xmax	true	number
xmin	true	number
ymax	true	number
ymin	true	number

view.index.grids

view.index.grids attributes

Name	Required	Value
level_1	false	enum (LOW, MEDIUM, HIGH)
level_2	false	enum (LOW, MEDIUM, HIGH)
level_3	false	enum (LOW, MEDIUM, HIGH)
level_4	false	enum (LOW, MEDIUM, HIGH)

view.index.on

view.index.on attributes

Name	Required	Value
column	true	Index columns can be one of: Object reference to column Object reference to table.column
desc	false	bool

view.index.on constraints

Constraint	Value
Required	false
Require Name	false
Repeatable	true

view.index constraints

Constraint	Value
Required	false
Require Name (e.g., view.index "name" )	true
Mutually exclusive sets	[columns, on]
One of required sets	[columns, on]

view constraints

Constraint	Value
Required	false
Require Name (e.g., view "name" )	true
Allow Qualifier (e.g., view "schema" "name" )	true