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.

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.

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.

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

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.

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.

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

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://stackoverflow.com/a/73518800/1690193

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