CODEWITHSUNDEEP
sql-serverdatabasereverse-engineeringerd
Ceesz
Ceesz

Reputation: 86

Reverse engineer MS SQL database to ERD

How can I create an ERD from an existing database in Microsoft SQL Server 2014?

The ERD needs to resemble: enter image description here

Upvotes: 3

Views: 22530

Answers (5)

Manngo
Manngo

Reputation: 16399

The only solution I have found is SQLEditor from https://www.malcolmhardie.com/index.html. However, it runs only on the Macintosh. At this point the developer has no interest in porting this to Windows.

For me, that’s fine, as I do all of my desktop work on Mac. I have successfully developed diagrams from existing databases on a number of DBMSs, including Microsoft SQL Server on the network.

I have tried for a long time to find something on Windows which shows relationships between columns and not just tables.

The only advice I can offer is get a Mac and get this program. Even including the cost of a Mac, it’s worth it rather than hiring someone to do it for you or navigating through a database blind.

Upvotes: 0

Francesco Mantovani
Francesco Mantovani

Reputation: 12357

With this query you can have an overview of the columns that have the same name and same datatype.

WITH ColumnCount AS(
    SELECT s.name AS SchemaName,
           t.name AS TableName,
           c.name AS ColumnName,
           ct.[name] AS DataType,
           c.max_length,
           c.precision,
           c.scale,
           COUNT(c.column_id) OVER (PARTITION BY c.[name], ct.[name], c.max_length, c.precision, c.scale) AS Duplicates
    FROM sys.schemas s
         JOIN sys.tables t ON s.schema_id = t.schema_id
         JOIN sys.columns c ON t.object_id = c.object_id
         JOIN sys.types ct ON c.user_type_id = ct.user_type_id)
SELECT *
FROM ColumnCount CC
WHERE CC.Duplicates > 1
ORDER BY CC.ColumnName,
         CC.SchemaName,
         CC.TableName;

When you don't have constraint the closest thing to Reverse Engineer is trying to backtrack the development of the database, what the developers where thinking while developing the structure of the database.

  • Hopefully the developers have used the same column name.
  • Hopefully the developers have used the same datatype (and skipped implicit conversions).

Is a query based on assumptions but it worked for me.

I look forward to create a Power BI dashboard with chord chard on my spare time. I will update when is done.

Upvotes: 0

sixtytrees
sixtytrees

Reputation: 1233

Check this discussion.
Effectively there is no reliable tool to reverse relationship between tables. SQL Power Architect picks up relationship somewhat better than other tools.

Upvotes: 0

user7529748
user7529748

Reputation: 11

The tools listed require a database to be configured with appropriate primary and foreign key constraints. If you've worked in enterprise you realize this isn't normally the case.

Search for Sql Caddy. It is a very useful tool for identifying relationships by analyzing sql queries that run against your database and finding the relationships based on joins within those queries rather than key constraints in the database.

Upvotes: -1

Kritner
Kritner

Reputation: 13765

The easiest way to do so, would be from SQL Server Management Studio (SSMS).

Go to Database -> Database Diagrams -> Right click -> Add new diagram -> Add the desired tables for your ERD. Given your tables actually have relationships defined between them the relationships will be drawn as well.

It should be noted, this will be a very basic looking ERD, and might not be quite to the standard of what you would expect out of an ERD.

Upvotes: 11

Related Questions