Skip to content

Instantly share code, notes, and snippets.

@xagronaut
Last active May 2, 2016 12:56
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save xagronaut/ecf4a4be511a4aaf1080 to your computer and use it in GitHub Desktop.
Save xagronaut/ecf4a4be511a4aaf1080 to your computer and use it in GitHub Desktop.
LINQPad Scripts to create CYPHER queries from database metadata extracted from Microsoft SQL Server. They should be executed in the order indicated at the beginning of the filename. You'll need to point to your own database connection in LINQPad. Make sure to check "Include System Views and SPs" in your connection.
<Query Kind="Statements">
</Query>
string.Format("// Add database schemas\nUNWIND [\n\t{0}\n] AS item" + @"
MERGE (db:Database {{ name: item[0] }})
MERGE (sc:DbSchema {{ name: item[1] }})
MERGE (ow:DbAccount {{ name: item[2] }})
MERGE (db)-[:CONTAINS]->(sc)
MERGE (ow)-[:OWNS]->(sc)
RETURN DISTINCT db, ow, sc;
",
string.Join(",\n\t",
INFORMATION_SCHEMA.SCHEMATA
.Where (schemata => schemata.SCHEMA_OWNER == "dbo")
.OrderBy (schemata => schemata.SCHEMA_NAME)
.Select (schemata => string.Format(@"[""{0}"", ""{1}"", ""{2}""]",
schemata.CATALOG_NAME, schemata.SCHEMA_NAME, schemata.SCHEMA_OWNER )
)
.ToList()
)).Dump();
<Query Kind="Statements">
</Query>
var index = 0;
var itemsPerGroup = 200;
var statements =
INFORMATION_SCHEMA.TABLES
.Where(table => table.TABLE_TYPE == "BASE TABLE")
.OrderBy(table => table.TABLE_SCHEMA)
.ThenBy(table => table.TABLE_NAME)
.ToList() // jump from IQueryable<T> to IEnumerable<T>
.GroupBy(table => index++ / itemsPerGroup) // integer division will cause grouping
.Select(tableGroup => string.Format(
"// Add database tables\nUNWIND [\n\t{0}\n] as item " + @"
MATCH (db:Database {{ name: item[0] }})
MATCH (sc:DbSchema {{ name: item[1] }})
MERGE (tbl:DbTable {{ name: item[2], schemaName: item[1] }})
MERGE (db)-[:CONTAINS]->(tbl)
MERGE (sc)-[:CONTAINS]->(tbl)
RETURN DISTINCT db, sc, tbl;
",
string.Join(",\n\t", tableGroup
.Select(table => string.Format("[\"{0}\", \"{1}\", \"{2}\"]",
table.TABLE_CATALOG, table.TABLE_SCHEMA, table.TABLE_NAME)
)
))
);
foreach (var statement in statements)
{
Util.RawHtml("<pre>\n" + statement + "\n\n</pre>\n\n").Dump();
}
<Query Kind="Statements">
</Query>
var index = 0;
var itemsPerGroup = 200;
var statements =
INFORMATION_SCHEMA.TABLES
.Where(table => table.TABLE_TYPE == "VIEW")
.OrderBy(table => table.TABLE_SCHEMA)
.ThenBy(table => table.TABLE_NAME)
.ToList() // jump from IQueryable<T> to IEnumerable<T>
.GroupBy(table => index++ / itemsPerGroup) // integer division will cause grouping
.Select(tableGroup => string.Format(
"// Add database views\nUNWIND [\n\t{0}\n] as item " + @"
MATCH (db:Database {{ name: item[0] }})
MATCH (sc:DbSchema {{ name: item[1] }})
MERGE (vw:DbView {{ name: item[2], schemaName: item[1] }})
MERGE (db)-[:CONTAINS]->(vw)
MERGE (sc)-[:CONTAINS]->(vw)
RETURN DISTINCT db, sc, vw;
",
string.Join(",\n\t", tableGroup
.Select(table => string.Format("[\"{0}\", \"{1}\", \"{2}\"]",
table.TABLE_CATALOG, table.TABLE_SCHEMA, table.TABLE_NAME)
)
))
);
foreach (var statement in statements)
{
Util.RawHtml("<pre>\n" + statement + "\n\n</pre>\n\n").Dump();
}
<Query Kind="Statements">
</Query>
var index = 0;
var itemsInGroup = 200;
var statements =
(from tc in INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where tc.CONSTRAINT_TYPE == "FOREIGN KEY"
join rc in INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
on new {
constraintName = tc.CONSTRAINT_NAME,
constraintSchema = tc.CONSTRAINT_SCHEMA
}
equals new {
constraintName = rc.CONSTRAINT_NAME,
constraintSchema = rc.CONSTRAINT_SCHEMA
}
join tc2 in INFORMATION_SCHEMA.TABLE_CONSTRAINTS
on new {
constraintName = rc.UNIQUE_CONSTRAINT_NAME,
constraintSchema = rc.UNIQUE_CONSTRAINT_SCHEMA }
equals new {
constraintName = tc2.CONSTRAINT_NAME,
constraintSchema = tc2.CONSTRAINT_SCHEMA
}
where tc2.CONSTRAINT_TYPE == "PRIMARY KEY"
select new {
referencingTableName = tc.TABLE_NAME,
referencingTableSchema = tc.TABLE_SCHEMA,
referencedTableName = tc2.TABLE_NAME,
referencedTableSchema = tc2.TABLE_SCHEMA,
foreignKeyName = rc.CONSTRAINT_NAME,
foreignKeySchema = rc.CONSTRAINT_SCHEMA
}).ToList()
.GroupBy (s => index++ / itemsInGroup)
.Select (g =>
string.Format(
"// Add table relationships\nUNWIND[\n\t{0}\n] AS item\nMATCH (tbl1:DbTable {{ name: item[0], schemaName: item[1] }})\nMATCH (tbl2:DbTable {{ name: item[2], schemaName: item[3] }})\nMERGE (tbl1)-[:REFERENCES {{ foreignKeyName: item[4], foreignKeySchemaName : item[5] }}]->(tbl2)\nRETURN tbl1, tbl2;",
string.Join(",\n\t",
g.Select (x =>
string.Format("[\"{0}\", \"{1}\", \"{2}\", \"{3}\", \"{4}\", \"{5}\"]", x.referencingTableName, x.referencingTableSchema, x.referencedTableName, x.referencedTableSchema, x.foreignKeyName, x.foreignKeySchema)
)
)
)
);
foreach (var statement in statements)
{
Util.RawHtml("<pre>\n" + statement + "\n\n</pre>\n\n").Dump();
}
<Query Kind="Statements">
</Query>
var index = 0;
var itemsPerGroup = 200;
var statements =
INFORMATION_SCHEMA.ROUTINES
.Where (rtn => rtn.ROUTINE_TYPE == "FUNCTION" && !rtn.ROUTINE_NAME.StartsWith("dt_")) // also, ... FUNCTION
.OrderBy (rtn => rtn.ROUTINE_SCHEMA)
.ThenBy (rtn => rtn.ROUTINE_NAME)
.ToList() // jump from IQueryable<T> to IEnumerable<T>
.GroupBy (rtn => index++ / itemsPerGroup) // integer division will cause grouping
.Select (d =>
string.Format(
"// Add database functions\nUNWIND [\n\t{0}\n] as item" + @"
MATCH (db:Database {{ name: item[0] }})
MATCH (sc:DbSchema {{ name: item[1] }})
MERGE (fnc:DbFunction {{ name: item[2], schemaName: item[1] }})
MERGE (db)-[:CONTAINS]->(fnc)
MERGE (sc)-[:CONTAINS]->(fnc)
RETURN DISTINCT db, sc, fnc;
",
string.Join(",\n\t", d
.Select (rtn => string.Format("[\"{0}\", \"{1}\", \"{2}\"]",
rtn.ROUTINE_CATALOG, rtn.ROUTINE_SCHEMA, rtn.ROUTINE_NAME)
)
))
);
foreach (var statement in statements)
{
Util.RawHtml("<pre>\n" + statement + "\n\n</pre>\n\n").Dump();
}
<Query Kind="Statements">
</Query>
var index = 0;
var itemsPerGroup = 200;
var statements =
INFORMATION_SCHEMA.ROUTINES
.Where (rtn => rtn.ROUTINE_TYPE == "PROCEDURE" && !rtn.ROUTINE_NAME.StartsWith("dt_")) // also, ... FUNCTION
.OrderBy (rtn => rtn.ROUTINE_SCHEMA)
.ThenBy (rtn => rtn.ROUTINE_NAME)
.ToList()
.GroupBy (rtn => index++ / itemsPerGroup) // integer division will cause grouping
.Select (d =>
string.Format(
"// Add stored procedures\nUNWIND [\n\t{0}\n] as item " + @"
MATCH (db:Database {{ name: item[0] }})
MATCH (sc:DbSchema {{ name: item[1] }})
MERGE (prc:DbProcedure {{ name: item[2], schemaName: item[1] }})
MERGE (db)-[:CONTAINS]->(prc)
MERGE (sc)-[:CONTAINS]->(prc)
RETURN DISTINCT db, sc, prc;
",
string.Join(",\n\t", d
.Select (rtn => string.Format("[\"{0}\", \"{1}\", \"{2}\"]",
rtn.ROUTINE_CATALOG, rtn.ROUTINE_SCHEMA, rtn.ROUTINE_NAME)
)
))
);
foreach (var statement in statements)
{
Util.RawHtml("<pre>\n" + statement + "\n\n</pre>\n\n").Dump();
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment