# Microsoft SQL Server

> Query and change data in Microsoft SQL Server and Azure SQL: run T-SQL with safe parameters, call stored procedures, select, insert, update, upsert and delete rows without writing SQL, create and drop tables, and look up schemas, tables, columns and indexes.

App · Databases · 20 actions. Web page: https://docs.lodol.com/docs/integrations/mssql

## Actions

### Count rows

How many rows in a table match the given values.

**Inputs**

- Table (`table`, text, required): The table's name, e.g. Customers (or sales.Customers to name the schema too). Use 'List tables' to find one.
- Schema (`schema`, text): The table's schema. Most tables are in dbo. Default: `dbo`.
- Match rows where (`filters`, object): Column and value pairs every row must equal, e.g. {"status": "active"}. A list matches any of its values; null matches empty.

**Outputs**

- `count` (number)
- `table` (text)

**Example output**

```json
{
  "count": 42,
  "table": "dbo.Customers"
}
```

### Create table

Create a table from a list of columns. By default an existing table of that name is left alone.

**Inputs**

- Table (`table`, text, required): The table's name, e.g. Customers (or sales.Customers to name the schema too). Use 'List tables' to find one.
- Schema (`schema`, text): The table's schema. Most tables are in dbo. Default: `dbo`.
- Columns (`columns`, list, required): One entry per column, written as name then type, e.g. 'id int identity primary key', 'email nvarchar(255) not null', 'amount decimal(10,2)'. Types: bigint, int, smallint, tinyint, bit, decimal, numeric, money, smallmoney, float, real, date, time, datetime2, datetimeoffset, datetime, smalldatetime, char, varchar, nchar, nvarchar, binary, varbinary, uniqueidentifier, xml.
- Skip if it exists (`if_not_exists`, true/false): Leave an existing table alone instead of failing. Default: `true`.

**Outputs**

- `created` (true/false)
- `table` (text)

**Example output**

```json
{
  "created": true,
  "table": "dbo.Customers"
}
```

### Delete rows

Remove the rows matching the given values. At least one match is required.

**Inputs**

- Table (`table`, text, required): The table's name, e.g. Customers (or sales.Customers to name the schema too). Use 'List tables' to find one.
- Schema (`schema`, text): The table's schema. Most tables are in dbo. Default: `dbo`.
- Match rows where (`filters`, object, required): Column and value pairs every row must equal, e.g. {"id": 42}. Required, so the whole table can never be changed by accident.

**Outputs**

- `rows_affected` (number)
- `table` (text)

**Example output**

```json
{
  "rows_affected": 1,
  "table": "dbo.Customers"
}
```

### Drop table

Delete a table and every row in it. This cannot be undone.

**Inputs**

- Table (`table`, text, required): The table's name, e.g. Customers (or sales.Customers to name the schema too). Use 'List tables' to find one.
- Schema (`schema`, text): The table's schema. Most tables are in dbo. Default: `dbo`.
- Ignore if missing (`if_exists`, true/false): Succeed without doing anything if it is not there. Default: `true`.

**Outputs**

- `dropped` (true/false)
- `table` (text)

**Example output**

```json
{
  "dropped": true,
  "table": "dbo.Customers"
}
```

### Get server info

The server's edition and version, the connected database and the login in use. The quickest way to check the connection.

**Inputs**: None.

**Outputs**

- `collation` (text)
- `database_name` (text)
- `edition` (text)
- `engine_edition` (number)
- `engine_edition_name` (text)
- `login_name` (text)
- `product_level` (text)
- `product_version` (text)
- `server_name` (text)
- `version_string` (text)

**Example output**

```json
{
  "collation": "SQL_Latin1_General_CP1_CI_AS",
  "database_name": "mydatabase",
  "edition": "SQL Azure",
  "engine_edition": 5,
  "engine_edition_name": "SQL Database",
  "login_name": "sqladmin",
  "product_level": "RTM",
  "product_version": "12.0.2000.8",
  "server_name": "myserver",
  "version_string": "Microsoft SQL Azure (RTM) - 12.0.2000.8"
}
```

