Skip to content

Instantly share code, notes, and snippets.

@mr-deamon
Last active January 5, 2021 09:53
Show Gist options
  • Save mr-deamon/a7aa6b3d0c965007f819d99b74644101 to your computer and use it in GitHub Desktop.
Save mr-deamon/a7aa6b3d0c965007f819d99b74644101 to your computer and use it in GitHub Desktop.
SQL-Analyze

Will display percentage of query-types. If DCLs are present, it will show a warning.

Use it locally or directly from github: wget -qO- https://gist.githubusercontent.com/mr-deamon/a7aa6b3d0c965007f819d99b74644101/raw/f722d6dbac009396fd01974fa449d6ec098c10a8/sqlanalyze.py | python3 - file1.sql file2.sql

#!/usr/bin/python3
import re,sys
regex = {"ddl":r"^(create|alter|drop) (table|index|view)[\s\S]*?;$","dml":r"^(insert|update|delete)[\s\S]*?;$","select":r"^select [\s\S]*?;$","dcl":r"^(alter password|grant|revoke|create synonym) [\s\S]*?;$"}
regex_all = r"^[a-zA-Z][\s\S]*?;$"
args = sys.argv
args.pop(0)
data = ""
for a in args:
with open('./'+a,'r', encoding='utf-8') as file:
data = data+"\r\n"+file.read()
all = len(list(re.finditer(regex_all,data,re.MULTILINE | re.IGNORECASE)))
print("Total {num} Queries".format(num=all))
star = False
for k,v in regex.items():
matches = re.finditer(v,data,re.MULTILINE | re.IGNORECASE)
num = len(list(matches))
comment = "*" if (k=="dcl" and num>0) else ""
star = True if (k=="dcl" and num>0 and star==False) else False
print("{name}: {num} Queries ({perc}%){com}".format(com=comment,num=num, name=k, perc=round(num/all*100,2)))
if(star==True):
print("* DCL-Queries vorhanden, bitte DBA-Team kontaktieren")
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment