Database governance built for humans and agents — controlling changes and access across every major database.
Go cicddata-governancedata-maskingdata-securitydatabase-accessdatabase-governancedevopsdevsecopsflywaygitopsliquibasemongodbmysqloraclepostgresqlschema-migrationssecuritysql-clientsqlservertidb
package mssql
import (
"context"
"database/sql"
"fmt"
"slices"
"strings"
metadatapb "github.com/bytebase/omni/metadata"
"github.com/pkg/errors"
"github.com/bytebase/bytebase/backend/plugin/db"
"github.com/bytebase/bytebase/backend/plugin/db/util"
)
// SyncInstance syncs the instance.
func (d *Driver) SyncInstance(ctx context.Context) (*db.InstanceMetadata, error) {
var version, fullVersion string
if err := d.db.QueryRowContext(ctx, "SELECT SERVERPROPERTY('productversion'), @@VERSION").Scan(&version, &fullVersion); err != nil {
return nil, err
}
tokens := strings.Fields(fullVersion)
for _, token := range tokens {
if len(token) == 4 && strings.HasPrefix(token, "20") {
version = fmt.Sprintf("%s (%s)", version, token)
break
}
}
var databases []*metadatapb.DatabaseSchemaMetadata
rows, err := d.db.QueryContext(ctx, "SELECT name, collation_name FROM sys.databases WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb', 'rdscore')")
if err != nil {
return nil, err
}
defer rows.Close()
for rows.Next() {
database := &metadatapb.DatabaseSchemaMetadata{}
var collation sql.NullString
if err := rows.Scan(&database.Name, &collation); err != nil {
return nil, err
}
if collation.Valid {
database.Collation = collation.String
}
databases = append(databases, database)
}
if err := rows.Err(); err != nil {
return nil, err
}
return &db.InstanceMetadata{
Version: version,
Databases: databases,
}, nil
}
// SyncDBSchema syncs a single database schema.
func (d *Driver) SyncDBSchema(ctx context.Context) (*metadatapb.DatabaseSchemaMetadata, error) {
txn, err := d.db.BeginTx(ctx, nil)
if err != nil {
return nil, err
}
defer txn.Rollback()
schemaNames, err := getSchemas(txn)
if err != nil {
return nil, errors.Wrapf(err, "failed to get schemas from database %q", d.databaseName)
}
columnMap, err := getTableColumns(txn, schemaNames)
if err != nil {
return nil, errors.Wrapf(err, "failed to get table columns from database %q", d.databaseName)
}
tableMap, err := getTables(txn, schemaNames, columnMap)
if err != nil {
return nil, errors.Wrapf(err, "failed to get tables from database %q", d.databaseName)
}
viewMap, err := getViews(txn, columnMap)
if err != nil {
return nil, errors.Wrapf(err, "failed to get views from database %q", d.databaseName)
}
sequenceMap, err := getSequences(txn)
if err != nil {
return nil, errors.Wrapf(err, "failed to get sequences from database %q", d.databaseName)
}
functionMap, err := getFunctions(txn)
if err != nil {
return nil, errors.Wrapf(err, "failed to get functions from database %q", d.databaseName)
}
procedureMap, err := getProcedures(txn)
if err != nil {
return nil, errors.Wrapf(err, "failed to get procedures from database %q", d.databaseName)
}
tableTriggers, viewTriggers, err := getTriggers(txn)
if err != nil {
return nil, errors.Wrapf(err, "failed to get triggers from database %q", d.databaseName)
}
synonymMap, err := getSynonyms(txn)
if err != nil {
return nil, errors.Wrapf(err, "failed to get synonyms from database %q", d.databaseName)
}
// Changes run as the user that syncs, so this is where their unqualified
// names land.
var defaultSchema string
if err := txn.QueryRow("SELECT SCHEMA_NAME()").Scan(&defaultSchema); err != nil {
return nil, errors.Wrapf(err, "failed to get the default schema of database %q", d.databaseName)
}
for schemaName, tables := range tableMap {
for i := range tables {
table := tables[i]
if triggers, ok := tableTriggers[db.TableKey{Schema: schemaName, Table: table.Name}]; ok {
table.Triggers = append(table.Triggers, triggers...)
}
}
}
for schemaName, views := range viewMap {
for i := range views {
view := views[i]
if triggers, ok := viewTriggers[db.TableKey{Schema: schemaName, Table: view.Name}]; ok {
view.Triggers = append(view.Triggers, triggers...)
}
}
}
if err := txn.Commit(); err != nil {
return nil, err
}
databaseMetadata := &metadatapb.DatabaseSchemaMetadata{
Name: d.databaseName,
DefaultSchema: defaultSchema,
}
for _, schemaName := range schemaNames {
databaseMetadata.Schemas = append(databaseMetadata.Schemas, &metadatapb.SchemaMetadata{
Name: schemaName,
Tables: tableMap[schemaName],
Views: viewMap[schemaName],
Sequences: sequenceMap[schemaName],
Functions: functionMap[schemaName],
Procedures: procedureMap[schemaName],
Synonyms: synonymMap[schemaName],
})
}
return databaseMetadata, nil
}
// getSynonyms gets the synonyms of a database by schema, with the parts of
// the name each stands for.
func getSynonyms(txn *sql.Tx) (map[string][]*metadatapb.SynonymMetadata, error) {
query := `
SELECT
SCHEMA_NAME(schema_id),
name,
PARSENAME(base_object_name, 4),
PARSENAME(base_object_name, 3),
PARSENAME(base_object_name, 2),
PARSENAME(base_object_name, 1)
FROM sys.synonyms
ORDER BY SCHEMA_NAME(schema_id), name`
rows, err := txn.Query(query)
if err != nil {
return nil, util.FormatErrorWithQuery(err, query)
}
defer rows.Close()
synonymMap := make(map[string][]*metadatapb.SynonymMetadata)
for rows.Next() {
var schemaName, name string
var server, database, targetSchema, targetName sql.NullString
if err := rows.Scan(&schemaName, &name, &server, &database, &targetSchema, &targetName); err != nil {
return nil, err
}
synonymMap[schemaName] = append(synonymMap[schemaName], &metadatapb.SynonymMetadata{
Name: name,
TargetServer: server.String,
TargetDatabase: database.String,
TargetSchema: targetSchema.String,
TargetName: targetName.String,
})
}
if err := rows.Err(); err != nil {
return nil, util.FormatErrorWithQuery(err, query)
}
return synonymMap, nil
}
func getSchemas(txn *sql.Tx) ([]string, error) {
query := `
SELECT schema_name FROM INFORMATION_SCHEMA.SCHEMATA
WHERE schema_name NOT IN ('db_owner', 'db_accessadmin', 'db_backupoperator', 'db_datareader', 'db_datawriter', 'db_ddladmin', 'db_denydatareader', 'db_denydatawriter', 'db_securityadmin', 'guest', 'INFORMATION_SCHEMA', 'sys');`
rows, err := txn.Query(query)
if err != nil {
return nil, err
}
defer rows.Close()
var result []string
for rows.Next() {
var schemaName string
if err := rows.Scan(&schemaName); err != nil {
return nil, err
}
result = append(result, schemaName)
}
if err := rows.Err(); err != nil {
return nil, err
}
return result, nil
}
// getTables gets all tables of a database.
func getTables(txn *sql.Tx, schemas []string, columnMap map[db.TableKey][]*metadatapb.ColumnMetadata) (map[string][]*metadatapb.TableMetadata, error) {
indexMap, err := getKeyAndIndexes(txn, schemas)
if err != nil {
return nil, errors.Wrapf(err, "failed to get indices")
}
fkMap, err := getForeignKeys(txn, schemas)
if err != nil {
return nil, errors.Wrapf(err, "failed to get foreign keys")
}
checkMap, err := getChecks(txn, schemas)
if err != nil {
return nil, errors.Wrapf(err, "failed to get checks")
}
// Get table comments separately
tableCommentsMap := make(map[db.TableKey]string)
// First get object IDs with schema info
objectSchemaMap := make(map[int]struct{ Schema, Table string })
objectQuery := `
SELECT o.object_id, s.name AS schema_name, o.name AS table_name
FROM sys.all_objects o
INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
WHERE o.type = 'U'`
objectRows, err := txn.Query(objectQuery)
if err == nil {
defer objectRows.Close()
for objectRows.Next() {
var objectID int
var schemaName, tableName string
if err := objectRows.Scan(&objectID, &schemaName, &tableName); err != nil {
continue
}
objectSchemaMap[objectID] = struct{ Schema, Table string }{schemaName, tableName}
}
if err := objectRows.Err(); err != nil {
return nil, errors.Wrap(err, "failed to iterate object schema mapping")
}
}
// Then get extended properties
commentsQuery := `
SELECT major_id, value
FROM sys.extended_properties
WHERE name = 'MS_Description' AND minor_id = 0 AND class = 1`
commentsRows, err := txn.Query(commentsQuery)
if err == nil {
defer commentsRows.Close()
for commentsRows.Next() {
var objectID int
var comment string
if err := commentsRows.Scan(&objectID, &comment); err != nil {
continue
}
// Join in Go
if obj, ok := objectSchemaMap[objectID]; ok {
key := db.TableKey{Schema: obj.Schema, Table: obj.Table}
tableCommentsMap[key] = comment
}
}
if err := commentsRows.Err(); err != nil {
// Log error but continue - comments are not critical
return nil, errors.Wrap(err, "failed to fetch table comments")
}
}
// Get table row counts separately
rowCountMap := make(map[int]int64)
rowCountQuery := `
SELECT object_id, SUM(row_count) AS total_rows
FROM sys.dm_db_partition_stats
WHERE index_id < 2
GROUP BY object_id`
rowCountRows, err := txn.Query(rowCountQuery)
if err == nil {
defer rowCountRows.Close()
for rowCountRows.Next() {
var objectID int
var rowCount int64
if err := rowCountRows.Scan(&objectID, &rowCount); err != nil {
continue
}
rowCountMap[objectID] = rowCount
}
if err := rowCountRows.Err(); err != nil {
return nil, errors.Wrap(err, "failed to iterate row counts")
}
}
tableMap := make(map[string][]*metadatapb.TableMetadata)
query := `
SELECT
t.object_id,
SCHEMA_NAME(t.schema_id),
t.name
FROM sys.tables t
ORDER BY SCHEMA_NAME(t.schema_id), t.name ASC`
rows, err := txn.Query(query)
if err != nil {
return nil, err
}
defer rows.Close()
for rows.Next() {
table := &metadatapb.TableMetadata{}
var objectID int
var schemaName string
if err := rows.Scan(&objectID, &schemaName, &table.Name); err != nil {
return nil, err
}
// Join with row count in Go
if rowCount, ok := rowCountMap[objectID]; ok {
table.RowCount = rowCount
}
key := db.TableKey{Schema: schemaName, Table: table.Name}
table.Columns = columnMap[key]
table.Indexes = indexMap[key]
table.ForeignKeys = fkMap[key]
table.CheckConstraints = checkMap[key]
// Join with comments in Go
if comment, ok := tableCommentsMap[key]; ok {
table.Comment = comment
}
tableMap[schemaName] = append(tableMap[schemaName], table)
}
if err := rows.Err(); err != nil {
return nil, err
}
return tableMap, nil
}
func getChecks(txn *sql.Tx, schemas []string) (map[db.TableKey][]*metadatapb.CheckConstraintMetadata, error) {
checkMap := make(map[db.TableKey][]*metadatapb.CheckConstraintMetadata)
// Get check constraint comments separately
checkCommentsMap := make(map[int]string)
commentsQuery := `
SELECT
p.major_id AS object_id,
CAST(p.[value] AS nvarchar(4000)) AS comment
FROM sys.extended_properties p
WHERE p.minor_id = 0 AND p.name = 'MS_Description'
AND p.major_id IN (SELECT object_id FROM sys.check_constraints)`
commentsRows, err := txn.Query(commentsQuery)
if err == nil {
defer commentsRows.Close()
for commentsRows.Next() {
var objectID int
var comment sql.NullString
if err := commentsRows.Scan(&objectID, &comment); err != nil {
continue
}
if comment.Valid {
checkCommentsMap[objectID] = comment.String
}
}
if err := commentsRows.Err(); err != nil {
// Log error but continue - comments are not critical
return nil, errors.Wrap(err, "failed to fetch check constraint comments")
}
}
// Get object schema mapping first
objectSchemaMap := make(map[int]struct{ Schema, Table string })
objectQuery := fmt.Sprintf(`
SELECT o.object_id, s.name AS schema_name, o.name AS table_name
FROM sys.objects o
INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
WHERE s.name in (%s) AND o.type IN ('U')
`, quoteList(schemas))
objectRows, err := txn.Query(objectQuery)
if err != nil {
return nil, util.FormatErrorWithQuery(err, objectQuery)
}
defer objectRows.Close()
for objectRows.Next() {
var objectID int
var schemaName, tableName string
if err := objectRows.Scan(&objectID, &schemaName, &tableName); err != nil {
continue
}
objectSchemaMap[objectID] = struct{ Schema, Table string }{schemaName, tableName}
}
if err := objectRows.Err(); err != nil {
return nil, errors.Wrap(err, "failed to fetch object schema mapping")
}
dumpCheckConstraintSQL := `
SELECT
ch.parent_object_id,
ch.name,
ch.object_id,
ch.definition
FROM sys.check_constraints ch
ORDER BY ch.parent_object_id, ch.object_id`
rows, err := txn.Query(dumpCheckConstraintSQL)
if err != nil {
return nil, util.FormatErrorWithQuery(err, dumpCheckConstraintSQL)
}
defer rows.Close()
for rows.Next() {
var parentObjectID int
var checkName, definition sql.NullString
var objectID sql.NullInt32
if err := rows.Scan(&parentObjectID, &checkName, &objectID, &definition); err != nil {
return nil, err
}
if !checkName.Valid || !definition.Valid {
continue
}
// Join with schema mapping in Go
obj, ok := objectSchemaMap[parentObjectID]
if !ok {
continue
}
key := db.TableKey{Schema: obj.Schema, Table: obj.Table}
check := &metadatapb.CheckConstraintMetadata{
Name: checkName.String,
Expression: definition.String,
}
// TODO: Join with comments when CheckConstraintMetadata supports it
// Currently the proto doesn't support comments for check constraints
// When support is added, use:
// if objectID.Valid {
// if comment, ok := checkCommentsMap[int(objectID.Int32)]; ok {
// check.Comment = comment
// }
// }
checkMap[key] = append(checkMap[key], check)
}
if err := rows.Err(); err != nil {
return nil, err
}
return checkMap, nil
}
func referentialAction(action int) string {
switch action {
case 0:
return "NO ACTION"
case 1:
return "CASCADE"
case 2:
return "SET NULL"
case 3:
return "SET DEFAULT"
default:
return "NO ACTION"
}
}
func getForeignKeys(txn *sql.Tx, schemas []string) (map[db.TableKey][]*metadatapb.ForeignKeyMetadata, error) {
fkMap := make(map[db.TableKey]map[string]*metadatapb.ForeignKeyMetadata)
// Get foreign key comments separately
fkCommentsMap := make(map[int]string)
commentsQuery := `
SELECT
p.major_id AS object_id,
CAST(p.[value] AS nvarchar(4000)) AS comment
FROM sys.extended_properties p
WHERE p.minor_id = 0 AND p.name = 'MS_Description'
AND p.major_id IN (SELECT object_id FROM sys.foreign_keys)`
commentsRows, err := txn.Query(commentsQuery)
if err == nil {
defer commentsRows.Close()
for commentsRows.Next() {
var objectID int
var comment sql.NullString
if err := commentsRows.Scan(&objectID, &comment); err != nil {
continue
}
if comment.Valid {
fkCommentsMap[objectID] = comment.String
}
}
if err := commentsRows.Err(); err != nil {
// Log error but continue - comments are not critical
return nil, errors.Wrap(err, "failed to fetch foreign key comments")
}
}
dumpForeignKeySQL := fmt.Sprintf(`
SELECT
s.name AS schema_name,
o.name AS table_name,
fk.object_id,
fk.name,
OBJECT_SCHEMA_NAME(fk.referenced_object_id) AS referenced_schema,
OBJECT_NAME(fk.referenced_object_id) AS referenced_table,
fk.delete_referential_action,
fk.update_referential_action,
pc.name AS parent_column,
rc.name AS referenced_column
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN sys.objects o ON o.object_id = fk.parent_object_id
INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
INNER JOIN sys.all_columns pc ON pc.object_id = fkc.parent_object_id AND pc.column_id = fkc.parent_column_id
INNER JOIN sys.all_columns rc ON rc.object_id = fkc.referenced_object_id AND rc.column_id = fkc.referenced_column_id
WHERE s.name in (%s)
ORDER BY s.name ASC, o.object_id ASC, fk.object_id ASC, fkc.constraint_column_id ASC
`, quoteList(schemas))
rows, err := txn.Query(dumpForeignKeySQL)
if err != nil {
return nil, err
}
defer rows.Close()
for rows.Next() {
var schemaName, tableName, fkName, referencedSchemaName, referencedTableName, parentColumnName, referencedColumnName sql.NullString
var objectID, onDelete, onUpdate sql.NullInt32
if err := rows.Scan(&schemaName, &tableName, &objectID, &fkName, &referencedSchemaName, &referencedTableName, &onDelete, &onUpdate, &parentColumnName, &referencedColumnName); err != nil {
return nil, err
}
if !schemaName.Valid || !tableName.Valid || !fkName.Valid || !referencedSchemaName.Valid || !referencedTableName.Valid || !parentColumnName.Valid || !referencedColumnName.Valid {
continue
}
outerKey := db.TableKey{Schema: schemaName.String, Table: tableName.String}
if _, ok := fkMap[outerKey]; !ok {
fkMap[outerKey] = make(map[string]*metadatapb.ForeignKeyMetadata)
}
if _, ok := fkMap[outerKey][fkName.String]; !ok {
fk := &metadatapb.ForeignKeyMetadata{
Name: fkName.String,
ReferencedSchema: referencedSchemaName.String,
ReferencedTable: referencedTableName.String,
}
// TODO: Join with comments when ForeignKeyMetadata supports it
// Currently the proto doesn't support comments for foreign keys
// When support is added, use:
// if objectID.Valid {
// if comment, ok := fkCommentsMap[int(objectID.Int32)]; ok {
// fk.Comment = comment
// }
// }
if onDelete.Valid {
fk.OnDelete = referentialAction(int(onDelete.Int32))
}
if onUpdate.Valid {
fk.OnUpdate = referentialAction(int(onUpdate.Int32))
}
fkMap[outerKey][fkName.String] = fk
}
fkMap[outerKey][fkName.String].Columns = append(fkMap[outerKey][fkName.String].Columns, parentColumnName.String)
fkMap[outerKey][fkName.String].ReferencedColumns = append(fkMap[outerKey][fkName.String].ReferencedColumns, referencedColumnName.String)
}
if err := rows.Err(); err != nil {
return nil, err
}
// Aggregate the map to a slice.
result := make(map[db.TableKey][]*metadatapb.ForeignKeyMetadata)
for k, m := range fkMap {
var foreignkeyNames []string
for _, v := range m {
foreignkeyNames = append(foreignkeyNames, v.Name)
}
slices.Sort(foreignkeyNames)
for _, fkName := range foreignkeyNames {
result[k] = append(result[k], m[fkName])
}
}
return result, nil
}
func quote(s string) string {
return fmt.Sprintf("N'%s'", s)
}
func quoteList(schemas []string) string {
var quoted []string
for _, schema := range schemas {
quoted = append(quoted, quote(schema))
}
return strings.Join(quoted, ",")
}
// getTableColumns gets the columns of a table.
func getTableColumns(txn *sql.Tx, schemas []string) (map[db.TableKey][]*metadatapb.ColumnMetadata, error) {
columnsMap := make(map[db.TableKey][]*metadatapb.ColumnMetadata)
// Get column comments separately
columnCommentsMap := make(map[struct{ ObjectID, ColumnID int }]string)
commentsQuery := `
SELECT
p.major_id AS object_id,
p.minor_id AS column_id,
CAST(p.[value] AS nvarchar(4000)) AS comment
FROM sys.extended_properties p
WHERE p.class = 1 AND p.name = 'MS_Description' AND p.minor_id > 0`
commentsRows, err := txn.Query(commentsQuery)
if err == nil {
defer commentsRows.Close()
for commentsRows.Next() {
var objectID, columnID int
var comment sql.NullString
if err := commentsRows.Scan(&objectID, &columnID, &comment); err != nil {
continue
}
if comment.Valid {
columnCommentsMap[struct{ ObjectID, ColumnID int }{objectID, columnID}] = comment.String
}
}
if err := commentsRows.Err(); err != nil {
// Log error but continue - comments are not critical
return nil, errors.Wrap(err, "failed to fetch column comments")
}
}
// Get default constraints separately
defaultsMap := make(map[int]struct{ Definition, Name string })
defaultsQuery := `
SELECT
so.object_id,
dc.definition,
so.name AS default_name
FROM sys.objects so
INNER JOIN sys.default_constraints dc ON dc.object_id = so.object_id
WHERE so.type = 'D'`
defaultsRows, err := txn.Query(defaultsQuery)
if err == nil {
defer defaultsRows.Close()
for defaultsRows.Next() {
var objectID int
var definition, name sql.NullString
if err := defaultsRows.Scan(&objectID, &definition, &name); err != nil {
continue
}
if definition.Valid && name.Valid {
defaultsMap[objectID] = struct{ Definition, Name string }{definition.String, name.String}
}
}
if err := defaultsRows.Err(); err != nil {
// Log error but continue - defaults are not critical
return nil, errors.Wrap(err, "failed to fetch default constraints")
}
}
getColumnSQL := fmt.Sprintf(`
SELECT
s.name AS schema_name,
OBJECT_NAME(c.object_id) AS table_name,
c.object_id,
c.column_id,
c.name AS column_name,
t.name AS type_name,
c.is_computed,
cc.definition,
cc.is_persisted,
c.max_length,
c.precision AS precision,
c.scale,
c.collation_name,
c.is_nullable,
c.is_identity,
c.default_object_id,
id.seed_value AS seed_value,
id.increment_value AS increment_value
FROM sys.columns c
LEFT JOIN sys.computed_columns cc ON cc.object_id = c.object_id AND cc.column_id = c.column_id
LEFT JOIN sys.types t ON c.user_type_id = t.user_type_id
LEFT JOIN sys.objects o ON o.object_id = c.object_id
LEFT JOIN sys.schemas s ON s.schema_id = o.schema_id
LEFT JOIN sys.identity_columns id ON c.object_id = id.object_id AND c.column_id = id.column_id
WHERE s.name in (%s)
ORDER BY s.name ASC, c.object_id ASC, c.column_id ASC
`, quoteList(schemas))
rows, err := txn.Query(getColumnSQL)
if err != nil {
return nil, err
}
defer rows.Close()
// Track position counter per table to compute ordinal positions
// Since results are ordered by object_id and column_id, we can calculate sequential positions
positionMap := make(map[db.TableKey]int32)
for rows.Next() {
var schemaName, tableName, columnName, typeName, definition, collationName sql.NullString
var isComputed, isPersisted, isNullable, isIdentity sql.NullBool
var objectID, columnID, defaultObjectID sql.NullInt32
var maxLength, precision, scale, seedValue, incrementValue sql.NullInt64
if err := rows.Scan(
&schemaName,
&tableName,
&objectID,
&columnID,
&columnName,
&typeName,
&isComputed,
&definition,
&isPersisted,
&maxLength,
&precision,
&scale,
&collationName,
&isNullable,
&isIdentity,
&defaultObjectID,
&seedValue,
&incrementValue,
); err != nil {
return nil, err
}
if !schemaName.Valid || !tableName.Valid || !columnName.Valid || !typeName.Valid {
continue
}
column := &metadatapb.ColumnMetadata{
Name: columnName.String,
}
// Calculate ordinal position (1-based sequential position per table)
// Results are ordered by column_id, so we increment position for each column in the table
key := db.TableKey{Schema: schemaName.String, Table: tableName.String}
positionMap[key]++
column.Position = positionMap[key]
column.Type, err = getColumnType(definition, typeName, isComputed, isPersisted, precision, scale, maxLength)
if err != nil {
return nil, errors.Errorf("failed to get column type: %v", err)
}
if isIdentity.Valid && isIdentity.Bool && seedValue.Valid && incrementValue.Valid {
column.IsIdentity = true
column.IdentitySeed = seedValue.Int64
column.IdentityIncrement = incrementValue.Int64
}
if collationName.Valid {
column.Collation = collationName.String
}
// Join with defaults in Go
if defaultObjectID.Valid {
if def, ok := defaultsMap[int(defaultObjectID.Int32)]; ok {
column.Default = def.Definition
column.DefaultConstraintName = def.Name
}
}
column.Nullable = true
if isNullable.Valid && !isNullable.Bool {
column.Nullable = false
}
// Join with comments in Go
if objectID.Valid && columnID.Valid {
if comment, ok := columnCommentsMap[struct{ ObjectID, ColumnID int }{int(objectID.Int32), int(columnID.Int32)}]; ok {
column.Comment = comment
}
}
columnsMap[key] = append(columnsMap[key], column)
}
if err := rows.Err(); err != nil {
return nil, err
}
return columnsMap, nil
}
func getColumnType(definition, typeName sql.NullString, isComputed, isPersisted sql.NullBool, precision, scale, maxLength sql.NullInt64) (string, error) {
var buf strings.Builder
if definition.Valid && isComputed.Valid && isComputed.Bool {
if _, err := fmt.Fprintf(&buf, "AS %s", definition.String); err != nil {
return "", err
}
if isPersisted.Valid && isPersisted.Bool {
if _, err := fmt.Fprint(&buf, " PERSISTED"); err != nil {
return "", err
}
}
return buf.String(), nil
}
if !typeName.Valid {
return "", errors.New("column type name is not valid")
}
if _, err := fmt.Fprintf(&buf, "%s", typeName.String); err != nil {
return "", err
}
switch typeName.String {
case "decimal", "numeric":
if precision.Valid && scale.Valid {
if _, err := fmt.Fprintf(&buf, "(%d, %d)", precision.Int64, scale.Int64); err != nil {
return "", err
}
} else if precision.Valid {
if _, err := fmt.Fprintf(&buf, "(%d)", precision.Int64); err != nil {
return "", err
}
}
case "float", "real":
if precision.Valid {
if _, err := fmt.Fprintf(&buf, "(%d)", precision.Int64); err != nil {
return "", err
}
}
case "dateoffset", "datetime2", "time":
if scale.Valid {
if _, err := fmt.Fprintf(&buf, "(%d)", scale.Int64); err != nil {
return "", err
}
}
case "char", "nchar", "varchar", "nvarchar", "binary", "varbinary":
if maxLength.Valid {
if maxLength.Int64 == -1 {
if _, err := fmt.Fprint(&buf, "(max)"); err != nil {
return "", err
}
} else {
// For Unicode types (nchar, nvarchar), SQL Server stores byte count in max_length
// Each Unicode character takes 2 bytes, so we need to divide by 2
length := maxLength.Int64
if typeName.String == "nchar" || typeName.String == "nvarchar" {
length /= 2
}
if _, err := fmt.Fprintf(&buf, "(%d)", length); err != nil {
return "", err
}
}
}
default:
// For other types, no additional formatting is needed
// The type name has already been written to the buffer
}
return buf.String(), nil
}
func getIndexes(txn *sql.Tx, schemas []string) (map[db.TableKey][]*metadatapb.IndexMetadata, error) {
// MSSQL doesn't support function-based indexes.
indexMap := make(map[db.TableKey]map[string]*metadatapb.IndexMetadata)
// Get index comments separately
indexCommentsMap := make(map[struct{ ObjectID, IndexID int }]string)
commentsQuery := `
SELECT
ep.major_id AS object_id,
ep.minor_id AS index_id,
CAST(ep.value AS NVARCHAR(MAX)) AS comment
FROM sys.extended_properties ep
WHERE ep.class = 7 AND ep.name = 'MS_Description' AND ep.minor_id > 0`
commentsRows, err := txn.Query(commentsQuery)
if err == nil {
defer commentsRows.Close()
for commentsRows.Next() {
var objectID, indexID int
var comment sql.NullString
if err := commentsRows.Scan(&objectID, &indexID, &comment); err != nil {
continue
}
if comment.Valid {
indexCommentsMap[struct{ ObjectID, IndexID int }{objectID, indexID}] = comment.String
}
}
if err := commentsRows.Err(); err != nil {
// Log error but continue - comments are not critical
return nil, errors.Wrap(err, "failed to fetch index comments")
}
}
query := fmt.Sprintf(`
SELECT
s.name AS schema_name,
o.name AS table_name,
i.object_id,
i.index_id,
i.name,
i.type_desc,
i.is_unique,
col.name AS column_name,
ic.is_descending_key
FROM sys.indexes i
INNER JOIN sys.all_objects o ON o.object_id = i.object_id
INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
INNER JOIN sys.index_columns ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
INNER JOIN sys.all_columns col ON ic.column_id = col.column_id AND ic.object_id = col.object_id
WHERE i.index_id > 0 AND i.is_primary_key = 0 AND i.is_unique_constraint = 0
AND s.name in (%s) AND o.type IN ('U', 'S', 'V')
ORDER BY s.name, o.name, i.index_id, ic.key_ordinal
`, quoteList(schemas))
rows, err := txn.Query(query)
if err != nil {
return nil, err
}
defer rows.Close()
for rows.Next() {
var schemaName, tableName, indexName, typeDesc, colName sql.NullString
var objectID, indexID sql.NullInt32
var isUnique, isDescending sql.NullBool
if err := rows.Scan(&schemaName, &tableName, &objectID, &indexID, &indexName, &typeDesc, &isUnique, &colName, &isDescending); err != nil {
return nil, err
}
if !schemaName.Valid || !tableName.Valid || !indexName.Valid || !colName.Valid {
continue
}
key := db.TableKey{Schema: schemaName.String, Table: tableName.String}
if _, ok := indexMap[key]; !ok {
indexMap[key] = make(map[string]*metadatapb.IndexMetadata)
}
if _, ok := indexMap[key][indexName.String]; !ok {
// This query excludes primary keys and unique constraints, which are
// reported separately, so is_unique here means a CREATE UNIQUE INDEX.
index := &metadatapb.IndexMetadata{
Name: indexName.String,
Unique: isUnique.Valid && isUnique.Bool,
Primary: false,
IsConstraint: false,
}
if typeDesc.Valid {
index.Type = typeDesc.String
// If this is a spatial index, populate basic spatial config
if typeDesc.String == "SPATIAL" {
index.SpatialConfig = &metadatapb.SpatialIndexConfig{
Method: "SPATIAL",
Tessellation: &metadatapb.TessellationConfig{
Scheme: "UNKNOWN", // Will be updated by getSpatialIndexes if available
},
Storage: &metadatapb.StorageConfig{},
Dimensional: &metadatapb.DimensionalConfig{
DataType: "GEOMETRY", // Default, will be updated if available
Dimensions: 2,
},
}
}
}
// Join with comments in Go
if objectID.Valid && indexID.Valid {
if comment, ok := indexCommentsMap[struct{ ObjectID, IndexID int }{int(objectID.Int32), int(indexID.Int32)}]; ok {
index.Comment = comment
}
}
indexMap[key][indexName.String] = index
}
indexMap[key][indexName.String].Expressions = append(indexMap[key][indexName.String].Expressions, colName.String)
if isDescending.Valid && isDescending.Bool {
indexMap[key][indexName.String].Descending = append(indexMap[key][indexName.String].Descending, true)
} else {
indexMap[key][indexName.String].Descending = append(indexMap[key][indexName.String].Descending, false)
}
}
if err := rows.Err(); err != nil {
return nil, err
}
tableIndexes := make(map[db.TableKey][]*metadatapb.IndexMetadata)
for k, m := range indexMap {
for _, v := range m {
tableIndexes[k] = append(tableIndexes[k], v)
}
slices.SortFunc(tableIndexes[k], func(a, b *metadatapb.IndexMetadata) int {
if a.Name < b.Name {
return -1
}
if a.Name > b.Name {
return 1
}
return 0
})
}
// Get spatial indexes separately
// If sys.spatial_indexes doesn't exist (e.g., SQL Server Express), this will fail
// In that case, we'll just use the spatial indexes found by the regular index query
spatialIndexes, err := getSpatialIndexes(txn, schemas)
if err != nil {
// Ignore error - spatial indexes might have been found by regular index query
spatialIndexes = make(map[db.TableKey][]*metadatapb.IndexMetadata)
}
// Merge spatial indexes with regular indexes
// Need to replace spatial indexes found by regular query with properly configured ones
// Merge spatial indexes with regular indexes
for k, spatialIdxs := range spatialIndexes {
// Process table spatial indexes
if _, ok := tableIndexes[k]; !ok {
tableIndexes[k] = make([]*metadatapb.IndexMetadata, 0)
}
// Remove any spatial indexes from regular indexes first
var nonSpatialIndexes []*metadatapb.IndexMetadata
for _, idx := range tableIndexes[k] {
if idx.Type != "SPATIAL" {
nonSpatialIndexes = append(nonSpatialIndexes, idx)
}
}
// Replace with non-spatial indexes and add properly configured spatial indexes
tableIndexes[k] = nonSpatialIndexes
tableIndexes[k] = append(tableIndexes[k], spatialIdxs...)
}
return tableIndexes, nil
}
func getSpatialIndexes(txn *sql.Tx, schemas []string) (map[db.TableKey][]*metadatapb.IndexMetadata, error) {
indexMap := make(map[db.TableKey]map[string]*metadatapb.IndexMetadata)
// Get spatial index comments separately
spatialCommentsMap := make(map[struct{ ObjectID, IndexID int }]string)
commentsQuery := `
SELECT
ep.major_id AS object_id,
ep.minor_id AS index_id,
CAST(ep.value AS NVARCHAR(MAX)) AS comment
FROM sys.extended_properties ep
WHERE ep.class = 7 AND ep.name = 'MS_Description' AND ep.minor_id > 0
AND ep.major_id IN (SELECT object_id FROM sys.spatial_indexes)`
commentsRows, err := txn.Query(commentsQuery)
if err == nil {
defer commentsRows.Close()
for commentsRows.Next() {
var objectID, indexID int
var comment sql.NullString
if err := commentsRows.Scan(&objectID, &indexID, &comment); err != nil {
continue
}
if comment.Valid {
spatialCommentsMap[struct{ ObjectID, IndexID int }{objectID, indexID}] = comment.String
}
}
if err := commentsRows.Err(); err != nil {
return nil, errors.Wrap(err, "failed to fetch spatial index comments")
}
}
// Get tessellation data separately
tessellationMap := make(map[struct{ ObjectID, IndexID int }]struct {
BoundingBox [4]float64
GridLevels [4]string
CellsPerObject int32
})
tessellationQuery := `
SELECT
object_id,
index_id,
COALESCE(bounding_box_xmin, 0),
COALESCE(bounding_box_ymin, 0),
COALESCE(bounding_box_xmax, 0),
COALESCE(bounding_box_ymax, 0),
COALESCE(level_1_grid_desc, ''),
COALESCE(level_2_grid_desc, ''),
COALESCE(level_3_grid_desc, ''),
COALESCE(level_4_grid_desc, ''),
COALESCE(cells_per_object, 0)
FROM sys.spatial_index_tessellations`
tessRows, err := txn.Query(tessellationQuery)
if err == nil {
defer tessRows.Close()
for tessRows.Next() {
var objectID, indexID int
var xmin, ymin, xmax, ymax float64
var level1, level2, level3, level4 string
var cellsPerObject int32
if err := tessRows.Scan(&objectID, &indexID, &xmin, &ymin, &xmax, &ymax,
&level1, &level2, &level3, &level4, &cellsPerObject); err != nil {
continue
}
key := struct{ ObjectID, IndexID int }{objectID, indexID}
tessellationMap[key] = struct {
BoundingBox [4]float64
GridLevels [4]string
CellsPerObject int32
}{
BoundingBox: [4]float64{xmin, ymin, xmax, ymax},
GridLevels: [4]string{level1, level2, level3, level4},
CellsPerObject: cellsPerObject,
}
}
if err := tessRows.Err(); err != nil {
return nil, errors.Wrap(err, "failed to iterate tessellation data")
}
}
// Main query for spatial indexes
query := fmt.Sprintf(`
SELECT
s.name AS schema_name,
o.name AS table_name,
si.object_id,
si.index_id,
i.name AS index_name,
ic.key_ordinal,
col.name AS column_name,
si.spatial_index_type,
si.spatial_index_type_desc,
si.tessellation_scheme,
col.system_type_id,
i.filter_definition,
i.fill_factor,
CASE WHEN i.is_padded = 1 THEN 1 ELSE 0 END AS is_padded,
CASE WHEN i.allow_row_locks = 1 THEN 1 ELSE 0 END AS allow_row_locks,
CASE WHEN i.allow_page_locks = 1 THEN 1 ELSE 0 END AS allow_page_locks
FROM sys.spatial_indexes si
INNER JOIN sys.indexes i ON si.object_id = i.object_id AND si.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns col ON ic.object_id = col.object_id AND ic.column_id = col.column_id
WHERE s.name IN (%s) AND o.type IN ('U', 'S', 'V')
ORDER BY s.name, o.name, i.name, ic.key_ordinal
`, quoteList(schemas))
rows, err := txn.Query(query)
if err != nil {
return nil, err
}
defer rows.Close()
for rows.Next() {
var schemaName, tableName, indexName, columnName sql.NullString
var objectID, indexID, keyOrdinal sql.NullInt32
var spatialIndexType sql.NullInt32
var spatialIndexTypeDesc, tessellationScheme sql.NullString
var systemTypeID sql.NullInt32
var filterDefinition sql.NullString
var fillFactor sql.NullInt32
var isPadded, allowRowLocks, allowPageLocks sql.NullInt32
if err := rows.Scan(&schemaName, &tableName, &objectID, &indexID, &indexName, &keyOrdinal, &columnName,
&spatialIndexType, &spatialIndexTypeDesc, &tessellationScheme,
&systemTypeID, &filterDefinition, &fillFactor, &isPadded,
&allowRowLocks, &allowPageLocks); err != nil {
return nil, err
}
if !schemaName.Valid || !tableName.Valid || !indexName.Valid || !columnName.Valid {
continue
}
key := db.TableKey{Schema: schemaName.String, Table: tableName.String}
if _, ok := indexMap[key]; !ok {
indexMap[key] = make(map[string]*metadatapb.IndexMetadata)
}
if _, ok := indexMap[key][indexName.String]; !ok {
// Create new spatial index metadata
index := &metadatapb.IndexMetadata{
Name: indexName.String,
Type: "SPATIAL",
Unique: false,
Primary: false,
SpatialConfig: &metadatapb.SpatialIndexConfig{
Method: "SPATIAL",
},
}
// Join with comments in Go
if objectID.Valid && indexID.Valid {
if comment, ok := spatialCommentsMap[struct{ ObjectID, IndexID int }{int(objectID.Int32), int(indexID.Int32)}]; ok {
index.Comment = comment
}
}
// Determine data type from spatial_index_type_desc (more reliable)
dataType := "GEOMETRY"
if spatialIndexTypeDesc.Valid {
dataType = spatialIndexTypeDesc.String
} else if systemTypeID.Valid {
// Fallback: system_type_id 240 = GEOMETRY, 241 = GEOGRAPHY
if systemTypeID.Int32 == 241 {
dataType = "GEOGRAPHY"
}
}
// Configure tessellation with complete metadata
index.SpatialConfig.Tessellation = &metadatapb.TessellationConfig{}
if tessellationScheme.Valid {
index.SpatialConfig.Tessellation.Scheme = tessellationScheme.String
} else {
// Fallback based on data type
index.SpatialConfig.Tessellation.Scheme = fmt.Sprintf("%s_GRID", dataType)
}
// Join with tessellation data in Go
if objectID.Valid && indexID.Valid {
tessKey := struct{ ObjectID, IndexID int }{int(objectID.Int32), int(indexID.Int32)}
if tessData, ok := tessellationMap[tessKey]; ok {
// Add bounding box (for GEOMETRY indexes or when explicitly provided)
if tessData.BoundingBox[0] != 0 || tessData.BoundingBox[1] != 0 ||
tessData.BoundingBox[2] != 0 || tessData.BoundingBox[3] != 0 {
index.SpatialConfig.Tessellation.BoundingBox = &metadatapb.BoundingBox{
Xmin: tessData.BoundingBox[0],
Ymin: tessData.BoundingBox[1],
Xmax: tessData.BoundingBox[2],
Ymax: tessData.BoundingBox[3],
}
}
// Add grid levels with proper descriptions
gridLevels := []*metadatapb.GridLevel{}
for i, level := range tessData.GridLevels {
if level != "" {
gridLevels = append(gridLevels, &metadatapb.GridLevel{Level: int32(i + 1), Density: level})
}
}
index.SpatialConfig.Tessellation.GridLevels = gridLevels
// Add cells per object
if tessData.CellsPerObject > 0 {
index.SpatialConfig.Tessellation.CellsPerObject = tessData.CellsPerObject
}
}
}
// Configure storage options - always create storage config
index.SpatialConfig.Storage = &metadatapb.StorageConfig{}
if fillFactor.Valid && fillFactor.Int32 > 0 {
index.SpatialConfig.Storage.Fillfactor = fillFactor.Int32
}
if isPadded.Valid && isPadded.Int32 == 1 {
index.SpatialConfig.Storage.PadIndex = true
}
if allowRowLocks.Valid && allowRowLocks.Int32 == 1 {
index.SpatialConfig.Storage.AllowRowLocks = true
}
if allowPageLocks.Valid && allowPageLocks.Int32 == 1 {
index.SpatialConfig.Storage.AllowPageLocks = true
}
// Configure dimensional properties
index.SpatialConfig.Dimensional = &metadatapb.DimensionalConfig{
DataType: dataType,
Dimensions: 2, // SQL Server spatial indexes are always 2D
}
// Note: Filter definitions for spatial indexes are not currently supported in the proto
// but we've retrieved the data in case it's needed in the future
indexMap[key][indexName.String] = index
}
// Add column to expressions
indexMap[key][indexName.String].Expressions = append(indexMap[key][indexName.String].Expressions, columnName.String)
indexMap[key][indexName.String].Descending = append(indexMap[key][indexName.String].Descending, false) // Spatial indexes don't support descending
}
if err := rows.Err(); err != nil {
return nil, err
}
// Convert to slice format
tableIndexes := make(map[db.TableKey][]*metadatapb.IndexMetadata)
for k, m := range indexMap {
for _, v := range m {
tableIndexes[k] = append(tableIndexes[k], v)
}
}
// Try to get additional spatial index parameters from XML showplan if available
// This is a best-effort attempt to retrieve MAXDOP, SORT_IN_TEMPDB, and ONLINE options
if len(tableIndexes) > 0 {
// Try to enhance spatial indexes with additional properties
if err := enhanceSpatialIndexesWithXMLPlan(txn, tableIndexes); err != nil {
return nil, errors.Wrap(err, "failed to enhance spatial indexes")
}
}
return tableIndexes, nil
}
// enhanceSpatialIndexesWithXMLPlan attempts to extract additional spatial index options
// that are not available in system tables but might be visible in execution plans
func enhanceSpatialIndexesWithXMLPlan(txn *sql.Tx, spatialIndexes map[db.TableKey][]*metadatapb.IndexMetadata) error {
// This is a best-effort function, so we ignore errors
// Collect all spatial indexes that need enhancement
type indexKey struct {
schema string
table string
index string
}
indexMap := make(map[indexKey]*metadatapb.IndexMetadata)
for tableKey, indexes := range spatialIndexes {
for _, index := range indexes {
if index.Type != "SPATIAL" || index.SpatialConfig == nil {
continue
}
key := indexKey{
schema: tableKey.Schema,
table: tableKey.Table,
index: index.Name,
}
indexMap[key] = index
}
}
if len(indexMap) == 0 {
return nil
}
// Batch query all spatial index properties in a single roundtrip
query := `
SELECT
s.name AS schema_name,
o.name AS table_name,
i.name AS index_name,
OBJECTPROPERTY(i.object_id, 'ExecIsAnsiNullsOn') AS ansi_nulls,
OBJECTPROPERTY(i.object_id, 'ExecIsQuotedIdentOn') AS quoted_ident
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN sys.spatial_indexes si ON si.object_id = i.object_id AND si.index_id = i.index_id
WHERE i.type = 4 -- SPATIAL index type
`
rows, err := txn.Query(query)
if err != nil {
return errors.Wrap(err, "failed to query spatial index properties")
}
defer rows.Close()
for rows.Next() {
var schemaName, tableName, indexName sql.NullString
var ansiNulls, quotedIdent sql.NullInt32
if err := rows.Scan(&schemaName, &tableName, &indexName, &ansiNulls, "edIdent); err != nil {
continue
}
if !schemaName.Valid || !tableName.Valid || !indexName.Valid {
continue
}
key := indexKey{
schema: schemaName.String,
table: tableName.String,
index: indexName.String,
}
if index, ok := indexMap[key]; ok {
// If we don't have storage config yet, create a basic one
// These properties give us hints about the index creation context
if (ansiNulls.Valid || quotedIdent.Valid) && index.SpatialConfig.Storage == nil {
index.SpatialConfig.Storage = &metadatapb.StorageConfig{}
// These are indirect indicators but better than nothing
index.SpatialConfig.Storage.AllowRowLocks = true
index.SpatialConfig.Storage.AllowPageLocks = true
}
}
}
if err := rows.Err(); err != nil {
return errors.Wrap(err, "error iterating spatial index properties")
}
return nil
}
func getKeys(txn *sql.Tx, schemas []string) (map[db.TableKey][]*metadatapb.IndexMetadata, error) {
indexMap := make(map[db.TableKey]map[string]*metadatapb.IndexMetadata)
dumpKeySQL := fmt.Sprintf(`
SELECT
s.name AS schema_name,
o.name AS table_name,
i.name,
c.name AS column_name,
ic.is_descending_key,
i.is_primary_key,
i.is_unique_constraint,
i.type_desc,
CAST(p.[value] AS nvarchar(4000)) AS comment
FROM
sys.indexes i
LEFT JOIN sys.index_columns ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
LEFT JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
LEFT JOIN sys.objects co ON co.parent_object_id = i.object_id AND co.name = i.name LEFT JOIN sys.objects o ON o.object_id = i.object_id
LEFT JOIN sys.schemas s ON s.schema_id = o.schema_id
LEFT JOIN sys.extended_properties p ON p.major_id = co.object_id AND p.class = 1 AND p.name = 'MS_Description'
WHERE i.index_id > 0 AND (i.is_primary_key = 1 OR i.is_unique_constraint = 1) AND o.type IN ('U', 'V') AND s.name in (%s)
ORDER BY s.name ASC, i.name ASC, ic.key_ordinal ASC
`, quoteList(schemas))
rows, err := txn.Query(dumpKeySQL)
if err != nil {
return nil, util.FormatErrorWithQuery(err, dumpKeySQL)
}
defer rows.Close()
for rows.Next() {
var schemaName, tableName, indexName, colName, typeDesc, comment sql.NullString
var isDescending, isPrimaryKey, isUniqueConstraint sql.NullBool
if err := rows.Scan(&schemaName, &tableName, &indexName, &colName, &isDescending, &isPrimaryKey, &isUniqueConstraint, &typeDesc, &comment); err != nil {
return nil, err
}
if !schemaName.Valid || !tableName.Valid || !indexName.Valid || !colName.Valid {
continue
}
key := db.TableKey{Schema: schemaName.String, Table: tableName.String}
if _, ok := indexMap[key]; !ok {
indexMap[key] = make(map[string]*metadatapb.IndexMetadata)
}
if _, ok := indexMap[key][indexName.String]; !ok {
index := &metadatapb.IndexMetadata{
Name: indexName.String,
Unique: false,
Primary: false,
IsConstraint: true,
}
if isPrimaryKey.Valid && isPrimaryKey.Bool {
index.Primary = true
index.Unique = true
}
if isUniqueConstraint.Valid && isUniqueConstraint.Bool {
index.Unique = true
}
if typeDesc.Valid {
index.Type = typeDesc.String
}
if comment.Valid {
index.Comment = comment.String
}
indexMap[key][indexName.String] = index
}
indexMap[key][indexName.String].Expressions = append(indexMap[key][indexName.String].Expressions, colName.String)
if isDescending.Valid && isDescending.Bool {
indexMap[key][indexName.String].Descending = append(indexMap[key][indexName.String].Descending, true)
} else {
indexMap[key][indexName.String].Descending = append(indexMap[key][indexName.String].Descending, false)
}
}
if err := rows.Err(); err != nil {
return nil, err
}
tableIndexes := make(map[db.TableKey][]*metadatapb.IndexMetadata)
for k, m := range indexMap {
for _, v := range m {
tableIndexes[k] = append(tableIndexes[k], v)
}
slices.SortFunc(tableIndexes[k], func(a, b *metadatapb.IndexMetadata) int {
if a.Name < b.Name {
return -1
}
if a.Name > b.Name {
return 1
}
return 0
})
}
return tableIndexes, nil
}
// getIndexes gets all indices of a database.
func getKeyAndIndexes(txn *sql.Tx, schemas []string) (map[db.TableKey][]*metadatapb.IndexMetadata, error) {
keys, err := getKeys(txn, schemas)
if err != nil {
return nil, errors.Wrapf(err, "failed to get keys")
}
indexes, err := getIndexes(txn, schemas)
if err != nil {
return nil, errors.Wrapf(err, "failed to get indexes")
}
for k, v := range indexes {
keys[k] = append(keys[k], v...)
}
return keys, nil
}
// getViews gets all views of a database.
func getViews(txn *sql.Tx, columnMap map[db.TableKey][]*metadatapb.ColumnMetadata) (map[string][]*metadatapb.ViewMetadata, error) {
viewMap := make(map[string][]*metadatapb.ViewMetadata)
// Get view comments separately
viewCommentsMap := make(map[int]string)
commentsQuery := `
SELECT
ep.major_id AS object_id,
CAST(ep.value AS NVARCHAR(MAX)) AS comment
FROM sys.extended_properties ep
WHERE ep.minor_id = 0 AND ep.class = 1 AND ep.name = 'MS_Description'
AND ep.major_id IN (SELECT object_id FROM sys.views)`
commentsRows, err := txn.Query(commentsQuery)
if err == nil {
defer commentsRows.Close()
for commentsRows.Next() {
var objectID int
var comment sql.NullString
if err := commentsRows.Scan(&objectID, &comment); err != nil {
continue
}
if comment.Valid {
viewCommentsMap[objectID] = comment.String
}
}
if err := commentsRows.Err(); err != nil {
// Log error but continue - comments are not critical
return nil, errors.Wrap(err, "failed to fetch view comments")
}
}
query := `
SELECT
SCHEMA_NAME(v.schema_id) AS schema_name,
v.name AS view_name,
v.object_id,
m.definition
FROM sys.views v
INNER JOIN sys.sql_modules m ON v.object_id = m.object_id
ORDER BY schema_name, view_name;`
rows, err := txn.Query(query)
if err != nil {
return nil, err
}
defer rows.Close()
for rows.Next() {
view := &metadatapb.ViewMetadata{}
var schemaName string
var objectID sql.NullInt32
var definition sql.NullString
if err := rows.Scan(&schemaName, &view.Name, &objectID, &definition); err != nil {
return nil, err
}
var viewDefinition string
if !definition.Valid {
// Definition is null if the view is encrypted.
// https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-all-sql-modules-transact-sql?view=sql-server-ver16
// https://www.mssqltips.com/sqlservertip/7465/encrypt-stored-procedure-sql-server/
// We will write a pseudo definition in pure comment.
viewDefinition = fmt.Sprintf("/* Definition of view %s.%s is encrypted. */", schemaName, view.Name)
} else {
viewDefinition = definition.String
}
view.Definition = viewDefinition
// Join with comments in Go
if objectID.Valid {
if comment, ok := viewCommentsMap[int(objectID.Int32)]; ok {
view.Comment = comment
}
}
key := db.TableKey{Schema: schemaName, Table: view.Name}
view.Columns = columnMap[key]
viewMap[schemaName] = append(viewMap[schemaName], view)
}
if err := rows.Err(); err != nil {
return nil, err
}
return viewMap, nil
}
func getTriggers(txn *sql.Tx) (map[db.TableKey][]*metadatapb.TriggerMetadata, map[db.TableKey][]*metadatapb.TriggerMetadata, error) {
// Get trigger comments separately
triggerCommentsMap := make(map[int]string)
commentsQuery := `
SELECT
ep.major_id AS object_id,
CAST(ep.value AS NVARCHAR(MAX)) AS comment
FROM sys.extended_properties ep
WHERE ep.minor_id = 0 AND ep.class = 1 AND ep.name = 'MS_Description'
AND ep.major_id IN (SELECT object_id FROM sys.triggers WHERE is_disabled = 0 AND is_ms_shipped = 0)`
commentsRows, err := txn.Query(commentsQuery)
if err == nil {
defer commentsRows.Close()
for commentsRows.Next() {
var objectID int
var comment sql.NullString
if err := commentsRows.Scan(&objectID, &comment); err != nil {
continue
}
if comment.Valid {
triggerCommentsMap[objectID] = comment.String
}
}
if err := commentsRows.Err(); err != nil {
// Log error but continue - comments are not critical
return nil, nil, errors.Wrap(err, "failed to fetch trigger comments")
}
}
query := `
SELECT
st.object_id,
st.name,
STUFF((
SELECT ',' + te.type_desc
FROM sys.trigger_events AS te
WHERE te.object_id = st.object_id
FOR XML PATH('')
), 1, 1, '') AS events,
CASE
WHEN st.type = 'TR' THEN 'AFTER' -- DML triggers created with FOR or AFTER
WHEN st.type = 'TA' THEN 'AFTER' -- DDL triggers
WHEN st.type = 'TI' THEN 'INSTEAD OF' -- INSTEAD OF triggers
ELSE 'UNKNOWN' -- Handle other potential types
END AS timing,
ssm.definition AS body,
so.name AS parentName,
ss.name AS schemaName,
so.type AS objectType
FROM sys.triggers AS st
JOIN sys.sql_modules AS ssm ON st.object_id = ssm.object_id
JOIN sys.objects AS so ON st.parent_id = so.object_id
JOIN sys.schemas AS ss ON so.schema_id = ss.schema_id
WHERE st.is_disabled = 0 AND st.is_ms_shipped = 0 AND st.parent_id <> 0 AND so.type IN ('U', 'V')
ORDER BY st.name;
`
tableTriggers := make(map[db.TableKey][]*metadatapb.TriggerMetadata)
viewTriggers := make(map[db.TableKey][]*metadatapb.TriggerMetadata)
rows, err := txn.Query(query)
if err != nil {
return nil, nil, err
}
defer rows.Close()
for rows.Next() {
var objectID sql.NullInt32
var name, events, timing, parentName, schemaName, parentType string
var body sql.NullString
if err := rows.Scan(&objectID, &name, &events, &timing, &body, &parentName, &schemaName, &parentType); err != nil {
return nil, nil, err
}
bodyString := fmt.Sprintf("/* Definition of trigger %s.%s.%s is encrypted. */", schemaName, parentName, name)
if body.Valid {
bodyString = body.String
}
m := tableTriggers
if parentType == "V" {
m = viewTriggers
}
trigger := &metadatapb.TriggerMetadata{
Name: name,
Event: events,
Timing: timing,
Body: bodyString,
}
// Join with comments in Go
if objectID.Valid {
if comment, ok := triggerCommentsMap[int(objectID.Int32)]; ok {
trigger.Comment = comment
}
}
key := db.TableKey{Schema: schemaName, Table: parentName}
m[key] = append(m[key], trigger)
}
if err := rows.Err(); err != nil {
return nil, nil, err
}
return tableTriggers, viewTriggers, nil
}
// getSequences gets all sequences of a database.
func getSequences(txn *sql.Tx) (map[string][]*metadatapb.SequenceMetadata, error) {
query := `
SELECT
s.name,
seq.name,
tp.name,
CAST(ep.value AS NVARCHAR(MAX)) AS comment
FROM
sys.sequences seq
INNER JOIN
sys.schemas s ON s.schema_id = seq.schema_id
INNER JOIN
sys.types tp ON tp.system_type_id = seq.system_type_id
LEFT JOIN sys.extended_properties ep ON ep.major_id = seq.object_id
AND ep.minor_id = 0
AND ep.class = 1
AND ep.name = 'MS_Description'
ORDER BY s.name, seq.name;`
rows, err := txn.Query(query)
if err != nil {
return nil, err
}
defer rows.Close()
sequenceMap := make(map[string][]*metadatapb.SequenceMetadata)
for rows.Next() {
sequence := &metadatapb.SequenceMetadata{}
var schemaName string
var comment sql.NullString
if err := rows.Scan(&schemaName, &sequence.Name, &sequence.DataType, &comment); err != nil {
return nil, err
}
if comment.Valid {
sequence.Comment = comment.String
}
sequenceMap[schemaName] = append(sequenceMap[schemaName], sequence)
}
if err := rows.Err(); err != nil {
return nil, err
}
return sequenceMap, nil
}
func getProcedures(txn *sql.Tx) (map[string][]*metadatapb.ProcedureMetadata, error) {
procedureMap := make(map[string][]*metadatapb.ProcedureMetadata)
query := `
SELECT
SCHEMA_NAME(ao.schema_id) AS schema_name,
ao.name AS procedure_name,
asm.definition
FROM sys.all_objects ao
INNER JOIN sys.all_sql_modules asm ON asm.object_id = ao.object_id
WHERE ao.type IN ('P', 'RF')
AND ao.is_ms_shipped = 0 AND
(
SELECT major_id
FROM sys.extended_properties
WHERE major_id = ao.object_id
AND minor_id = 0
AND class = 1
AND name = 'microsoft_database_tools_support'
) IS NULL
ORDER BY schema_name, procedure_name;`
rows, err := txn.Query(query)
if err != nil {
return nil, err
}
defer rows.Close()
for rows.Next() {
procedure := &metadatapb.ProcedureMetadata{}
var schemaName string
var definition sql.NullString
if err := rows.Scan(&schemaName, &procedure.Name, &definition); err != nil {
return nil, err
}
var procedureDefinition string
if !definition.Valid {
// Definition is null if the procedure is encrypted.
// https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-all-sql-modules-transact-sql?view=sql-server-ver16
// https://www.mssqltips.com/sqlservertip/7465/encrypt-stored-procedure-sql-server/
// We will write a pseudo definition in pure comment.
procedureDefinition = fmt.Sprintf("/* Definition of procedure %s.%s is encrypted. */", schemaName, procedure.Name)
} else {
procedureDefinition = definition.String
}
procedure.Definition = procedureDefinition
procedureMap[schemaName] = append(procedureMap[schemaName], procedure)
}
if err := rows.Err(); err != nil {
return nil, err
}
return procedureMap, nil
}
func getFunctions(txn *sql.Tx) (map[string][]*metadatapb.FunctionMetadata, error) {
funcMap := make(map[string][]*metadatapb.FunctionMetadata)
// The CAST(...) = 0 means the function is not a system function.
query := `
SELECT
SCHEMA_NAME(ao.schema_id) AS schema_name,
ao.name AS func_name,
asm.definition,
CAST(ep.value AS NVARCHAR(MAX)) AS comment
FROM sys.all_objects ao
INNER JOIN sys.all_sql_modules asm ON asm.object_id = ao.object_id
LEFT JOIN sys.extended_properties ep ON ep.major_id = ao.object_id
AND ep.minor_id = 0
AND ep.class = 1
AND ep.name = 'MS_Description'
WHERE ao.type IN ('FN', 'IF', 'TF')
AND ao.is_ms_shipped = 0 AND
(
SELECT major_id
FROM sys.extended_properties
WHERE major_id = ao.object_id
AND minor_id = 0
AND class = 1
AND name = 'microsoft_database_tools_support'
) IS NULL
ORDER BY schema_name, func_name;`
rows, err := txn.Query(query)
if err != nil {
return nil, err
}
defer rows.Close()
for rows.Next() {
function := &metadatapb.FunctionMetadata{}
var schemaName string
var definition, comment sql.NullString
if err := rows.Scan(&schemaName, &function.Name, &definition, &comment); err != nil {
return nil, err
}
var functionDefinition string
if !definition.Valid {
// Definition is null if the function is encrypted.
// https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-all-sql-modules-transact-sql?view=sql-server-ver16
// https://www.mssqltips.com/sqlservertip/7465/encrypt-stored-procedure-sql-server/
// We will write a pseudo definition in pure comment.
functionDefinition = fmt.Sprintf("/* Definition of function %s.%s is encrypted. */", schemaName, function.Name)
} else {
functionDefinition = definition.String
}
function.Definition = functionDefinition
if comment.Valid {
function.Comment = comment.String
}
funcMap[schemaName] = append(funcMap[schemaName], function)
}
if err := rows.Err(); err != nil {
return nil, err
}
return funcMap, nil
}