Creating and changing objects

Templates for new objects

The + on a group in the explorer, or right-clicking a database, opens a commented template in a new tab: for views, procedures, table-valued and scalar-valued functions, aggregate functions (CLR) and every Service Broker object.

The template contains placeholders such as NewProcedure and the currently selected schema. Adjust the names and content, then run it with F5. Afterwards choose Refresh in the explorer so the new object appears.

The table designer

Tables get a designer instead of a template: New table in the database menu, or Edit table in a table's context menu.

  • At the top Schema and Table name.
  • Per column: Name, Data type (e.g. nvarchar(50)), whether NULL is allowed, a default value (e.g. 0 or N''), primary key and identity.
  • Add column appends a row. When editing, columns about to be removed are listed under Will be deleted.
  • SQL preview shows the script NovaSql will run: CREATE TABLE for a new table, ALTER TABLE with individual steps for changes.

Create or Apply runs the script. Reading the preview first is worth it, especially when changing tables that hold data.

Changing views, procedures and functions

In the context menu, Edit opens the definition as an editable script in a new tab. Adjust it and run it with F5. Show opens the same definition read-only.

Renaming

Rename in a table's context menu gives it a new name. Views, procedures and functions that use the table are not adjusted. For that the editor has Rename / Refactor (Shift+F2): it finds every definition containing the old name and replaces it there, see The query editor.

Moving to another schema

Move to schema (tables, views, procedures, functions) asks for the target schema and moves the object with ALTER SCHEMA … TRANSFER. Permissions granted directly on the object are lost in the process; that is how SQL Server behaves.

Deleting

Drop table asks for confirmation and deletes the table including its data. This cannot be undone. Service Broker objects can be deleted the same way.

Tip: before changing a shared database, take a snapshot. Then you can trace later what changed.