def check_index(workbook,ddl_sheet,db_a, db_b):
query = "SELECT DISTINCT o.name as table_nm,i.name as index_nm, 'Y' as val FROM SYS.OBJECTS O JOIN SYS.index_columns IC ON IC.OBJECT_ID = O.OBJECT_ID JOIN SYS.COLUMNS C ON IC.column_id = C.column_id and C.OBJECT_ID = O.OBJECT_ID JOIN SYS.INDEXES I ON I.OBJECT_ID = O.OBJECT_ID AND I.index_id = IC.index_id JOIN information_schema.tables tab ON O.NAME = tab.TABLE_NAME AND tab.table_schema = 'dbo' WHERE o.type = 'U' AND (table_name LIKE 'D[_]%' OR table_name LIKE 'B[_]%' OR table_name LIKE 'R[_]%' OR table_name LIKE 'F[_]%' OR table_name LIKE 'RPT[_]%') ORDER BY o.name,i.name"
new_sheet = workbook.copy_worksheet(ddl_sheet)
new_sheet.title = "INDEX"
index_a = DB.query_db_pandas(query, db_a)
index_b = DB.query_db_pandas(query, db_b)
merge_index(index_a,index_b,new_sheet)
def check_sp(workbook,sp_sheet,db_a, db_b):
query = "SELECT CASE a.[type] WHEN 'P' THEN 'Stored Procedures' WHEN 'V' THEN 'Views' WHEN 'AF' THEN 'Aggregate function' END AS 'Type', a.name, b.[definition] FROM sys.all_objects a, sys.sql_modules b WHERE a.is_ms_shipped=0 AND a.object_id = b.object_id AND a.[type] IN ('P','V','AF') order by a.[type], a.[name] ASC"
new_sheet = workbook.copy_worksheet(sp_sheet)
new_sheet.title = "SP"
sp_a = DB.query_db_pandas(query, db_a)
sp_b = DB.query_db_pandas(query, db_b)
merge_sp(sp_a,sp_b,new_sheet)
def check_ddl(workbook,ddl_sheet,db_a, db_b):
query = "SELECT table_name, column_name, ordinal_position , data_type, COALESCE(character_maximum_length,numeric_precision,datetime_precision) data_length, numeric_scale, is_nullable FROM information_schema.columns WHERE table_schema = 'dbo' AND (table_name LIKE 'D[_]%' OR table_name LIKE 'B[_]%' OR table_name LIKE 'R[_]%' OR table_name LIKE 'F[_]%' OR table_name LIKE 'RPT[_]%') ORDER BY table_name"
new_sheet = workbook.copy_worksheet(ddl_sheet)
new_sheet.title = "DDL"
ddl_a = DB.query_db_pandas(query, db_a)
ddl_b = DB.query_db_pandas(query, db_b)
merge_ddl(ddl_a,ddl_b,new_sheet)
def check_index(workbook, ddl_sheet, contract):
print("checking " + contract + " index")
query = "SELECT DISTINCT o.name as table_nm,i.name as index_nm, 'Y' as val FROM " + contract + ".SYS.OBJECTS O JOIN " + contract + ".SYS.index_columns IC ON IC.OBJECT_ID = O.OBJECT_ID JOIN " + contract + ".SYS.COLUMNS C ON IC.column_id = C.column_id and C.OBJECT_ID = O.OBJECT_ID JOIN " + contract + ".SYS.INDEXES I ON I.OBJECT_ID = O.OBJECT_ID AND I.index_id = IC.index_id JOIN " + contract + ".information_schema.tables tab ON O.NAME = tab.TABLE_NAME AND tab.table_schema = 'dbo' WHERE o.type = 'U' AND (table_name LIKE 'D[_]%' OR table_name LIKE 'B[_]%' OR table_name LIKE 'R[_]%' OR table_name LIKE 'F[_]%' OR table_name LIKE 'RPT[_]%') ORDER BY o.name,i.name"
new_sheet = workbook.copy_worksheet(ddl_sheet)
new_sheet.title = contract + "_INDEX"
dev = DB.query_db_pandas(query, acct.DEV_CO_HF_MART)
qa = DB.query_db_pandas(query, acct.QA_CO_HF_MART)
uat = DB.query_db_pandas(query, acct.UAT_CO_HF_MART)
prod = DB.query_db_pandas(query, acct.PROD_CO_HF_MART)
#merge_index(dev,qa,uat,new_sheet)
merge_index(dev, qa, uat, new_sheet, prod)
def check_view(workbook, ddl_sheet, contract):
print("checking " + contract + " view")
query = "SELECT CASE a.[type] WHEN 'P' THEN 'Stored Procedures' WHEN 'V' THEN 'Views' WHEN 'AF' THEN 'Aggregate function' END AS 'Type', a.name, b.[definition] FROM " + contract + ".sys.all_objects a, " + contract + ".sys.sql_modules b WHERE a.is_ms_shipped=0 AND a.object_id = b.object_id AND a.[type] IN ('P','V','AF') order by a.[type], a.[name] ASC"
new_sheet = workbook.copy_worksheet(ddl_sheet)
new_sheet.title = contract + "_VIEW"
dev = DB.query_db_pandas(query, acct.DEV_CO_HF_MART)
qa = DB.query_db_pandas(query, acct.QA_CO_HF_MART)
uat = DB.query_db_pandas(query, acct.UAT_CO_HF_MART)
prod = DB.query_db_pandas(query, acct.PROD_CO_HF_MART)
#merge_view(dev,qa,uat,new_sheet)
merge_view(dev, qa, uat, new_sheet, prod)
def check_ddl(workbook, ddl_sheet, contract):
print("checking " + contract + " ddl")
query = "SELECT table_name, column_name, ordinal_position , data_type, COALESCE(character_maximum_length,numeric_precision,datetime_precision) data_length, numeric_scale, is_nullable FROM " + contract + ".information_schema.columns WHERE table_schema = 'dbo' AND (table_name LIKE 'D[_]%' OR table_name LIKE 'B[_]%' OR table_name LIKE 'R[_]%' OR table_name LIKE 'F[_]%' OR table_name LIKE 'RPT[_]%') ORDER BY table_name"
new_sheet = workbook.copy_worksheet(ddl_sheet)
new_sheet.title = contract + "_DDL"
dev = DB.query_db_pandas(query, acct.DEV_CO_HF_MART)
qa = DB.query_db_pandas(query, acct.QA_CO_HF_MART)
uat = DB.query_db_pandas(query, acct.UAT_CO_HF_MART)
prod = DB.query_db_pandas(query, acct.PROD_CO_HF_MART)
#merge_ddl(dev,qa,uat,new_sheet)
merge_ddl(dev, qa, uat, new_sheet, prod)