我最近下载了Azure Data Studio以及SQL Server Express,因为我正在使用Linux。是否有一种实体关系图功能,就像SQL Server Management Studio具有数据库图表功能一样?如果可能的话,我想在视觉上看到数据库中表之间的关系。
我最近下载了Azure Data Studio以及SQL Server Express,因为我正在使用Linux。是否有一种实体关系图功能,就像SQL Server Management Studio具有数据库图表功能一样?如果可能的话,我想在视觉上看到数据库中表之间的关系。
为了方便未来提问的人,我在此留下这篇文章。虽然Azure Data Studio不支持此功能,但DBeaver可以,并且它是跨平台的。https://dbeaver.io/
我也遇到了这个问题,通过在数据库资源管理器中的数据库模式文件夹下右键单击dbo,您可以选择"查看图表",这将构建一个与SQL Server Management Studio相同的视图。
我不知道你是否还在寻找能够实现此功能的插件。无论如何,我已经使用R0tenur/visualization解决了这个问题。
此扩展程序使用mermaid.js生成模式图。
如果你想安装它(版本v0.7.1),只需看一下这里,下载vsix文件,并从Azure Data Studio的File -> Install Extension from VSIX Package安装即可。
很遗憾,目前Azure Data Studio中没有数据库模式图。这个功能在2017年就已经被要求了 https://github.com/Microsoft/azuredatastudio/issues/94 ,但至今仍未更新。
我尝试使用“模式可视化”扩展程序。不幸的是,由于某些原因,它一直在抛出错误。因此,我必须返回到本地的“sys”模式,通过该模式使用以下代码生成了https://dbdiagram.io/home - 特定格式:
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;
在我们获得适当的扩展之前,我们可以使用此代码作为解决方法,并使用'dbdiagram.io'来完成其余工作。
我也遇到了这个问题。我使用了 ssms 18 并在 linux SQL Server 上创建了一个图表,但几天后就无法打开该图表。我下载了 ssms 19 预览版 3 并创建了一个新的图表,它非常顺利地工作了。
下载 ssms 19: https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms-19?view=sql-server-ver16
我在使用@sabharikarthik的脚本时遇到了一些问题(似乎发生了某种截断),但它看起来非常方便。因此,我对其进行了修改以使其适用于自己。这是它的代码。您需要从输出窗口中复制/粘贴,将结尾处的完成时间:部分删除。
CREATE PROC [dbo].[pDBDiagram]
@SchemaFilter VARCHAR(100)='dbo'
AS
-- Generate code to be pasted into https://dbdiagram.io/d
-- Based on https://dev59.com/dlQJ5IYBdhLWcg3wtYHq#73518800
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