### Insert row

Add one row to a table and get back its new identity value, if the table has an identity column.

**Inputs**

- Table (`table`, text, required): The table's name, e.g. Customers (or sales.Customers to name the schema too). Use 'List tables' to find one.
- Schema (`schema`, text): The table's schema. Most tables are in dbo. Default: `dbo`.
- Values (`values`, object, required): Column and value pairs, e.g. {"name": "Ada", "email": "ada@example.com"}. Leave out identity and default columns.

**Outputs**

- `identity` (number)
- `rows_affected` (number)
- `table` (text)

**Example output**

```json
{
  "identity": 43,
  "rows_affected": 1,
  "table": "dbo.Customers"
}
```

### Insert rows

Add up to 1,000 rows in one go, all or nothing. A column missing from a row is stored as null.

**Inputs**

- Table (`table`, text, required): The table's name, e.g. Customers (or sales.Customers to name the schema too). Use 'List tables' to find one.
- Schema (`schema`, text): The table's schema. Most tables are in dbo. Default: `dbo`.
- Rows (`rows`, list, required): A list of rows, each a column and value object, e.g. [{"name": "Ada"}, {"name": "Grace"}].

**Outputs**

- `rows_affected` (number)
- `table` (text)

**Example output**

```json
{
  "rows_affected": 2,
  "table": "dbo.Customers"
}
```

### List columns

A table's or view's columns in order, with type, length, nullability and default.

**Inputs**

- Table (`table`, text, required): The table's name, e.g. Customers (or sales.Customers to name the schema too). Use 'List tables' to find one.
- Schema (`schema`, text): The table's schema. Most tables are in dbo. Default: `dbo`.

**Outputs**

- `columns` (list)
- `count` (number)
- `table` (text)

**Example output**

```json
{
  "columns": [
    {
      "character_maximum_length": null,
      "collation_name": null,
      "column_default": null,
      "column_name": "id",
      "data_type": "int",
      "datetime_precision": null,
      "is_nullable": false,
      "numeric_precision": 10,
      "numeric_scale": 0,
      "ordinal_position": 1
    }
  ],
  "count": 1,
  "table": "dbo.Customers"
}
```

### List databases

The databases the login can see, with state and recovery model. On Azure SQL Database this is the connected database and master only.

**Inputs**: None.

**Outputs**

- `count` (number)
- `databases` (list)

**Example output**

```json
{
  "count": 1,
  "databases": [
    {
      "collation_name": "SQL_Latin1_General_CP1_CI_AS",
      "compatibility_level": 170,
      "create_date": "2026-09-01T10:00:00",
      "database_id": 5,
      "is_read_only": false,
      "name": "mydatabase",
      "recovery_model_desc": "FULL",
      "state_desc": "ONLINE",
      "user_access_desc": "MULTI_USER"
    }
  ]
}
```

### List indexes

A table's indexes with their key and included columns, and its primary key columns.

**Inputs**

- Table (`table`, text, required): The table's name, e.g. Customers (or sales.Customers to name the schema too). Use 'List tables' to find one.
- Schema (`schema`, text): The table's schema. Most tables are in dbo. Default: `dbo`.

**Outputs**

- `count` (number)
- `indexes` (list)
- `primary_key_columns` (list)
- `table` (text)

**Example output**

```json
{
  "count": 1,
  "indexes": [
    {
      "filter_definition": null,
      "has_filter": false,
      "included_columns": [],
      "index_id": 1,
      "is_disabled": false,
      "is_primary_key": true,
      "is_unique": true,
      "is_unique_constraint": false,
      "key_columns": [
        {
          "column": "id",
          "descending": false
        }
      ],
      "name": "PK_Customers",
      "type_desc": "CLUSTERED"
    }
  ],
  "primary_key_columns": [
    "id"
  ],
  "table": "dbo.Customers"
}
```

### List schemas

The schemas in the connected database.

**Inputs**

- Include system schemas (`include_system`, true/false): Also list sys, INFORMATION_SCHEMA and the db_ roles. Default: `false`.

