fork download 
  1. DECLARE @EntityName NVARCHAR(128) = 'Company'; -- USER INPUT
  2. DECLARE @Sql NVARCHAR(MAX);
  3.  
  4. /* =========================
  5.    Derived Names
  6. ========================= */
  7. DECLARE @MainTable NVARCHAR(128)        = QUOTENAME(@EntityName);
  8. DECLARE @TypeTable NVARCHAR(128)        = QUOTENAME(@EntityName + 'Type');
  9. DECLARE @LinkTable NVARCHAR(128)        = QUOTENAME(@EntityName + '_' + @EntityName + 'Type');
  10.  
  11. DECLARE @SelfRelTable NVARCHAR(128)     = QUOTENAME(@EntityName + '_' + @EntityName);
  12. DECLARE @RelTypeTable NVARCHAR(128)     = QUOTENAME(@EntityName + 'RT');
  13. DECLARE @SelfRelLinkTable NVARCHAR(128) = QUOTENAME(@EntityName + '_' + @EntityName + '_' + @EntityName + 'RT');
  14.  
  15. DECLARE @EntityIdCol NVARCHAR(128)      = QUOTENAME(@EntityName + 'Id');
  16. DECLARE @EntityNameCol NVARCHAR(128)    = QUOTENAME(@EntityName + 'Name');
  17. DECLARE @EntityDescCol NVARCHAR(128)    = QUOTENAME(@EntityName + 'Description');
  18.  
  19. DECLARE @EntityTypeIdCol NVARCHAR(128)  = QUOTENAME(@EntityName + 'TypeId');
  20. DECLARE @EntityTypeNameCol NVARCHAR(128)= QUOTENAME(@EntityName + 'TypeName');
  21.  
  22. DECLARE @LinkIdCol NVARCHAR(128)        = QUOTENAME(@EntityName + '_' + @EntityName + 'TypeId');
  23.  
  24. DECLARE @SelfRelIdCol NVARCHAR(128)     = QUOTENAME(@EntityName + '_' + @EntityName + 'Id');
  25. DECLARE @Entity1IdCol NVARCHAR(128)     = QUOTENAME(@EntityName + '1Id');
  26. DECLARE @Entity2IdCol NVARCHAR(128)     = QUOTENAME(@EntityName + '2Id');
  27.  
  28. DECLARE @RTIdCol NVARCHAR(128)          = QUOTENAME(@EntityName + 'RTId');
  29. DECLARE @RTNameCol NVARCHAR(128)        = QUOTENAME(@EntityName + 'RTName');
  30.  
  31. DECLARE @SelfRelLinkIdCol NVARCHAR(128) = QUOTENAME(@EntityName + '_' + @EntityName + '_' + @EntityName + 'RTId');
  32.  
  33. /* =========================
  34.    1️⃣ Main Table
  35. ========================= */
  36. SET @Sql = '
  37. CREATE TABLE ' + @MainTable + ' (
  38.     ' + @EntityIdCol + ' BIGINT IDENTITY(1,1) PRIMARY KEY,
  39.     ' + @EntityNameCol + ' NVARCHAR(MAX),
  40.     ' + @EntityDescCol + ' NVARCHAR(MAX),
  41.     CreatedBy NVARCHAR(MAX) NULL,
  42.     CreatedAt DATETIME NULL
  43. );';
  44. EXEC sp_executesql @Sql;
  45.  
  46. /* =========================
  47.    2️⃣ Type Table
  48. ========================= */
  49. SET @Sql = '
  50. CREATE TABLE ' + @TypeTable + ' (
  51.     ' + @EntityTypeIdCol + ' BIGINT IDENTITY(1,1) PRIMARY KEY,
  52.     ' + @EntityTypeNameCol + ' NVARCHAR(MAX),
  53.     CreatedBy NVARCHAR(MAX) NULL,
  54.     CreatedAt DATETIME NULL
  55. );';
  56. EXEC sp_executesql @Sql;
  57.  
  58. /* =========================
  59.    3️⃣ Entity ↔ Type Link Table
  60. ========================= */
  61. SET @Sql = '
  62. CREATE TABLE ' + @LinkTable + ' (
  63.     ' + @LinkIdCol + ' BIGINT IDENTITY(1,1) PRIMARY KEY,
  64.     ' + @EntityIdCol + ' BIGINT,
  65.     ' + @EntityTypeIdCol + ' BIGINT,
  66.     CreatedBy NVARCHAR(MAX) NULL,
  67.     CreatedAt DATETIME NULL,
  68.  
  69.     CONSTRAINT FK_' + @EntityName + '_ET_Main
  70.         FOREIGN KEY (' + @EntityIdCol + ')
  71.         REFERENCES ' + @MainTable + '(' + @EntityIdCol + '),
  72.  
  73.     CONSTRAINT FK_' + @EntityName + '_ET_Type
  74.         FOREIGN KEY (' + @EntityTypeIdCol + ')
  75.         REFERENCES ' + @TypeTable + '(' + @EntityTypeIdCol + ')
  76. );';
  77. EXEC sp_executesql @Sql;
  78.  
  79. /* =========================
  80.    4️⃣ Self Relation Table
  81. ========================= */
  82. SET @Sql = '
  83. CREATE TABLE ' + @SelfRelTable + ' (
  84.     ' + @SelfRelIdCol + ' BIGINT IDENTITY(1,1) PRIMARY KEY,
  85.     ' + @Entity1IdCol + ' BIGINT,
  86.     ' + @Entity2IdCol + ' BIGINT,
  87.     CreatedBy NVARCHAR(MAX) NULL,
  88.     CreatedAt DATETIME NULL,
  89.  
  90.     CONSTRAINT FK_' + @EntityName + '_SR_Entity1
  91.         FOREIGN KEY (' + @Entity1IdCol + ')
  92.         REFERENCES ' + @MainTable + '(' + @EntityIdCol + '),
  93.  
  94.     CONSTRAINT FK_' + @EntityName + '_SR_Entity2
  95.         FOREIGN KEY (' + @Entity2IdCol + ')
  96.         REFERENCES ' + @MainTable + '(' + @EntityIdCol + ')
  97. );';
  98. EXEC sp_executesql @Sql;
  99.  
  100. /* =========================
  101.    5️⃣ Relationship Type Table
  102. ========================= */
  103. SET @Sql = '
  104. CREATE TABLE ' + @RelTypeTable + ' (
  105.     ' + @RTIdCol + ' BIGINT IDENTITY(1,1) PRIMARY KEY,
  106.     ' + @RTNameCol + ' NVARCHAR(MAX),
  107.     CreatedBy NVARCHAR(MAX) NULL,
  108.     CreatedAt DATETIME NULL
  109. );';
  110. EXEC sp_executesql @Sql;
  111.  
  112. /* =========================
  113.    6️⃣ Self Relation ↔ RelationshipType Link Table
  114. ========================= */
  115. SET @Sql = '
  116. CREATE TABLE ' + @SelfRelLinkTable + ' (
  117.     ' + @SelfRelLinkIdCol + ' BIGINT IDENTITY(1,1) PRIMARY KEY,
  118.     ' + @SelfRelIdCol + ' BIGINT,
  119.     ' + @RTIdCol + ' BIGINT,
  120.     CreatedBy NVARCHAR(MAX) NULL,
  121.     CreatedAt DATETIME NULL,
  122.  
  123.     CONSTRAINT FK_' + @EntityName + '_SRL_SelfRel
  124.         FOREIGN KEY (' + @SelfRelIdCol + ')
  125.         REFERENCES ' + @SelfRelTable + '(' + @SelfRelIdCol + '),
  126.  
  127.     CONSTRAINT FK_' + @EntityName + '_SRL_RT
  128.         FOREIGN KEY (' + @RTIdCol + ')
  129.         REFERENCES ' + @RelTypeTable + '(' + @RTIdCol + ')
  130. );';
  131. EXEC sp_executesql @Sql;
