想把SQL Server上的Stored Procedure/User-Defined Function的內容存成檔案備存。

利用INFORMATION_SCHEMA.ROUTINES檢視查詢,就可以調出所有的SP/UDF資訊,唯一要留意的地方是ROUTINES檢視用來存放SP/UDF的ROUTINE_DEFINITION欄位有NVARCHAR(4000)的限制,所以改用OBJECT_DEFINTION傳回NVARCHAR(MAX),才能避免部分又臭又長的SP/UDF被腰斬。

        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());
                }
            }
        }

程式很短,執行起來也超快。不用一秒鐘,SQL Server上MyDBName裡的SP/UDF就一一被匯出成.SQL檔案囉!


Comments

# by ChrisTorng

我都使用 VS 的資料庫專案,可以比對與實體資料庫的差異,由資料庫專案更新至實體資料庫,或者由實體資料庫轉回資料庫專案 (我都使用後者)。也利用它比對開發資料庫與正式資料庫的差異,並將所有開發資料庫的修改同步至正式資料庫中。可以比對 Schema 及資料內容並同步。

# by ChrisTorng

對了,再把資料庫專案簽入至原始檔控制,這樣還可以保留舊版,比對新舊版本的差異等等...

# by Jeffrey

to ChrisTorng, 哇,看起來又是一件我錯過的好東西,謝謝分享~~

Post a comment