**Outputs**

- `count` (number)
- `schemas` (list)

**Example output**

```json
{
  "count": 1,
  "schemas": [
    {
      "catalog_name": "mydatabase",
      "schema_name": "dbo",
      "schema_owner": "dbo"
    }
  ]
}
```

### List stored procedures

The database's own stored procedures, optionally with each one's parameters.

**Inputs**

- Schema (`schema`, text): Only this schema. Leave empty for every schema.
- Include parameters (`include_parameters`, true/false): Also list each procedure's parameters and types. Default: `false`.

**Outputs**

- `count` (number)
- `procedures` (list)

**Example output**

```json
{
  "count": 1,
  "procedures": [
    {
      "create_date": "2026-09-01T10:00:00",
      "modify_date": "2026-09-01T10:00:00",
      "name": "usp_GetCustomer",
      "object_id": 581577110,
      "schema_name": "dbo",
      "type": "P",
      "type_desc": "SQL_STORED_PROCEDURE"
    }
  ]
}
```

### List tables

The tables the login can see, optionally in one schema.

**Inputs**

- Schema (`schema`, text): Only this schema. Leave empty for every schema.

**Outputs**

- `count` (number)
- `tables` (list)

**Example output**

```json
{
  "count": 1,
  "tables": [
    {
      "table_catalog": "mydatabase",
      "table_name": "Customers",
      "table_schema": "dbo",
      "table_type": "BASE TABLE"
    }
  ]
}
```

### List views

The views the login can see, optionally in one schema.

**Inputs**

- Schema (`schema`, text): Only this schema. Leave empty for every schema.

**Outputs**

- `count` (number)
- `views` (list)

**Example output**

```json
{
  "count": 1,
  "views": [
    {
      "table_catalog": "mydatabase",
      "table_name": "ActiveCustomers",
      "table_schema": "dbo",
      "table_type": "VIEW"
    }
  ]
}
```

### Run query

Run a T-SQL query and return its rows. Use %(name)s placeholders with 'Parameters' for any value that comes from data. The login's permissions decide what it can touch.

**Inputs**

- SQL query (`query`, text, required): The T-SQL to run, e.g. SELECT * FROM dbo.Customers WHERE status = %(status)s.
- Parameters (`parameters`, object): Values for each %(name)s in the SQL, e.g. {"status": "active"} for WHERE status = %(status)s. Passing values here, not pasting them into the SQL, keeps a quote in the data from changing the statement.
- Maximum rows (`max_rows`, number): Return at most this many rows. From 1 to 10000. Default: `1000`.

**Outputs**

- `columns` (list): Column names, in order
- `row_count` (number): Rows returned
- `rows` (list): The rows, each keyed by column
- `rows_affected` (number): Rows changed, when the batch changed data
- `truncated` (true/false): True when more rows existed than the limit allowed

**Example output**

```json
{
  "columns": [
    "id",
    "name",
    "email"
  ],
  "row_count": 1,
  "rows": [
    {
      "email": "ada@example.com",
      "id": 1,
      "name": "Ada Lovelace"
    }
  ],
  "rows_affected": -1,
  "truncated": false
}
```

### Run statement

Run an INSERT, UPDATE, DELETE, MERGE or DDL statement (such as CREATE VIEW) and get the number of rows it changed. Use %(name)s placeholders with 'Parameters' for values.

**Inputs**

- SQL statement (`statement`, text, required): The T-SQL to run.
- Parameters (`parameters`, object): Values for each %(name)s in the SQL, e.g. {"status": "active"} for WHERE status = %(status)s. Passing values here, not pasting them into the SQL, keeps a quote in the data from changing the statement.

**Outputs**

- `rows_affected` (number)

**Example output**

```json
{
  "rows_affected": 1
}
```

### Run stored procedure

Run a stored procedure with named arguments and return every result set it produces plus its return value.

**Inputs**