Success #stdin #stdout #stderr 0.01s 5312KB
comments (?)
stdin 
Standard input is empty
stdout 
Standard output is empty
stderr 
Error: near line 1: near "DECLARE": syntax error
Error: near line 2: near "DECLARE": syntax error
Error: near line 7: near "DECLARE": syntax error
Error: near line 8: near "DECLARE": syntax error
Error: near line 9: near "DECLARE": syntax error
Error: near line 11: near "DECLARE": syntax error
Error: near line 12: near "DECLARE": syntax error
Error: near line 13: near "DECLARE": syntax error
Error: near line 15: near "DECLARE": syntax error
Error: near line 16: near "DECLARE": syntax error
Error: near line 17: near "DECLARE": syntax error
Error: near line 19: near "DECLARE": syntax error
Error: near line 20: near "DECLARE": syntax error
Error: near line 22: near "DECLARE": syntax error
Error: near line 24: near "DECLARE": syntax error
Error: near line 25: near "DECLARE": syntax error
Error: near line 26: near "DECLARE": syntax error
Error: near line 28: near "DECLARE": syntax error
Error: near line 29: near "DECLARE": syntax error
Error: near line 31: near "DECLARE": syntax error
Error: near line 36: near "SET": syntax error
Error: near line 44: near "EXEC": syntax error
Error: near line 49: near "SET": syntax error
Error: near line 56: near "EXEC": syntax error
Error: near line 61: near "SET": syntax error
Error: near line 77: near "EXEC": syntax error
Error: near line 82: near "SET": syntax error
Error: near line 98: near "EXEC": syntax error
Error: near line 103: near "SET": syntax error
Error: near line 110: near "EXEC": syntax error
Error: near line 115: near "SET": syntax error
Error: near line 131: near "EXEC": syntax error
https://ideone.com/Y5lfsb
language:
SQLite (sqlite 3.27.2)
created:
14 hours ago
visibility:
public

Share or Embed source code

Discover > Sphere Engine API

The brand new service which powers Ideone!

Discover > IDE Widget

Widget for compiling and running the source code in a web browser!