生成数据库默认值脚本
起因:SQL Server数据库的默认值再还原数据库时,莫名其妙的没了。没找到怎么导出数据中的默认。索性写个小程序生成脚本
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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;
}
}
}
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;
}
}
}
我的记事本