Azure data studio schema diagram?

Question

I just recently downloaded Azure Data Studio with SQL Server Express since I'm using Linux . Is there an entity-relationship diagramming feature, kind of how SQL Server Management Studio has a database diagram feature? I want to visually see the relationships with tables in a database if possible.

Hankering

Hello, you can see the following link for comparison betwenn Azure Data Studio and SSMS: Azure Data Studio - Diagrams, still doesn't have this functionality. – Teutonic

This feature will be deleted in SQL Server Management Studio 18.x – Goahead

@JDC, it appears that the designer tool was added back with 18.1. Do you have a link where Microsoft acknowledges removing this in a future version again? – Greatcoat

@jim-wooley Look here Deprecated #2121 – Salina

@David, From that thread, they acknowledged that it was coming back in 18.1 GA. See specifically github.com/MicrosoftDocs/sql-docs/issues/… – Greatcoat

Answer 1

Leaving this here for future people asking this question. While Azure Data Studio does not support this, DBeaver does and it's cross platform. https://dbeaver.io/

I had this same issue and by right clicking the dbo under your databases Schemas folder in the database explorer, you can choose "View Diagram" and it will build a view just like SQL Server Management Studio does.

Rhyolite

dbeaver.io is also a whole lot more stable than Azure Data Studio (on linux, anyway). Thanks a lot for the tip -- saved me from pulling out the rest of my hair waiting for ADS to respond! – Unnecessary

Could not get this to work to connect Azure Postgres. We only allow VNet integration, no pubic access. So yeah none of these 3rd party solutions will work. ADS doesn't require public access so it can connect without turning public access on. – Yordan

Answer 2

I don't know if you're still looking for an extension to do that. Anyhow, I've solved with the R0tenur/visualization.

This extension uses mermaid.js to generate the schema.

If you want to install (v0.7.1) it, just look at here, download the vsix file, and install it from the File -> Install Extension from VSIX Package in Azure Data Studio.

Venezuela

If you search for Schema Visualization in the Extensions section of ADS you'll also find this plugin. – Eldreeda

Unfortunately, this doesn't work with Postgres databases in Azure Data Studio. – Williamwilliams

I've downloaded it, it looks great, but it took a while to work out how to activate it. You need to right click on the db a d press Manage then go to the Schema Visualisation tab – Indulgence

Answer 3

You can generate a schema diagram with the plugin "Schema Visualization", just download the .vsix file of the last release and install it from Azure Data Studio as you see in this image

Sumatra

I haven't tried this yet but are you actually in control of the diagrams, can you add tables and position them wherever you want (like SSMS diagrams), or does it just produce it's own diagram that you can't change? – Stauffer

@Paul, yes at the moment it only produces a diagram from your schema, but you can't change it, on the other hand, the last version gives you more control over the diagram, in theory, because I couldn't get it to work 😒. – Hagiocracy

I've got a large DB. This extension ate 15 minutes of CPU then produced no output. There are some open issues about that github.com/R0tenur/visualization/issues – Englacial

It took a while to work out how to activate it. You need to right click on the db and press Manage then go to the Schema Visualisation tab – Indulgence

Does not work for PostgresDB in Azure. – Yordan

Answer 4

Unfortunately, this is not possible. There is no database schema diagram for the moment in Azure Data Studio. This feature has been requested in 2017 https://github.com/Microsoft/azuredatastudio/issues/94 and still no updates on that.

Darkle

Answer 5

I tried using "Schema Visualization" extension. Unfortunately, for some reason, it kept on throwing errors. So, I have to opt back to the native 'sys' schema, through which I generated the https://dbdiagram.io/home - specific format using the following code:

CREATE TABLE #CreateQueries
(
    QueryString NVARCHAR(MAX)
)

DECLARE @Counter INT 

DECLARE  
      @object_name SYSNAME  
    , @object_id INT  
    , @SQL NVARCHAR(MAX)  

SET @Counter=0
WHILE ( @Counter <= ( SELECT COUNT(*) FROM sys.objects WHERE type = 'U' ) )
BEGIN

    SELECT  
      @object_name =  OBJECT_SCHEMA_NAME(o.[object_id]) + '.' + OBJECT_NAME([object_id])   
    , @object_id = [object_id]  
