A database diagram is a graphical representation of the structure of the database and relations between database objects. In dbForge Query Builder for SQL Server, you can build queries, add or remove tables from a query, work with subqueries, specify sort order, add columns to output data, change query types, set aliases for tables and columns, control layout diagram by aligning tables and joins.
You can start building a query in one of the following ways:
You can add multiple tables to the diagram at once by holding Ctrl and selecting the tables in Database Explorer.
If there are foreign keys between tables, then JOINs between them will be created on the diagram automatically and displayed on the Joins tab of the Tabbed Editor. By default, the SELECT statement opens. If you need to change the type of the statement, you can do this using the Change Type functionality. For more information about query types, see How to work with different query types.
After the tables are added, you can select the columns visually. Once done, they automatically appear on the Selection tab of the Tabbed Editor. For more information about how to create a SELECT statement, see How to design conditions for the SELECT clause. Then, you can specify filtering conditions, group, or order results, preview the query text, and run the query.
In addition, using the drag-and-drop feature, diagram shapes can be moved, resized, or aligned to the diagram grid.
The guide covers the following topics:
When you create a query, you retrieve data from a table or other objects structured like tables - views and certain user-defined functions.
To add tables to the diagram, use one of the following ways:
In Database Explorer, select a required table, tables or table-valued objects and drag them onto the diagram.
In Database Explorer, right-click the selected tables and select Send to > Query Builder.
Tables and views are automatically added to the query when you reference them in the statement in the SQL editor.
Database objects you drag onto the diagram and Query Builder document you use should be connected to the same server.
You can remove a table or any table-valued object from the query.
To remove the table from the query, use one of the following ways:
On the diagram, right-click the table(s) you want to delete and select Remove from Diagram. You can remove multiple objects at a time. For this, select the objects by holding the Ctrl and repeat the step.
Remove all references to the object in the SQL editor.
When you remove a table or table-valued object, Query Builder automatically removes JOINs that involve that table or table-valued object and removes references to the object’s columns in the SQL editor.
Removing a table or table-valued object does not delete anything from the database, it only removes it from the current query.
You can add a column to the query, sort it, search the contents of the column, or summarize its contents.
To add an individual column, use one of the following ways:
On the diagram, select the checkbox next to the column that you want to add.
In the Tabbed Editor, click the empty row in the Column grid column, select a table and a column name from the drop-down list. To open the Tabbed Editor, on the Query toolbar, click Show Tabbed Editor or on the Query menu, and select Show Tabbed Editor.
To add all columns for one table or table-valued object, do either of the following:
To add all columns for all tables and table-structured objects
1. On the diagram, select all tables.
2. Right-click any table header and select Select all columns on the shortcut menu.
Make sure that no JOIN lines on the diagram are selected.
Each shape may be aligned to the grid. To align a shape to the grid, select the table on the diagram and click Align > to Grid on the Layout toolbar. Alternatively, you can also use corresponding commands of the Align submenu of the Layout menu.
Several shapes can be aligned in a column or in a row.
Query Builder for SQL Server allows you to layout a diagram automatically or manually. To resize the diagram automatically, do either of the following:
To change the shape size, select it and drag the square resize markers at the shape sides and corners. To fit the shape size to the grid, select it and click Size to Grid on the Layout toolbar. Alternatively, on the Layout menu, select Make Same Size > Size to Grid. Shapes have minimum and maximum height and width. The minimum width and height are constant. The maximum width depends on the length of the name of the longest column in a table shape. Maximum height depends on the table shape column count.
To fit the same size for multiple shapes, select the shapes and click Make Same Width or Make Same Height on the Layout toolbar. Alternatively, you can use the corresponding commands of the Make Same Size submenu of the Layout menu. When using this feature, the size of the selected shapes will be aligned with the size of the primary selection.
To change the shape order, do either of the following:
A primary selection is one of the selected shapes. Its resize markers are blue, while resize markers of other selected shapes are black. If a selected shape cannot have the same size as the primary selection, its size will be changed to become as close to the primary selection size as possible.
The diagram provides flexible control over spacing between shapes on it. You can proportionally increase and decrease the horizontal and vertical spacing between the shapes, make it equal or remove it.
To work with spacing, use the Layout toolbar.
When increasing or decreasing spacing or making it equal, spacing is considered as the distance between centers of shapes. But when removing spacing, it is considered as the distance between shape borders.
When you remove, for example, vertical spacing, shapes will be located in the following way: the top of the second shape will be located at the same height as the bottom of the highest shape, the top of the third one will be located at the same height as the bottom of the second shape and so on.