While there are a lot of tools out there for data modeling, there is one option that is free if you have Visual Studio.  It’s called the Entity Data Model Designer.  It works well if you need a quick-and-dirty way to create a logical model of tables and have the DDL automatically created to build the physical tables in one step.  Or if you want to create a logical model from existing physical tables.

This solution may also solve your needs if you have been using Visio for your data modeling.  With Visio 2010 it’s possible to extract a database schema from an existing SQL Server database (“reverse engineer”) as well as a way to build CREATE TABLE scripts from your resulting diagram (via the workaround Create SQL Server scripts with Visio 2010).  But with Visio 2013 these options are gone (see the Deprecations section at UML and Database diagrams in the new Visio).

For the steps below I am using Visual Studio 2012, but other versions should be similar.  The purpose of the Entity Data Model Designer is to allow you to build logical tables from scratch, then create the DDL that can be run in SQL Server to build the physical tables.  Or, you can generate the logical tables from an existing SQL Server database, modify them, and update the physical tables.  One other feature is that it creates the .NET code to interact with the tables if you are building an application (hence the template name “ADO.NET Entity Data Model”).  In our case, we do not need that so well skip it.

When installing Visual Studio 2012, make sure to choose to install either Microsoft SQL Server Data Tools or Microsoft Web Developer Tools in order to get the Entity Data Model Designer (info).

1. Open Visual Studio
2. Create a project or open an existing project (must be Visual Basic, Visual C# project, or Console Application)
3. Right-click the project and choose Add -> New Item…
4. Under Visual C# Items select “Data”
5. Select the template “ADO.NET Entity Data Model”
6. Give it a name and click “Add”
7. Select “Generate from database” or “Empty model”
8. If “Generate from database” selected enter connection info, choose the database objects and done!

The model is stored as a “.edmx” file.

When you complete the model, right-click and choose “Validate” to make sure there are no errors.

If you want to create the DDL, right-click and choose “Generate Database from Model…”.

If there were changes to the physical tables after you created the model, right-click and choose “Update Model from Database…”.

If this solution is not to your liking, the other free option is to use the built-in Database Diagram tool in SSMS (see Getting started with SQL Server database diagrams and video Creating a Database Diagram in SSMS – SQL School Video).

If you don’t mind paying for a tool, some of the more popular one’s are: CA ERwin, ER/Studio, SAP Sybase PowerDesigner, DeZign for Databases, and dbForge Studio.

More info:

ADO.NET Entity Data Model Designer

Creating ADO.NET Entity Data Model in VS 2012

Introduction to Model First development using Entity Framework

Video Creating an Entity Data Model from a Database