Categories
SQL Server

SQL Join

 83 total views

Categories
Data Neo4j SQL Server

MS SQL Server Database Logical Entity-Relationship Model in Neo4j

In Neo4j 4.0 onward, we can have multi database serving in the same instance of neo4j.

Sometimes, we need to store the E-R metadata of SQL Server in Neo4j database.

So, with neo4j, we can create a separate metadata database for a given SQL Server database, and extract the metadata of the Entities and Attributes from SQL Server and load it into Neo4j as a reference.


USE < database > 
go 

/*extract all tables */

SELECT
   'MERGE (' + TABLE_NAME + ':' + TABLE_NAME + ');' 
FROM
   INFORMATION_SCHEMA.TABLES Tab 
WHERE
   TABLE_TYPE = 'BASE TABLE' 
ORDER BY
   table_name;
go 


/* extract all releationships */ 

SELECT
   'MATCH (' + OBJECT_NAME(referenced_object_id) + ':table{table_name:"' + OBJECT_NAME(referenced_object_id) + '"}),(' + OBJECT_NAME(parent_object_id) + ':table{table_name:"' + OBJECT_NAME(parent_object_id) + '"}) MERGE (' + OBJECT_NAME(referenced_object_id) + ')-[r:' + OBJECT_NAME(constraint_object_id) + ']->(' + OBJECT_NAME(parent_object_id) + ');' 
FROM
   sys.foreign_key_columns 
ORDER BY
   referenced_object_id;

For the demo, I used “AdventureWorks” sample database from SQL Server, and executed the above queries in SQL Server Management Studio. Make sure, you have selected “Results to Text” in SSMS for easy copying of the results.

Tables

Relationships

Copy the results and execute it in Neo4j database.

Note:

  1. The script will also work in Neo4j 3.x.
  2. Some of the relationships from SQL Server is larger than 25 characters. Creating those relationships in neo4j will fail. In my next release of the code, I will generate an unique relationship names.

 166 total views

Categories
Data RDBMS SQL Server

SQL Server Data Conversion