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.
6 Answers
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.
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.
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
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.
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.
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
Related