Search objects in SQL Server databases with pyodbc


Command line utility to search for objects based on names. Search results will be displayed in well formatted tabular format, left-justified.

1. pyodbc installed;
2. Use trusted authentication by default. Find the relevant code to adjust to login/password as needed;
3. -w for wild card search. Default is exact name match, case insensitive in almost all cases, depending on your SQL Server configuration;
4. If -d is not specified, all databases in instance will be searched, minus the ones that one not accessible at the moment. To specify database(s) you want to search into, use -d followed by database names. Separate them with comma if more than one;
5. Use -S to specify instance;
6. Good sample code for command line parameters processing and pyodbc usage;
7. Won’t work with SQL Server 2000 or lower.

Example 1: python objectSearch.py myobject -S instance1
Result: Database objects that are named myobject in instance1 will be displayed

Example 2: python objectSearch.py -S server1 -w -d db1,db2 myobject
Result: Database objects that has “myobject” in db1 and db2 in server1 will be displayed

[sourcecode language=”python”]
import pyodbc, argparse

def pp(cursor, data=None, check_row_lengths=True):
if not data:
data = cursor.fetchall( )
names = [ ]
lengths = [ ]
rules = [ ]
for col, field_description in enumerate(cursor.description):
field_name = field_description[0]
names.append(field_name)
field_length = field_description[2] or 12
field_length = max(field_length, len(field_name))
if check_row_lengths:
# double-check field length, if it’s unreliable
data_length = max([ len(str(row[col])) for row in data ])
field_length = max(field_length, data_length)
lengths.append(field_length)
rules.append(‘-‘ * field_length)
format = ” “.join([“%%-%ss” % l for l in lengths])
result = [ format % tuple(names), format % tuple(rules) ]
for row in data:
result.append(format % tuple(row))
return “\n”.join(result)

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 pp(cursor, rows, 1)
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 pp(cursor, rows, 1)

parser = argparse.ArgumentParser(description=’SQL Server object search. Case insensitive’)
parser.add_argument(‘-S’, ‘–server’, help=’Instance you wish to connect to. myInstance is the default if not specified’, dest=’instance’, default=’myInstance’)
parser.add_argument(‘-w’, help=’Wildcard search indicator. If specified, LIKE clause will be used for pattern matching. Otherwise it will look for an exact match. Exact match is default’, action=”store_true”, dest=’wild’, default=False)
parser.add_argument(‘-d’, ‘–database’, help=’Database(s) you want to search in. If more than one, separate them by comma only. It will search all databases by default’, action=”store”, dest=”dbs”)
parser.add_argument(‘objectNamePattern’, help=’Object name pattern you want to search for’, action=”store”)

argList = parser.parse_args()

try:
cn = pyodbc.connect(“DRIVER={SQL Server};SERVER=%s;DATABASE=master;Trusted_Connection=yes” % argList.instance)
except:
print “Couldn’t connect to %s. It is down or you might have had a typo.” % argList.instance

if argList.dbs is None:
cursor = cn.cursor()
cursor.execute(“select name from sys.databases where state = 0”)
rows = cursor.fetchall()
for row in rows:
objectMatch(argList.wild, argList.objectNamePattern, row.name, cn)
else:
dbs = argList.dbs.split(‘,’)
for db in dbs:
objectMatch(argList.wild, argList.objectNamePattern, db, cn)
[/sourcecode]

, , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.