Advertisement
hecrus

Grraph DB Structure

Aug 28th, 2021
3,432
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 2.31 KB | None | 0 0
  1. CREATE PROCEDURE [dbo].[crud_watch_graph_getItems]
  2.     @filters CRUDFilterParameter READONLY,  
  3.     @sort sql_variant,
  4.     @direction nvarchar(8),
  5.     @page INT,
  6.     @pageSize INT,
  7.     @username nvarchar(32)
  8. AS
  9. BEGIN
  10.     -- filters...
  11.     DECLARE @text nvarchar(128)=''
  12.     SELECT @text = VALUE FROM @filters WHERE [KEY] = 'text'
  13.    
  14.     DECLARE @ids TABLE (id nvarchar(512))
  15.    
  16.     INSERT INTO @ids
  17.     SELECT TABLE_NAME
  18.     FROM INFORMATION_SCHEMA.TABLES t0
  19.     WHERE TABLE_TYPE='BASE TABLE'
  20.         AND @text<>'' AND TABLE_NAME LIKE '%'+@text+'%'
  21.        
  22.     -- SELECT 1    
  23.     SELECT t0.id id, t0.id name, '#f00' color, 'circle' TYPE, 6 dim,
  24.     (
  25.         STUFF((SELECT ', ' + COLUMN_NAME
  26.         FROM INFORMATION_SCHEMA.COLUMNS
  27.         WHERE TABLE_NAME = t0.id
  28.         ORDER BY ORDINAL_POSITION  
  29.         FOR XML PATH('')), 1, 2, '')
  30.     ) tip
  31.     FROM @ids t0
  32.    
  33.    
  34.     -- SELECT 2
  35.     SELECT COUNT(*) FROM @ids  
  36.  
  37.     -- SELECT 3
  38.     SELECT 'graph' viewType,
  39.         '{"contHeight": "1000px"
  40.        }' graphOptions
  41.     /*Select  '' Title,
  42.         '' ToolbarAdditional,
  43.         '' GroupOperationsToolbar,
  44.         '' EmptyText,
  45.         '' FastCreateLinkText, '' FastCreateDialogHeader, '' FastCreateDialogPlaceholder,
  46.         0 FastCreateSearch, 0 FastCreateTextarea,
  47.          0 HideTitleCount,
  48.          0 DisableCellTitle,
  49.          '10px' FontSize,
  50.          '{filterCode}' FilterMakeup,
  51.          1 InstantFilter,
  52.            */
  53.        
  54.     -- 4 SELECT Footer data or kanban/gantt data
  55.     SELECT t1.[TABLE] nodeFrom, t1.referenced_table nodeTo, '#00f' color FROM (
  56.    
  57.     SELECT  obj.name AS FK_NAME,
  58.     sch.name AS [schema_name],
  59.     tab1.name AS [TABLE],
  60.     col1.name AS [COLUMN],
  61.     tab2.name AS [referenced_table],
  62.     col2.name AS [referenced_column]
  63. FROM sys.foreign_key_columns fkc
  64. INNER JOIN sys.objects obj
  65.     ON obj.object_id = fkc.constraint_object_id
  66. INNER JOIN sys.TABLES tab1
  67.     ON tab1.object_id = fkc.parent_object_id
  68. INNER JOIN sys.schemas sch
  69.     ON tab1.schema_id = sch.schema_id
  70. INNER JOIN sys.COLUMNS col1
  71.     ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
  72. INNER JOIN sys.TABLES tab2
  73.     ON tab2.object_id = fkc.referenced_object_id
  74. INNER JOIN sys.COLUMNS col2
  75.     ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
  76. WHERE tab1.name IN (SELECT id FROM @ids) OR tab2.name IN (SELECT id FROM @ids)
  77.       ) t1
  78.    
  79.    
  80. END
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement