using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using System.Data.SqlClient;using System.Diagnostics;namespace SyncDatabaseColumnDescription{    public partial class Form1 : Form    {        public Form1()        {            InitializeComponent();        }        private void Form1_Load(object sender, EventArgs e)        {            var ssb = new SqlConnectionStringBuilder();            ssb.DataSource = @".\SQL2008";            ssb.UserID = "sa";            ssb.Password = "firstelite";            ssb.InitialCatalog = "OMSDB";            using (var connection = new SqlConnection(ssb.ConnectionString))            {                connection.Open();                var tableList = GetTableList(connection);                    foreach (var table in tableList)                    {                        var fieldList = GetFieldList(connection, table);                        foreach (var field in fieldList)                        {                            var desc = GetFieldDescription(connection, table, field);                            if (desc != null)                                Debug.Print(table + " -> " + field + ":" + desc);                        }                    }            }        }        private void UpdateFieldDescription()        {            var ssb = new SqlConnectionStringBuilder();            ssb.DataSource = @".\SQL2008";            ssb.UserID = "**";            ssb.Password = "****";            ssb.InitialCatalog = "OMSDB_WITH_MEMO";            using (var connection = new SqlConnection(ssb.ConnectionString))            {                connection.Open();                var tableList = GetTableList(connection);                ssb.InitialCatalog = "OMSDB";                using (var connection2 = new SqlConnection(ssb.ConnectionString))                {                    connection2.Open();                    var tableList2 = GetTableList(connection2);                    foreach (var table in tableList)                    {                        if (!tableList2.Any(t => t == table))                            continue;                        var fieldList = GetFieldList(connection, table);                        var fieldList2 = GetFieldList(connection2, table);                        foreach (var field in fieldList)                        {                            if (!fieldList2.Any(f => f == field))                                continue;                            var desc = GetFieldDescription(connection, table, field);                            if (string.IsNullOrWhiteSpace(desc))                                continue;                            var desc2 = GetFieldDescription(connection2, table, field);                            if (desc2 != null)                                UpdateFieldDescription(connection2, table, field, desc);                            else                                AddFieldDescription(connection2, table, field, desc);                        }                    }                }            }        }        private List<string> GetTableList(SqlConnection connection)        {            var tableList = new List<string>();            using (var cmd = new SqlCommand(@"                        SELECT TABLE_NAME                         FROM INFORMATION_SCHEMA.TABLES                         WHERE TABLE_TYPE = ‘BASE TABLE‘                         ORDER BY TABLE_NAME", connection))            {                using (var reader = cmd.ExecuteReader())                {                    while (reader.Read())                    {                        tableList.Add(reader[0].ToString());                    }                }            }            return tableList;        }        private List<string> GetFieldList(SqlConnection connection, string tableName)        {            var fieldList = new List<string>();            using (var cmd = new SqlCommand("SELECT * FROM [" + tableName + "] WHERE 1=2", connection))            {                using (var reader = cmd.ExecuteReader())                {                    for (var i = 0; i < reader.FieldCount; i++)                        fieldList.Add(reader.GetName(i));                }            }            return fieldList;        }        private string GetFieldDescription(SqlConnection connection, string tableName, string fieldName)        {            using (var cmd = new SqlCommand(@"SELECT g.[value]                                            FROM    dbo.syscolumns A                                                    INNER JOIN dbo.sysobjects D                                                         ON A.id = D.id                                                            AND D.xtype = ‘U‘                                                            AND D.name <> ‘dtproperties‘                                                    INNER JOIN sys.extended_properties G                                                         ON A.id = G.major_id                                                           AND A.colid = G.minor_id                                            WHERE   D.name = ‘" + tableName + @"‘                                                AND A.name=‘" + fieldName + @"‘", connection))            {                var ret = cmd.ExecuteScalar();                if (ret == null)                    return null;                return ret.ToString();            }        }        private bool AddFieldDescription(SqlConnection connection, string tableName, string filedName, string description)        {            using (var cmd = new SqlCommand(@"EXECUTE   sp_addextendedproperty   N‘MS_Description‘,   ‘" + description.Replace("‘","‘‘")                + "‘,   N‘user‘,   N‘dbo‘,   N‘table‘,   N‘" + tableName + "‘,   N‘column‘,   N‘" + filedName + "‘ ", connection))            {                return cmd.ExecuteNonQuery() > 0;            }        }        private bool UpdateFieldDescription(SqlConnection connection, string tableName, string filedName, string description)        {            using (var cmd = new SqlCommand(@"EXECUTE   sp_updateextendedproperty   N‘MS_Description‘,   ‘" + description.Replace("‘", "‘‘")                + "‘,   N‘user‘,   N‘dbo‘,   N‘table‘,   N‘" + tableName + "‘,   N‘column‘,   N‘" + filedName + "‘ ", connection))            {                return cmd.ExecuteNonQuery() > 0;            }        }    }}