def filter_computed_columns(table, columns): columns_str = ','.join(f"'{column}'" for column in columns) query = ( f"SELECT *, TYPE_NAME(system_type_id) as type_name FROM sys.columns " f"WHERE name in ({columns_str}) AND object_id = OBJECT_ID('{table}') " "AND is_computed = 1") return [column['name'] for column in db.query(query)]
def get_tables(): table_names: Iterator[str] = (table['TABLE_NAME'] for table in db.query('sp_tables') if table['TABLE_TYPE'] == 'TABLE') tables = [ table for table in table_names if not table.startswith('_') and not table[0].islower() and table not in REDUNDANT_TABLES ] return tables
def find_tables_fks(tables): table_fks = defaultdict(list) for table in tables: fks = db.query(f'sp_fkeys {table}') for fk in fks: fk_table = fk['FKTABLE_NAME'] fk_column = fk['FKCOLUMN_NAME'] table_fks[fk_table].append(fk_column) return table_fks
def get_computed_column_formula(table, column): query = f"SELECT * FROM sys.computed_columns WHERE name = '{column}' and object_id = OBJECT_ID('{table}')" computed_columns = db.query(query) computed_column = computed_columns.first() formula: str = computed_column['definition'] name = computed_column['name'] if formula.startswith('(left') and name.startswith('__'): related_computed_column = name.strip('_') result_formula = f"(CONVERT(VARCHAR(32), HASHBYTES('MD5', {related_computed_column}), 2))" else: result_formula = formula return result_formula
def identify_columns_types(table, columns): def _format_column_type(info): if info['is_computed']: formula = get_computed_column_formula(table, info['name']) return f'as {formula}' elif info['type_name'] in CHAR_TYPES: return F"{info['type_name']}({info['max_length']})" else: return info['type_name'] columns_str = ','.join(f"'{column}'" for column in columns) query = ( f"SELECT *, TYPE_NAME(system_type_id) as type_name FROM sys.columns " f"WHERE name in ({columns_str}) AND object_id = OBJECT_ID('{table}')" ) infos = db.query(query) return { info['name']: _format_column_type(info) for info in infos }
def drop_index(index): *_, table = index['name'].split('_') index_name = index['name'] if index['is_unique_constraint']: return f'ALTER TABLE dbo.{table} DROP CONSTRAINT {index_name};' else: return f"DROP INDEX {index_name} ON {table};" if __name__ == '__main__': with open('data/0_tables.json') as f: tables = json.load(f) indexes = db.query( 'select * from sys.indexes where name is not null and is_primary_key = 0 order by name;' ) table_indexes = [ index for index in indexes if is_table_index(index, tables) ] drop_indexes_str = '\nGO\n'.join(map(drop_index, table_indexes)) with open(f'sql/2_drop_constraints [{DATABASE}].sql', 'w') as f: print(f'''use {DATABASE}; GO sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all' GO ALTER TABLE dbo.Properties DROP CONSTRAINT DF_Properties_TableName; GO ALTER TABLE dbo.HeadTabl DROP CONSTRAINT DF_HeadTabl_Expert;
def list_related_tables(table): return [ (fk['FKTABLE_NAME'], fk['FKCOLUMN_NAME']) for fk in db.query(f"exec sp_fkeys '{table}'").all() ]
def get_text_columns(table): return [ column['COLUMN_NAME'] for column in db.query(f'exec sp_columns {table}') if column['TYPE_NAME'] in TEXT_TYPES ]
context_builder = partial(build_context_for_table, invariant_with_suffix=invariant_with_suffix) contexts = list(map(context_builder, tables_with_properties)) return contexts if __name__ == '__main__': with open('data/1_tables_with_types.json') as f: tables_with_types = json.load(f) HAS_RU_COLUMNS_tables = [ table for table, type_ in tables_with_types.items() if table not in ['Bibliogr', 'LastModified', 'Properties', 'HeadTabl', 'SingTabl'] and type_ == 'HAS_RU_COLUMNS' ] table_properties = { property_['TableName']: property_ for property_ in db.query(PROPERTIES_QUERY) } contexts = _build_contexts(HAS_RU_COLUMNS_tables, table_properties) NO_RU_COLUMNS_tables = {'Wavepure', 'SistTabl', 'LitrTabl'} contexts.extend( _build_contexts(NO_RU_COLUMNS_tables, table_properties, invariant_with_suffix=False)) with open('data/page_contexts.json', 'w', encoding='utf-8') as f: json.dump(contexts, f)
def get_rows(table): return db.query(f'SELECT * FROM {table}')
def get_columns(table): return [ column['COLUMN_NAME'] for column in db.query(f'sp_columns {table}') ]
def find_table_pks(table): return [pk['COLUMN_NAME'] for pk in db.query(f'sp_pkeys {table}').all()]