生成数据库默认值脚本

起因：SQL Server数据库的默认值再还原数据库时，莫名其妙的没了。没找到怎么导出数据中的默认。索性写个小程序生成脚本

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using Microsoft.Win32;



namespace createDFsql
{
    /// <summary>
    /// 获取数据库的说有默认值并生成创建相关默认的sql语句。
    /// </summary>
    class Program
    {

        static void Main(string[] args)
        {
            //打开数据库 获取说有的默认值

            Console.WriteLine("请输入SQLServer数据库链接字符传server=;database=;UID=;PWD=");
            string connntionsring = Console.ReadLine();
            //connntionsring = "Server=ADMIN-5604B0BF3;database=cdlg;UID=sa;PWD=sa";
            SqlConnection cn = new SqlConnection(connntionsring);
            cn.Open();
            string comText = @"SELECT SO.NAME,SC.NAME,SM.TEXT FROM 
dbo.sysobjects SO 
INNER JOIN dbo.syscolumns SC 
ON SO.id = SC.id 
LEFT JOIN dbo.syscomments SM 
ON SC.cdefault = SM.id 
WHERE SO.xtype = 'U'  and SM.TEXT is not null";
            SqlDataAdapter da = new SqlDataAdapter(comText, cn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            cn.Close();

            //生成文件存放的路径和名称
            string database = string.Empty;
            string [] temp=connntionsring.Split(new char[]{'=',';'});
            for(int i=0;i<temp.Length;i++)
            {
                if (temp[i]=="database")
                {
                    database = temp[i + 1];
                }
            }
            //System.Environment.GetFolderPath(System.Environment.SpecialFolder.Desktop)
            //string desktopPath=System.Environment.GetFolderPath(System.Environment.SpecialFolder.Desktop);
            RegistryKey folders;
            folders = OpenRegistryPath(Registry.CurrentUser, @"\software\microsoft\windows\currentversion\explorer\shell folders");
            // Windows用户桌面路径
            string desktopPath = folders.GetValue("Desktop").ToString();
            string filename = database + DateTime.Now.ToString("yyyyMMddHHmmss") + ".txt";
            string filePath = desktopPath + "\\" + filename;
            if (!File.Exists(filePath)) 
            {
                File.Create(filePath).Close();
            }


            //向生成文件中提添加sql脚本   
            StreamWriter sw = new StreamWriter(filePath, true, Encoding.GetEncoding("GB2312"));

            for(int i=0;i<ds.Tables[0].Rows.Count;i++)
            {
                string sql = @"if not EXISTS (select a.name  from sysobjects as a inner join syscolumns as b on b.cdefault=a.id inner join sysobjects as c on c.id = b.id 
where c.name='" + ds.Tables[0].Rows[i][0].ToString() + "'and b.name='" + ds.Tables[0].Rows[i][1].ToString() + @"')
begin
ALTER TABLE [dbo].[" + ds.Tables[0].Rows[i][0].ToString() + @"] ADD CONSTRAINT [DF_" + ds.Tables[0].Rows[i][0].ToString() + "_" + ds.Tables[0].Rows[i][1].ToString() + @"] DEFAULT " + ds.Tables[0].Rows[i][2].ToString() + " FOR [" + ds.Tables[0].Rows[i][1].ToString() + @"]
end
;";
                sw.WriteLine(sql);
            }
            sw.Close();

            Console.WriteLine("创建默认脚本完成请查看桌面" + filename + "，按任意键退出");
            Console.ReadKey();
        }
        private static RegistryKey OpenRegistryPath(RegistryKey root, string s)
        {
            s = s.Remove(0, 1) + @"\";
            while (s.IndexOf(@"\") != -1)
            {
                root = root.OpenSubKey(s.Substring(0, s.IndexOf(@"\")));
                s = s.Remove(0, s.IndexOf(@"\") + 1);
            }
            return root;
        }

        

    }
}