Generating DDL for dbo schema on MSSQL

已回答

Hi,

I am trying to generate a DDL for a MS SQL environment. The need for this DDL is to use it for version control as the databases are being worked on daily.

However I am running into an issue where all tables and views in the DDL are not present in their respective databases:

Upload id: 2022_01_19_WCm17oPpCRPLmsA6 (file: Screenshot 2022-01-20 124122.png)

0

Please press Ctrl+B on the objects under the anonymous db and show the content of that file, please also do the same for the object under the correct db. I'm curious why it works :)
The layout you've selected is not intended for multi-database dumps as you will have all the databases merged. I guess that is the thing you see, and all the databases except anonymous are empty.

The layouts are just script files hat tell where to put the objects.
So I've created as cript for your case:

import com.intellij.database.model.DasObjectWithSource
import com.intellij.database.model.DasSchemaChild
import com.intellij.database.model.ObjectKind
import com.intellij.database.util.DasUtil
import com.intellij.database.util.ObjectPath

LAYOUT.ignoreDependencies = true
LAYOUT.baseName { ctx -> baseName(ctx.object) }
LAYOUT.fileScope { path -> fileScope(path) }


def baseName(obj) {
def db = DasUtil.getCatalog(obj)
def schema = DasUtil.getSchema(obj)
def file = fileName(obj)
if (db.isEmpty()) {
if (!schema.isEmpty()) return "anonymous/" + sanitize(schema) + "/" + file
return file
}
else if (schema.isEmpty()) {
return sanitize(db) + "/" + file
}
else {
return sanitize(db) + "/" + sanitize(schema) + "/" + file
}
}

def fileName(obj) {
for (def cur = obj; cur != null; cur = cur.dasParent) {
if (storeSeparately(cur)) return sanitize(cur.name)
}
return sanitize(obj.name)
}

def fileScope(path) {
def root = path.getName(0).toString()
if (root.endsWith(".sql")) return null
def next = path.getName(1).toString()
if (next.endsWith(".sql")) {
if (root == "anonymous") return null
return ObjectPath.create(root, ObjectKind.DATABASE)
}
if (root == "anonymous") return ObjectPath.create(next, ObjectKind.SCHEMA)
return ObjectPath.create(root, ObjectKind.DATABASE).append(next, ObjectKind.SCHEMA)
}

def storeSeparately(obj) {
return obj instanceof DasObjectWithSource || obj instanceof DasSchemaChild
}

def sanitize(name) {
return name.replace('/', 'slash')
}


You can put it here:

And it will be available as layout

0

请先登录再写评论。