起因: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;
}
}
}
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;
}
}
}