SQLServer中批量导出的存储过程明细

使用sqlserver management客户端可以导出存储过程的明细，但是如果要大批量导出则很不方便，这种情况下，可以使用python脚本来实现

sql写法

代码语言：txt复制

step1 先列出当前库下有哪些存储过程
SELECT '['   SCHEMA_NAME(schema_id)   '].['   name   ']' FROM sys.objects WHERE type = 'P' AND is_ms_shipped = 0;


step2 对上面的每个存储过程执行输出的操作
DECLARE @procname NVARCHAR(MAX) = '上一步获取到的存储过程的名称';  -- 例如我这里填的是 [dbo].[uspTryCatchTest]
DECLARE @procdef NVARCHAR(MAX);

SELECT @procdef = OBJECT_DEFINITION(OBJECT_ID(@procname));

IF @procdef IS NOT NULL
BEGIN
    PRINT @procdef;
END
ELSE
BEGIN
    PRINT 'Stored Procedure does not exist.';
END

转成python的代码如下：

代码语言：txt复制

# pip install pymssql==2.2.7

import pymssql

host = "127.0.0.1"
port = "1433"
user = "sa"
passwd = "Abcd1234"
db = "AdventureWorks2019"  # 这个是待遍历的库名

conn = pymssql.connect(
    host=host,
    port=port,
    user=user,
    password=passwd,
    database="master",
    charset="utf8",
)
cursor = conn.cursor()

detail_sql = (
    "USE ["
      str(db)
      "];"
      "SELECT '['   SCHEMA_NAME(schema_id)   '].['   name   ']' FROM sys.objects WHERE type = 'P' AND is_ms_shipped = 0;"
)

try:
    cursor.execute(detail_sql)
except Exception as e:
    print(str(e))

res = cursor.fetchall()
for i in res:
    print(f"当前导出的存储过程名称为：{i[0]}")

    try:
        cursor.execute(f"select OBJECT_DEFINITION(OBJECT_ID('{i[0]}'))")
    except Exception as e:
        print(f"导出{i[0]}存储过程执行失败 {str(e)}")
        continue
    produce_res = cursor.fetchall()
    with open(str(i[0])   ".txt", "w") as f:
        f.writelines(produce_res[0])

云数据库SQLServer

0 人点赞