FROM (SELECT [object_id] = OBJECT_ID('dbo.' + name, 'U') FROM sys.objects WHERE type = 'U' ORDER BY name OFFSET @Counter ROWS FETCH FIRST 1 ROWS ONLY) o

SET @SQL = ''
      
SELECT @SQL = 'Table ' + @object_name + CHAR(13) + '{' + CHAR(13) +   
        
        TRIM( CHAR(13) + CHAR(10) FROM (SELECT CHAR(13) + CHAR(10) + c.name  +  ' ' + tp.name+

        CASE   
            WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary')  
                THEN '(' + CASE WHEN c.max_length = -1   
                                THEN 'MAX'   
                                ELSE CAST(c.max_length AS VARCHAR(5))   
                            END + ')'  
            WHEN tp.name IN ('nvarchar', 'nchar')  
                THEN '(' + CASE WHEN c.max_length = -1   
                                THEN 'MAX'   
                                ELSE CAST(c.max_length / 2 AS VARCHAR(5))   
                            END + ')'  
            WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset')   
                THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'  
            WHEN tp.name = 'decimal'  
                THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'  
            ELSE ''  
        END + CHAR(13) + CHAR(10)
    FROM sys.columns c WITH(NOLOCK)  
    JOIN sys.types tp WITH(NOLOCK) ON c.user_type_id = tp.user_type_id  
    LEFT JOIN sys.check_constraints cc WITH(NOLOCK)   
         ON c.[object_id] = cc.parent_object_id   
        AND cc.parent_column_id = c.column_id  
    WHERE c.[object_id] = @object_id  
    ORDER BY c.column_id
    FOR XML PATH(''), TYPE)
    .value('.', 'NVARCHAR(MAX)') ) +  CHAR(13) + CHAR(10) + '}' + CHAR(13) + CHAR(10) +

    ISNULL( ( SELECT 'Ref: '+ @object_name + 
        '.'+ COL_NAME(f_k_c.[parent_object_id], f_k_c.[parent_column_id])+ ' < '+ 
        OBJECT_SCHEMA_NAME(f_k_c.referenced_object_id) + '.' + OBJECT_NAME(f_k_c.referenced_object_id) +
        '.'+COL_NAME(f_k_c.[referenced_object_id], f_k_c.[referenced_column_id])+CHAR(13)+CHAR(10)
        FROM sys.foreign_keys f WITH(NOLOCK)
        JOIN sys.foreign_key_columns f_k_c WITH(NOLOCK) ON
        f_k_c.constraint_object_id = f.object_id
        AND f.parent_object_id = @object_id
        FOR XML PATH(''), TYPE)
    .value('.', 'NVARCHAR(MAX)'), '')


    SET @Counter = @Counter + 1

    INSERT INTO #CreateQueries VALUES (@SQL)

  
END

SELECT * FROM #CreateQueries ORDER BY QueryString;

Till we get a proper extension, we can use this code as a work around and use 'dbdiagram.io' to do the rest for us.

Fend

Answer 6

I had this problem too. I had ssms 18 and created a diagram on linux sql server but after some days the diagram has not open. I downloaded ssms 19 preview 3 and created a new diagram and it work like a charm. Download ssms 19 : https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms-19?view=sql-server-ver16

Gae

Answer 7

I had some problems getting @sabharikarthik script working (some kind of truncation going on) but it seemed really handy. So I altered it to get it working for myself. Here it is. You need to copy/paste out of the output window, removing the Completion time: part from the end

CREATE PROC [dbo].[pDBDiagram]
@SchemaFilter VARCHAR(100)='dbo'
AS
-- Generate code to be pasted into https://dbdiagram.io/d
-- Based on https://mcmap.net/q/299521/-azure-data-studio-schema-diagram

SET NOCOUNT ON;

DECLARE @TotalObjects INT 
DECLARE @Counter INT 
DECLARE @CR VARCHAR(2) = CHAR(13) + CHAR(10)

