Get the Names of Stored Procedures that Reference a Given Table in SQL Server

One of my colleagues sent me a very handy bit of code this morning that I thought would be helpful to post.

Below are two options which, when run against the database, should return the names of all stored procedures that reference the named table.

----Option 1
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tablename%'

----Option 2
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'

I am told that the functionality of the first option has been verified.

Sending SQL Server Stored Procedure Results as an HTML Table in Email Using Python

By modifying the code I used for sending email attachments, I am able to execute a SQL Server stored proc and format the results as an HTML table in an email. Also, I use the sys.exit command if there are no rows returned so that the email is not sent if there is no data returned.

# disabled username / password logon for use in our Exchange environment

######### Setup your stuff here #######################################

host = 'smtp.whatever.com' # specify port, if required, using a colon and port number following the hostname

fromaddr = 'donotreply@whatever.com' # must be a vaild 'from' address in your environment
toaddr  = ['donotreply@whatever.com'] # list of email addresses
ccaddr  = [''] # list of email addresses
bccaddr  = ['email@whatever.com'] # list of email addresses
replyto = fromaddr # unless you want a different reply-to

# username = 'username' # not used in our Exchange environment
# password = 'password' # not used in our Exchange environment

msgsubject = 'Email Subject'
htmlmsgtext = "

Report for " # text with appropriate HTML tags connstring = 'DRIVER={SQL Server};SERVER=servername;DATABASE=dbname;UID=userid;PWD=password' ######### In normal use nothing changes below this line ############### import smtplib, pyodbc, sys from email.MIMEMultipart import MIMEMultipart from email.MIMEText import MIMEText from email.Utils import COMMASPACE from HTMLParser import HTMLParser import datetime as dt yesterday = dt.datetime.now() - dt.timedelta(days=1) date = "'" + yesterday.strftime('%m-%d-%Y') + "'" htmlmsgtext = htmlmsgtext + date + "

" conn=pyodbc.connect(connstring) cursor=conn.cursor() cursor.execute("exec dbo.sp_RandomStoredProc " + date) rows = cursor.fetchall() column_names = [d[0] for d in cursor.description] cursor.close() del cursor if len(rows) == 0: sys.exit htmlmsgtext = htmlmsgtext + 'n' htmlmsgtext = htmlmsgtext + 'n' for column_name in column_names: htmlmsgtext = htmlmsgtext + '' htmlmsgtext = htmlmsgtext + 'nn' for row in rows: for column in row: htmlmsgtext = htmlmsgtext + '' htmlmsgtext = htmlmsgtext + 'nn' htmlmsgtext = htmlmsgtext + '
' + column_name + '
' + str(column) + '
n' # A snippet - class to strip HTML tags for the text version of the email class MLStripper(HTMLParser): def __init__(self): self.reset() self.fed = [] def handle_data(self, d): self.fed.append(d) def get_data(self): return ''.join(self.fed) def strip_tags(html): s = MLStripper() s.feed(html) return s.get_data() ######################################################################## try: # Make text version from HTML - First convert tags that produce a line break to carriage returns msgtext = htmlmsgtext.replace('
',"r").replace('
',"r").replace('

',"r") # Then strip all the other tags out msgtext = strip_tags(msgtext) # necessary mimey stuff msg = MIMEMultipart() msg.preamble = 'This is a multi-part message in MIME format.n' msg.epilogue = '' body = MIMEMultipart('alternative') body.attach(MIMEText(msgtext)) body.attach(MIMEText(htmlmsgtext, 'html')) msg.attach(body) msg['From'] = fromaddr msg['To'] = COMMASPACE.join(toaddr) msg['CC'] = COMMASPACE.join(ccaddr) msg['Subject'] = msgsubject msg['Reply-To'] = replyto print 'To addresses follow:' print toaddr # The actual email sendy bits server = smtplib.SMTP(host) server.set_debuglevel(False) # set to True for verbose output recipients = toaddr + ccaddr + bccaddr # Comment this block and uncomment the below try/except block if TLS or user/pass is required. server.sendmail(fromaddr, recipients, msg.as_string()) print 'Email sent.' server.quit() # bye bye # try: # # If TLS is used # server.starttls() # server.login(username,password) # server.sendmail(fromaddr, recipients, msg.as_string()) # print 'Email sent.' # server.quit() # bye bye # except: # # if tls is set for non-tls servers you would have raised an exception, so.... # server.login(username,password) # server.sendmail(fromaddr, recipients, msg.as_string()) # print 'Email sent.' # server.quit() # bye bye except: print "Email NOT sent to %s successfully. ERR: %s %s %s " % (str(toaddr), str(sys.exc_info()[0]), str(sys.exc_info()[1]), str(sys.exc_info()[2]) ) #just in case
%d bloggers like this: