3

想把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