def objectMatch(wildcardSearch, objectNamePattern, dbName, cn): if wildcardSearch: sql = "select '%s' as dbName, s.name schema_name, o.name, o.type from %s.sys.objects o inner join %s.sys.schemas s on o.schema_id = s.schema_id where o.name like '%%%s%%\' order by dbname, schema_name, o.name" % (dbName, dbName, dbName, objectNamePattern) cursor = cn.cursor() cursor.execute(sql) rows = cursor.fetchall() if len(rows) > 0: print cursorResultsPrettyPrint(cursor, rows, True) else: sql = "select '%s' as dbName, s.name schema_name, o.name, o.type from %s.sys.objects o inner join %s.sys.schemas s on o.schema_id = s.schema_id where o.name = '%s' order by dbname, schema_name, o.name" % (dbName, dbName, dbName, objectNamePattern) cursor = cn.cursor() cursor.execute(sql) rows = cursor.fetchall() if len(rows) > 0: print cursorResultsPrettyPrint(cursor, rows, True)
def objectMatch(wildcardSearch, objectNamePattern, dbName, cn):
if wildcardSearch:
sql = "select '%s' as dbName, s.name schema_name, o.name, o.type from %s.sys.objects o inner join %s.sys.schemas s on o.schema_id = s.schema_id where o.name like '%%%s%%\' order by dbname, schema_name, o.name" % (
dbName, dbName, dbName, objectNamePattern)
cursor = cn.cursor()
cursor.execute(sql)
rows = cursor.fetchall()
if len(rows) > 0:
print cursorResultsPrettyPrint(cursor, rows, True)
else:
sql = "select '%s' as dbName, s.name schema_name, o.name, o.type from %s.sys.objects o inner join %s.sys.schemas s on o.schema_id = s.schema_id where o.name = '%s' order by dbname, schema_name, o.name" % (
dbName, dbName, dbName, objectNamePattern)
cursor = cn.cursor()
cursor.execute(sql)
rows = cursor.fetchall()
if len(rows) > 0:
print cursorResultsPrettyPrint(cursor, rows, True)
description=
"Get an instance's database file information such as their names and drive location"
)
parser.add_argument(
'-S',
'--server',
help=
'Instance you wish to connect to. myImportantInstance is the default if not specified',
dest='instance',
default='myImportantInstance')
argList = parser.parse_args()
cn = pyodbc.connect(
'DRIVER={SQL Server};SERVER=%s;DATABASE=master;Trusted_Connection=yes' %
argList.instance)
cursor = cn.cursor()
cursor.execute("select name, database_id from sys.databases ")
rows = cursor.fetchall()
sql = ''
for row in rows:
sql = sql + (
"select '%s' dbName, type_desc, physical_name from %s.sys.database_files"
% (row.name, row.name)) + ' union all '
#Remove the last union all because a sql statement ends with union all is not valid
sql = sql.rpartition('union all ')[0]
cursor.execute(sql)
l1rows = cursor.fetchall()
print cursorResultsPrettyPrint(cursor, l1rows, True)
WHEN 4 THEN cast('00:'
+ Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(run_duration,5),1)
+':' + Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(run_duration,6),2)
+':' + Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
END as 'duration' from msdb..sysjobs sj inner
join sysjobhistory sjh on sj.job_id=sjh.job_id
where sjh.step_id = 0 and sjh.run_date >= '%s' and sj.name not in ('syspolicy_purge_history', 'RefreshADGroupInfo')
order by runDate, startTime, jobName, duration
""" % yesterday
for server in serverList:
cn = pyodbc.connect(
'DRIVER={SQL Server};SERVER=%s;DATABASE=msdb;Trusted_Connection=yes' %
server.strip())
cursor = cn.cursor()
cursor.execute(sql)
rows = cursor.fetchall()
toAddrs = ['*****@*****.**']
subj = '%s job status since yesterday' % server.strip()
msg = """\
%s
""" % cursorResultsPrettyPrint(cursor, rows, True)
sendEmail(toAddrs, subj, msg)
def test_cursorResultsPrettyPrint(self):
cn = pyodbc.connect("DRIVER={SQL Server};SERVER=myTestServer;DATABASE=master;Trusted_Connection=yes")
cursor = cn.cursor()
cursor.execute("select name from sys.databases")
self.assertIn("master", mssqlUtility.cursorResultsPrettyPrint(cursor))
+ Left(right(run_duration,3),1)
+':' + right(run_duration,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(run_duration,5),1)
+':' + Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(run_duration,6),2)
+':' + Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
END as 'duration' from msdb..sysjobs sj inner
join sysjobhistory sjh on sj.job_id=sjh.job_id
where sjh.step_id = 0 and sjh.run_date >= '%s' and sj.name not in ('syspolicy_purge_history', 'RefreshADGroupInfo')
order by runDate, startTime, jobName, duration
""" % yesterday
for server in serverList:
cn = pyodbc.connect('DRIVER={SQL Server};SERVER=%s;DATABASE=msdb;Trusted_Connection=yes' % server.strip())
cursor = cn.cursor()
cursor.execute(sql)
rows = cursor.fetchall()
toAddrs = ['*****@*****.**']
subj = '%s job status since yesterday' % server.strip()
msg = """\
%s
""" % cursorResultsPrettyPrint(cursor, rows, True)
sendEmail(toAddrs, subj, msg)
import pyodbc, argparse
from mssqlUtility import cursorResultsPrettyPrint
parser = argparse.ArgumentParser(description="Get an instance's database file information such as their names and drive location")
parser.add_argument('-S', '--server', help='Instance you wish to connect to. myImportantInstance is the default if not specified', dest='instance', default='myImportantInstance')
argList = parser.parse_args()
cn = pyodbc.connect('DRIVER={SQL Server};SERVER=%s;DATABASE=master;Trusted_Connection=yes' % argList.instance)
cursor = cn.cursor()
cursor.execute("select name, database_id from sys.databases ")
rows = cursor.fetchall()
sql = ''
for row in rows:
sql = sql + ("select '%s' dbName, type_desc, physical_name from %s.sys.database_files" % (row.name, row.name)) + ' union all '
#Remove the last union all because a sql statement ends with union all is not valid
sql = sql.rpartition('union all ')[0]
cursor.execute(sql)
l1rows = cursor.fetchall()
print cursorResultsPrettyPrint(cursor, l1rows, True)