DECLARE  
      @object_name SYSNAME  
    , @object_id INT  
    , @SQL NVARCHAR(MAX)  

SET @SchemaFilter = 'bil'

SET @Counter=0


SELECT @TotalObjects = COUNT(*) FROM sys.objects so WHERE so.type = 'U' and so.schema_id = SCHEMA_ID(@SchemaFilter)

PRINT '// Found ' + FORMAT(@TotalObjects,'0') + ' tables'

WHILE ( @Counter < @TotalObjects )

BEGIN

    SELECT  
      @object_name =  OBJECT_SCHEMA_NAME(o.[object_id]) + '.' + OBJECT_NAME([object_id])   
    , @object_id = [object_id]  
FROM (
    SELECT [object_id] = OBJECT_ID(@SchemaFilter + '.' + ob.name, 'U') 
    FROM sys.objects ob
    WHERE ob.type = 'U' 
    AND ob.schema_id = SCHEMA_ID(@SchemaFilter)
    ORDER BY ob.name OFFSET @Counter ROWS FETCH FIRST 1 ROWS ONLY) o

SET @SQL = ''
      
SELECT @SQL = 'Table ' + @object_name + CHAR(13) + '{' + CHAR(13) +   
        
        TRIM( CHAR(13) + CHAR(10) FROM (SELECT CHAR(13) + CHAR(10) + c.name  +  ' ' + tp.name+

        CASE   
            WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary')  
                THEN '(' + CASE WHEN c.max_length = -1   
                                THEN 'MAX'   
                                ELSE CAST(c.max_length AS VARCHAR(5))   
                            END + ')'  
            WHEN tp.name IN ('nvarchar', 'nchar')  
                THEN '(' + CASE WHEN c.max_length = -1   
                                THEN 'MAX'   
                                ELSE CAST(c.max_length / 2 AS VARCHAR(5))   
                            END + ')'  
            WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset')   
                THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'  
            WHEN tp.name = 'decimal'  
                THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'  
            ELSE ''  
        END + CHAR(13) + CHAR(10)
    FROM sys.columns c WITH(NOLOCK)  
    JOIN sys.types tp WITH(NOLOCK) ON c.user_type_id = tp.user_type_id  
    LEFT JOIN sys.check_constraints cc WITH(NOLOCK)   
         ON c.[object_id] = cc.parent_object_id   
        AND cc.parent_column_id = c.column_id  
    WHERE c.[object_id] = @object_id  
    ORDER BY c.column_id
    FOR XML PATH(''), TYPE)
    .value('.', 'NVARCHAR(MAX)') ) +  CHAR(13) + CHAR(10) + '}' + CHAR(13) + CHAR(10) +

    ISNULL( ( SELECT 'Ref: '+ @object_name + 
        '.'+ COL_NAME(f_k_c.[parent_object_id], f_k_c.[parent_column_id])+ ' < '+ 
        OBJECT_SCHEMA_NAME(f_k_c.referenced_object_id) + '.' + OBJECT_NAME(f_k_c.referenced_object_id) +
        '.'+COL_NAME(f_k_c.[referenced_object_id], f_k_c.[referenced_column_id])+CHAR(13)+CHAR(10)
        FROM sys.foreign_keys f WITH(NOLOCK)
        JOIN sys.foreign_key_columns f_k_c WITH(NOLOCK) ON
        f_k_c.constraint_object_id = f.object_id
        AND f.parent_object_id = @object_id
        FOR XML PATH(''), TYPE)
    .value('.', 'NVARCHAR(MAX)'), '')

    PRINT '// ' + ISNULL(@object_name,'') + ' [' + FORMAT(@Counter+1,'0') + ']'
    PRINT @SQL;

    SET @Counter = @Counter + 1

    --INSERT INTO #CreateQueries VALUES (@SQL)

  
END

Indulgence

I have a strange error: the query inside the sp returns 7, it is ok, but the variable '@TotalObjects' 'SELECT @TotalObjects = COUNT(*) FROM sys.objects so WHERE so.type = 'U' and so.schema_id = SCHEMA_ID(@SchemaFilter)' is equal 0 – Paymaster