- Procedure (`procedure`, text, required): The procedure's name, e.g. usp_GetCustomer. Use 'List stored procedures' to find one.
- Schema (`schema`, text): The table's schema. Most tables are in dbo. Default: `dbo`.
- Arguments (`parameters`, object): Argument names and values, e.g. {"@customer_id": 42}. The @ is optional.
- Maximum rows (`max_rows`, number): Return at most this many rows. From 1 to 10000. Default: `1000`.

**Outputs**

- `procedure` (text)
- `result_set_count` (number)
- `result_sets` (list)
- `return_value` (number)

**Example output**

```json
{
  "procedure": "dbo.usp_GetCustomer",
  "result_set_count": 1,
  "result_sets": [
    {
      "columns": [
        "id",
        "name",
        "email"
      ],
      "row_count": 1,
      "rows": [
        {
          "email": "ada@example.com",
          "id": 1,
          "name": "Ada Lovelace"
        }
      ],
      "truncated": false
    }
  ],
  "return_value": 0
}
```

### Select rows

Read rows from a table without writing SQL: choose columns, match on values, sort and page. A limit always applies.

**Inputs**

- Table (`table`, text, required): The table's name, e.g. Customers (or sales.Customers to name the schema too). Use 'List tables' to find one.
- Schema (`schema`, text): The table's schema. Most tables are in dbo. Default: `dbo`.
- Columns (`columns`, list): Columns to return. Leave empty for all.
- Match rows where (`filters`, object): Column and value pairs every row must equal, e.g. {"status": "active"}. A list matches any of its values; null matches empty.
- Sort by column (`order_by`, text): Column to sort on. Leave empty for no order.
- Sort direction (`sort_direction`, text): Which way to sort, when a sort column is given. One of: `ASC` (Ascending), `DESC` (Descending). Default: `ASC`.
- Maximum rows (`limit`, number): Return at most this many rows. From 1 to 10000. Default: `100`.
- Skip rows (`offset`, number): Skip this many rows first, for paging. Set a sort column too, or the pages may overlap. From 0 to 100000000. Default: `0`.

**Outputs**

- `columns` (list): Column names, in order
- `row_count` (number): Rows returned
- `rows` (list): The rows, each keyed by column
- `table` (text): schema.table
- `truncated` (true/false): True when more rows existed than the limit allowed

**Example output**

```json
{
  "columns": [
    "id",
    "name",
    "email"
  ],
  "row_count": 1,
  "rows": [
    {
      "email": "ada@example.com",
      "id": 1,
      "name": "Ada Lovelace"
    }
  ],
  "table": "dbo.Customers",
  "truncated": false
}
```

### Update rows

Change columns on the rows matching the given values. At least one match is required.

**Inputs**

- Table (`table`, text, required): The table's name, e.g. Customers (or sales.Customers to name the schema too). Use 'List tables' to find one.
- Schema (`schema`, text): The table's schema. Most tables are in dbo. Default: `dbo`.
- New values (`values`, object, required): Column and value pairs to set, e.g. {"status": "inactive"}.
- Match rows where (`filters`, object, required): Column and value pairs every row must equal, e.g. {"id": 42}. Required, so the whole table can never be changed by accident.

**Outputs**

- `rows_affected` (number)
- `table` (text)

**Example output**

```json
{
  "rows_affected": 1,
  "table": "dbo.Customers"
}
```

### Upsert row

Update the row with the same key, or insert it if there is none. The key defaults to the table's primary key.

**Inputs**

- Table (`table`, text, required): The table's name, e.g. Customers (or sales.Customers to name the schema too). Use 'List tables' to find one.
- Schema (`schema`, text): The table's schema. Most tables are in dbo. Default: `dbo`.
- Values (`values`, object, required): Column and value pairs for the row, including its key, e.g. {"email": "ada@example.com", "name": "Ada"}.
- Match on columns (`key_columns`, list): The columns that identify the row. Leave empty to use the primary key.

**Outputs**

- `action` (text)
- `key_columns` (list)
- `rows_affected` (number)
- `table` (text)

**Example output**

```json
{
  "action": "UPDATE",
  "key_columns": [
    "id"
  ],
  "rows_affected": 1,
  "table": "dbo.Customers"
}
```