Script to create a MSSQL HumanReadable database that maps Espaces to Schemas

Script to create a MSSQL HumanReadable database that maps Espaces to Schemas

  
Thought some of you might be able to pull some value from this.

Feedback welcome.
-----------------------------------------------------------------------------------

CREATE DATABASE HumanReadable
GO
 
CREATE SCHEMA HumanReadable AUTHORIZATION dbo
GO
 
CREATE VIEW HumanReadable.vSchema
AS
SELECT
'[' + NAME + ']' As Name
FROM
dbo.ossys_Espace
WHERE
(NAME NOT LIKE '%(deleted%')
GO
 
CREATE VIEW HumanReadable.vTableMappings
AS
SELECT 
'[' + es.NAME + '].[' +  en.NAME + ']' AS ViewName,
en.PHYSICAL_TABLE_NAME AS TableName
FROM 
ossys_Entity en JOIN ossys_Espace es
ON en.ESPACE_ID = es.ID
WHERE
(en.NAME NOT LIKE '%(deleted%')
AND (es.NAME NOT LIKE '%(deleted%')
GO
 
CREATE VIEW HumanReadable.vTables
AS
SELECT 
s.name as SchemaName,
'Outsystems.dbo.[' + t.name + ']' as TableName,
'[' + s.name + '].[' + t.name + ']' AS Fullname
FROM
HumanReadable.sys.views t JOIN HumanReadable.sys.schemas s
ON t.schema_id = s.schema_id
GO
 
CREATE PROC HumanReadable.[Schema_Import]
AS
BEGIN
DECLARE @Schema AS VARCHAR(200)
DECLARE @SQL AS VARCHAR(200)
DECLARE @Schemas AS TABLE(Name VARCHAR(200))
 
INSERT INTO @Schemas
SELECT Name from Outsystems.HumanReadable.vSchema WHERE Name NOT IN (SELECT '[' + Name + ']' FROM HumanReadable.sys.schemas)
 
WHILE EXISTS(SELECT TOP 1 * FROM @Schemas)
BEGIN
SELECT TOP 1 @Schema = Name FROM @Schemas
SET @SQL = 'USE HumanReadable; EXEC sp_executesql N''CREATE SCHEMA ' + @Schema +  ' AUTHORIZATION dbo'''
EXEC (@SQL)
DELETE FROM @Schemas WHERE Name = @Schema
END
END
GO
 
CREATE PROC HumanReadable.[Table_Import]
AS 
BEGIN
DECLARE @ViewName AS VARCHAR(200)
DECLARE @TableName AS VARCHAR(200)
DECLARE @SQL AS VARCHAR(200)
DECLARE @Tables AS TABLE(ViewName VARCHAR(200), TableName VARCHAR(200))
 
INSERT INTO @Tables
SELECT ViewName, TableName from HumanReadable.vTableMappings WHERE ViewName NOT IN (SELECT Fullname FROM vTables)
 
WHILE EXISTS(SELECT TOP 1 * FROM @Tables)
BEGIN
SELECT TOP 1 @ViewName = ViewName, @TableName = TableName FROM @Tables
SET @SQL = 'USE HumanReadable; EXEC sp_executesql N''CREATE VIEW ' + @ViewName + ' AS SELECT * FROM Outsystems.dbo.' + @TableName + ''';'
EXEC  (@SQL)
DELETE FROM @Tables WHERE ViewName = @ViewName
END
END
GO
 
CREATE PROC [HumanReadable].[View_Remove_All]
AS
BEGIN
DECLARE 
@sql VARCHAR(MAX) = '', 
@crlf VARCHAR(2) = CHAR(13) + CHAR(10);
 
SELECT @sql = @sql + 'USE HumanReadable; EXEC sp_executesql N''DROP VIEW ' + QUOTENAME(s.name) + '.' + QUOTENAME(v.name) +';''' + @crlf
FROM   
HumanReadable.sys.views v JOIN HumanReadable.sys.schemas s 
ON v.schema_id = s.schema_id
 
PRINT @sql;
EXEC(@sql);
END
GO
 
EXEC [HumanReadable].[View_Remove_All]
 
EXEC HumanReadable.Schema_Import
 
EXEC HumanReadable.[Table_Import]

Hello

what are you trying to achieve?