Problem
Trying to visual a database model is sometimes quite difficult. When the model is small it is pretty easy to have an idea what tables reference other tables. But at the data model gets larger it is often difficult to see exactly how the tables relate. In this tip I show you how you can use the built-in SQL Server database diagram tool. I go through some of them messages you may get when getting started and then look at various tasks that you would perform using this
Solution
- You can use only tables in SQL Server database diagrams. No other SQL Server objects are allowed to be accessed from the diagram editor.
- Tables in the database diagram are not independent. Any modification in tables through this tool will directly affect the table architecture in the database.
- Also if you modify any table outside the diagram editor, the changes will automatically be updated in any saved diagrams.
- Any operation that may be implemented on a table by opening it in table designer of SSMS can also be implemented through using database diagrams. And such updates would be reflected in the tables architecture. Examples of such tasks are index creation, constraints and relationships.
- Users that are owner of a database or member of the db_owner database role can view all of the diagrams. Other users can view only their own diagrams and they can create new diagrams with certain limitation according to permissions they have in the database
- There is no undo or redo facility provided in SSMS diagram editor.
- Currently
there is no method provided by Microsoft for migration of SQL Server
database diagrams from one instance to another or from one database to
another. However you may copy diagrams to some other file format and
save it as
backup . - Diagrams can directly be printed using the print option in the file menu of SSMS. You can use the arrange tables and page break options in the diagram editor for better alignment of diagrams on a page for printing..
- You can change the owner of SQL Server database diagram just like any other SQL Server object. You may have to perform such operations if the owner of a diagram is deleted for any reason.
- You may change NULL values setting or data type of any column through database diagrams.
- After an upgrade of a database, database diagrams are usable in the new version.
- It will not be mentioned throughout this tutorial, but along with accessing options through right clicks, you may also access these through the Database Diagrams menu in SSMS.
Where database diagrams reside in SSMS?
SQL Server database diagrams reside in the first folder under any database in SSMS as shown below.
Before
discussing features of SQL Server database diagrams it would be better
to clarify a couple of messages that you may face while working with SQL
Server database diagrams.
Confirmation message while starting with database diagrams in SSMS
The first time you click on the Database Diagrams folder of any user database in SSMS, you may get the following dialog.
It
is not an error, but just a permission seeking dialog to create some
system stored procedures that are necessary to work with database
diagrams. If you choose No then no procedures will be created and you
will not be able to work with SQL Server database diagrams. If you
choose Yes then the following system stored procedures would be created
in your database and you will be able to work with SQL Server database
diagrams.
- sp_alterdiagram
- sp_dropdiagram
- sp_creatediagram
- sp_renamediagram
- sp_helpdiagramdefinition
- sp_helpdigarms
- sp_upgradediagrams
Message while starting with database diagrams of a attached/restored database
If
you have just attached/restored a database from another SQL Server
instance. While accessing its database diagrams folder, you may get the
following message
It
is an informational message and would prevent you from working on
database diagrams. The message says that your database has no valid
owner currently. Reason for this error is that database owner on a
previous SQL Server instance does not exist here. To overcome this error
just add the valid database owner through T-SQL or SSMS. Also verify
the compatibility level of your database is correct for your SQL Server
instance.
Warning while starting with database diagrams in SSMS through non db_owner user
If
you are not currently logged in as member of the db_owner database
role, then while creating a new database diagram you may get following
warning.
This warning, that several objects may not be accessible is due to lack of permissions.
So
while working with database diagrams you will be able to add only
tables for which you have permissions. And if you lack to DDL
permissions in a database then your changes for DDL operations may not
be saved. Although you may start working with database diagram after
this warning there may be problems due to restrictions and the full
features of SQL Server database diagrams may not be exploited if you are
not database owner or member of the db_owner database role.
Now
it is time to go through some practical uses of SQL Server database
diagrams. Several options used in SSMS for database diagrams are simple,
self explanatory and accessible with one click. However it is
reasonable to go through some practical tasks to reveal the power of
database diagrams in SSMS. We will be using the AdventureWorks database
for this purpose.
Creating new SQL Server database diagram in SSMS
To create a new SQL Server database diagram
- Go to Database Diagrams folder
- Right click on the folder
- Click on New Database Diagram
A
new pane will appear with all tables in a frame for selection in a new
diagram. Select single or multiple tables that are required to be added
in the diagram and click the "Add" button.
The
selected tables will be added to the diagram editor by clicking Add
button. After selection and addition is finished click the "Close"
button to close the frame.
Add existing table to SQL Server database diagrams in SSMS
At
any point we have the option to add an existing table or create a new
table in SQL Server database diagrams. For example we may add the
existing table HumanResources.Shift to our database diagram in the following way. Right click anywhere in SSMS database diagram editor and choose option "Add Table"
'
A
frame with list of accessible user tables will appear and you may
choose the required table or tables. Close the tables frame and you will
have the selected tables in the diagram editor. As in our case we
choose HumanResources.Shift.
Creating and Adding new table to SQL Server database diagrams in SSMS
A
powerful feature of SQL Server database diagrams is that you can create
a new table using database diagrams. You can create a table in the
diagram editor, work on it and as you save the diagram, the table will
be created in your database.
Here we will create a new table named lib_groups and note that this table will also be created in the database.
- Right click anywhere in diagram editor
- Choose option "New Table"
- A frame will appear for table name, provide the name for the new table as lib_groups
- A new table will be created in diagram editor and we can add columns for the new table here
Currently
this table exists in diagram editor and you will not find it listed
outside the diagram editor. Once the diagram is saved the table will be
created and will be accessible for all operations.
Create Primary Key through SQL Server database diagrams in SSMS
SQL
Server database diagrams provide you the capability to create and
manage keys on your tables. Let us create primary key on our newly
created table Lib_Groups.
Right click on column record on which the primary key is required to be
created. Choose option "Set Primary Key" as shown in diagram.
Now the primary key has been created on GroupCode through database diagrams in SSMS.
Create unique key constraint through database diagrams in SSMS
To
progress with our tasks in our tutorial for working with database
diagrams, we are required to create unique key constraint on column GroupName in table lib_Groups. Right click on required column in database diagram and choose option "Indexes/Keys" as shown below.
A
frame will appear same as that in SSMS table designer. Add new key
configuration parameters for the key in the same way as in case of table
designer and click "Close". Now our table lib_Groups has primary and unique keys.
Create relations between tables through database diagrams in SSMS
Like
table designer in SSMS you can create all types of relations among
tables through database diagrams. Right click on a table where a foreign
key will reside. In our case, to ensure valid and uniform group names
in department table, we have to create a primary and foreign key
relationship among lib_groups and department tables respectively. So right click on Department table and click on "Relationships..."
A
relationships frame will appear same as in the case of SSMS table
designer. There you can configure parameters and relationships.
Is there any change script available?
As
part of best practices, most DBAs save every script that is used for
DDL operations in their databases. SSMS also provides an option to
create scripts based on the steps you take through the GUI. See this
tip for more information -Script for the changes carried out through SSMS GUI. Luckily you also have an option to get a script for changes performed through the diagram editor.
Right
click on a table for which a change script is required to be generated.
The last option in the menu is to generate a change script for DDL
operations on the table. This option will only be enabled if there are
any changes. Click the menu item and a dialog box will pop-up to save
the script.
Get more detailed view of tables
In
default format, database diagrams do not provide any information other
than names for columns of tables. We can not see the data types or NULL
options of columns in these tables. To further utilize many features of
SQL Server database diagrams, we are required to have detailed
information about the columns of each table.
To
get detailed information displayed, select all or required tables in
diagram editor. Then right click on any selected table and choose
standard view as shown in following image
Now you can see more column information in all tables. In this menu there is also an option for a customized table view.
Removing a table from SSMS database diagram editor or from database
Using
the delete button on any number of selected tables will just remove the
tables from the diagram. However you can delete any table permanently
from the database along with removing it from the diagram. For this task
you have to right click on the and select "Delete Tables from
Database".
If you choose to delete tables from a database then a confirmation dialog will appear for confirmation of the delete operation.
Deleting a relationship in SSMS database diagram editor
The
delete button will not work for deleting relationships in diagram
editor. Relationships may be deleted by right clicking on the relation
or you can select a relationship and then delete it through Database
Diagrams menu.
A
confirmation dialog will appear to confirm the delete operation for the
relationship. To delete multiple relations at one time you may select
multiple and use either the Database Diagrams menu or right click on any
of the selected relationships to perform the delete operation.
A little formatting in SSMS database diagrams editor
As
more tables will be added, these may not be arranged for easy viewing.
To change the arrangement of the diagrams you can right click in the
database diagram editor or use the Database Diagrams menu of SSMS as
shown below.
1. Add text to diagrams
To
make the diagrams more readable you can add text. To add text in your
diagram, right click anywhere in the diagram and choose "New Text
Annotation". A text box will appear, you can write text and to format
the text, right click inside the text box and select the formatting
options.
2. Select all objects in diagram editor
When
you right click and bring up the menu you have the option to select all
added tables and any text that is present in the database diagram
editor. The same task can be achieved by using Cntrl+A or by dragging a
clicked mouse.
3. Show and hide Relationship Labels for database diagrams in SSMS
Relations
are shown among tables through bars/pipes by default. However if it is
required to also display the name of the relationship key among tables
in a text form, then choose "Show Relationship Labels". It will display
names of all present relationships among the tables.
4. To arrange the tables in diagram editor
Right click anywhere in the diagram editor and select "Arrange Tables". All tables will get aligned through this option.
5. Zoom in or out
By
right clicking anywhere in the diagram editor, you can get the Zoom
option. To analyze the diagrams in any position, you can make use of the
zoom option.
6. Keep track of page breaks
SQL
Server database diagrams would rapidly consume multiplie pages. So it
is better to keep track of page boundaries through page breaks. Enable
the page break view by right clicking anywhere in the diagram editor and
choose this option. Similarly you can turn off the page break view by
again clicking on same menu option.
7. Recalculate page breaks
With
the addition of more tables your database diagram may span many pages.
Before printing the diagram it may be appropriate to recalculate page
breaks, so that printing may be performed optimally. Use this option to
assist with setting up your diagram for printing.
8. Copy Diagram to Clipboard
For
further customization, editing or just for backup, it may be required
to save the diagram to some other file format. For such purposes use the
menu item to copy and paste the diagram outside the diagram editor.
Along with this menu option you can copy any number of selected tables
from one diagram editor and paste it to other diagram. To do this you
have to use Cntrl+C for copy, because the copy option is not provided by
using your mouse and then you can paste it into another diagram.
Next Steps
Once a diagram has been created, save it and follow these next steps.
- As SQL Server database diagrams are very powerful and require the user to be a member of the db_owner role, development servers are always the best option to work with database diagrams. Due to permissions issues, database diagrams are not suitable for production servers.
- For a better view of a diagram you can switch to the full screen view of SSMS by pressing Shift+Alt+Enter keys. Or you may access the full screen option using the menus in SSMS.
- You may rename a diagram by right clicking on it in Database Diagrams folder.
- Almost all features of SSMS database diagrams are same for SSMS 2005 and SSMS 2008. Even most of these features are applicable for database diagrams in Enterprise Manager
- Click here to read about changing owner of database.
- Click here to read about changing compatibility level of a database.
Source Collected from MSSQLTIPS.COM
No comments :
Post a Comment