django mssql inspectdb all schema and tables

[gustavosooeiro]

Fellows,

I am trying to generate models from all the tables of a MSSQL SERVER 2017 legacy database with a django inspectdb. My problem is that it is been generated just the tables that has dbo prefix. Other tables on other schemas, like HumanResources.Department, are not been generated.

I already tryed to change the Default Schema for the user connected in the database, but no success.

I've seen a few posts that suggests to change the behavior of inspectdb, but I dont know if this is the best solution, or to get it done right.

Any ideas about how to change that and get all tables?

[michiya]

If you want to generate models for existing tables with inspectdb, you will need to change the ownership of the schema for the tables to a database user you use in addition to changing the default schema for the database user. You can use ALTER AUTHORIZATION statement to change the ownership of a schema to a database user like the following.

ALTER AUTHORIZATION ON SCHEMA::HumanResources TO dbuser

And the following is the models I generated from tables belong to HumanResources schema in AdventureWorks2016 database using inspectdb.

# This is an auto-generated Django model module.
# You'll have to do the following manually to clean this up:
#   * Rearrange models' order
#   * Make sure each model has one field with primary_key=True
#   * Make sure each ForeignKey has `on_delete` set to the desired behavior.
#   * Remove `managed = False` lines if you wish to allow Django to create, modify, and delete the table
# Feel free to rename the models, but don't rename db_table values or field names.
from django.db import models

class Department(models.Model):
    departmentid = models.AutoField(db_column='DepartmentID', primary_key=True)  # Field name made lowercase.
    modifieddate = models.DateTimeField(db_column='ModifiedDate')  # Field name made lowercase.

    class Meta:
        managed = False
        db_table = 'Department'

class Employee(models.Model):
    businessentityid = models.IntegerField(db_column='BusinessEntityID', primary_key=True)  # Field name made lowercase.
    nationalidnumber = models.CharField(db_column='NationalIDNumber', unique=True, max_length=15)  # Field name made lowercase.
    loginid = models.CharField(db_column='LoginID', unique=True, max_length=256)  # Field name made lowercase.
    organizationnode = models.TextField(db_column='OrganizationNode', blank=True, null=True)  # Field name made lowercase. This field type is a guess.
    organizationlevel = models.SmallIntegerField(db_column='OrganizationLevel', blank=True, null=True)  # Field name made lowercase.
    jobtitle = models.CharField(db_column='JobTitle', max_length=50)  # Field name made lowercase.
    birthdate = models.DateField(db_column='BirthDate')  # Field name made lowercase.
    maritalstatus = models.CharField(db_column='MaritalStatus', max_length=1)  # Field name made lowercase.
    gender = models.CharField(db_column='Gender', max_length=1)  # Field name made lowercase.
    hiredate = models.DateField(db_column='HireDate')  # Field name made lowercase.
    vacationhours = models.SmallIntegerField(db_column='VacationHours')  # Field name made lowercase.
    sickleavehours = models.SmallIntegerField(db_column='SickLeaveHours')  # Field name made lowercase.
    rowguid = models.CharField(unique=True, max_length=36)
    modifieddate = models.DateTimeField(db_column='ModifiedDate')  # Field name made lowercase.

    class Meta:
        managed = False
        db_table = 'Employee'

class Employeedepartmenthistory(models.Model):
    businessentityid = models.ForeignKey(Employee, models.DO_NOTHING, db_column='BusinessEntityID', primary_key=True)  # Field name made lowercase.
    departmentid = models.ForeignKey(Department, models.DO_NOTHING, db_column='DepartmentID')  # Field name made lowercase.
    shiftid = models.ForeignKey('Shift', models.DO_NOTHING, db_column='ShiftID')  # Field name made lowercase.
    startdate = models.DateField(db_column='StartDate')  # Field name made lowercase.
    enddate = models.DateField(db_column='EndDate', blank=True, null=True)  # Field name made lowercase.
    modifieddate = models.DateTimeField(db_column='ModifiedDate')  # Field name made lowercase.

    class Meta:
        managed = False
        db_table = 'EmployeeDepartmentHistory'
        unique_together = (('businessentityid', 'startdate', 'departmentid', 'shiftid'),)

class Employeepayhistory(models.Model):
    businessentityid = models.ForeignKey(Employee, models.DO_NOTHING, db_column='BusinessEntityID', primary_key=True)  # Field name made lowercase.
    ratechangedate = models.DateTimeField(db_column='RateChangeDate')  # Field name made lowercase.
    rate = models.DecimalField(db_column='Rate', max_digits=19, decimal_places=4)  # Field name made lowercase.
    payfrequency = models.SmallIntegerField(db_column='PayFrequency')  # Field name made lowercase.
    modifieddate = models.DateTimeField(db_column='ModifiedDate')  # Field name made lowercase.

    class Meta:
        managed = False
        db_table = 'EmployeePayHistory'
        unique_together = (('businessentityid', 'ratechangedate'),)

class Jobcandidate(models.Model):
    jobcandidateid = models.AutoField(db_column='JobCandidateID', primary_key=True)  # Field name made lowercase.
    businessentityid = models.ForeignKey(Employee, models.DO_NOTHING, db_column='BusinessEntityID', blank=True, null=True)  # Field name made lowercase.
    resume = models.TextField(db_column='Resume', blank=True, null=True)  # Field name made lowercase. This field type is a guess.
    modifieddate = models.DateTimeField(db_column='ModifiedDate')  # Field name made lowercase.

    class Meta:
        managed = False
        db_table = 'JobCandidate'

class Shift(models.Model):
    shiftid = models.AutoField(db_column='ShiftID', primary_key=True)  # Field name made lowercase.
    starttime = models.TimeField(db_column='StartTime')  # Field name made lowercase.
    endtime = models.TimeField(db_column='EndTime')  # Field name made lowercase.
    modifieddate = models.DateTimeField(db_column='ModifiedDate')  # Field name made lowercase.

    class Meta:
        managed = False
        db_table = 'Shift'
        unique_together = (('starttime', 'endtime'),)