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)