static void Main(string[] args)
{
string path = @"D:\Dump";
using (var cn = new SqlConnection(cs))
{
DataTable t = new DataTable();
cn.Open();
var cmd = cn.CreateCommand();
//用OBJECT_DEFINITION克服ROUTINE_DEFINITION只有NVARCHAR(4000)的問題
cmd.CommandText = @"
SELECT ROUTINE_NAME, ROUTINE_TYPE,
OBJECT_DEFINITION(OBJECT_ID(ROUTINE_SCHEMA + '.' + ROUTINE_NAME)) AS BODY
FROM MyDBName.INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_BODY = 'SQL'";
t.Load(cmd.ExecuteReader());
cn.Close();
foreach (DataRow row in t.Rows)
{
string body = row["BODY"].ToString();
string routineName = row["ROUTINE_NAME"].ToString();
//可加入自訂的篩選條件
if (routineName.StartsWith("dt_")) continue;
File.WriteAllText(
Path.Combine(
path,
string.Format("{0}-{1}.sql", row["ROUTINE_TYPE"], routineName)),
row["BODY"].ToString());
}
}
}