Post reply

Reverse engineering data model in Azure MSSQL

  • April 8, 2019 at 6:31 pm

    #3027179

    Hi,

    I have a database that has been created in Azure MSSQL Server. It has a lot of tables with Primary/Foreign Keys.

    I'd like to create an ER diagram automatically.

    Are there tools available that can do that?

    Please advise.

    Thank you!

  • April 9, 2019 at 10:06 am

    #3055973

    You can RE Azure DBs in the latest version of SSMS:

    https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017

  • April 10, 2019 at 11:32 am

    #3089015

    There are some tools to create ER diagram automatically. For large databases, you probably want to use several ER diagrams.

    In SSMS, you can use the diagram functionality to manually generate diagrams, look below the Database Diagrams.

    There may be some extended properties defined, may be MS_Description for tables, that may help.

    select object_schema_name(ep.major_id) [Schema],
    object_name(ep.major_id) TableName, 
    c.column_id ColumnId, 
    c.name ColumnName, 
    ep.value [Description]
    from sys.extended_properties as ep
    inner join sys.sysobjects as so on so.id = ep.major_id
    left outer join sys.columns as c on c.object_id = ep.major_id and c.column_id = ep.minor_id
    where so."type" = 'U' and so.name <> 'sysdiagrams' and ep.class_desc = 'object_or_column' and ep.name = 'MS_Description'
    order by object_schema_name(ep.major_id), object_name(ep.major_id);

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply