[Python]代碼    

#!/usr/bin/python

coding=gbk
import os
import re
import time
import datetime
import operator
import pyodbc

import sys
"""

backup procedure,view,function
"""
def ado_cmd(src, sql):
    db = pyodbc.connect(src)

    cursor = db.cursor() 
    cursor.execute(sql)
    db.commit()
    db.close()
def ado_sel(src, sql):
    db = pyodbc.connect(src)

    cursor = db.cursor()

    cursor.execute(sql)
    ds = cursor.fetchall()
    db.close() 
    return ds
def getprocedure(src, pname):
    sql = "EXEC Sp_HelpText '" + pname + "';"
    ds = ado_sel(src, sql)
    text = ''
    index = 0
    try:

        for dr in ds:

        #print(str(dr[0]))
        text = text + str(dr[0])
        text = text.replace("\r\n", "") + "\n"
        index = index + 1
except Exception as e:
    print("查詢存儲過程出錯：" + pname + "  [line:"+str(index)+"] ")
    print(e)

return text



if name == 'main':

src = 'DRIVER={SQL Server};SERVER=服務器;DATABASE=數據庫;UID=用戶名;PWD=密碼'  
# p procedure; v view; fn function
sql = "SELECT [name],[type] FROM sysobjects WHERE type IN('p','v','fn') order by name"
#src = 'DSN=sampledb;UID=dba;pwd=sql'  

now = datetime.datetime.now()
path = now.strftime('%Y-%m-%d')
if os.path.exists(path):
    for i in range(98,122):
        new_path = path + "_" + chr(i) 

        if not os.path.exists(new_path):  
            path = new_path
            break
        else:
            print(new_path + '已存在')            

os.makedirs(path)   #創建新文件夾
sv  = "View"
sp  = "Prodecure"
sfn = "Function"
os.makedirs(path + '/' + sv)
os.makedirs(path + '/' + sp)
os.makedirs(path + '/' + sfn)

ds = ado_sel(src, sql)
i_count = len(ds)
print("count=" + str(i_count))
for dr in ds:    
    p2 = ""
    pname = str(dr[0])
    typ   = str(dr[1])
    typ   = typ.strip()
    if   typ == "V"  : p2 = sv
    elif typ == "P"  : p2 = sp
    elif typ == "FN" : p2 = sfn
    print(typ + ", " + p2 + ", " + pname)

    text = getprocedure(src, pname)

    filename = pname + ".sql"

    file1 = open(path + "/" + p2 + "/" + filename, "w")
    file1.write(text + "\n")
    file1.close        

</pre>
 QQ截圖20150910150454.png     

 
  
 
 QQ截圖20150910150517.png