检查SQL Server表和表中的行，如果为null，则创建

我将在这里附加一个脚本，该脚本将转储TempTable中对象的所有对象和列。我在与之比较的数据库上运行相同的脚本，并检查哪些对象不存在，哪些列中不存在表，以及哪些列已更改。我很久以前就使用过Delphi应用程序来"升级"我的DB的

我在MASTER数据库上运行此代码。

If Exists(Select 1 from sysobjects where name = 'CheckTables')
  Drop Table CheckTables
GO
Select o.id oid, o.name oname, c.colid cid, c.name cname, t.name ctype, c.xusertype, c.[length] tlength, c.prec cprec, c.scale cscale, isnullable 
into CheckTables
from sysobjects o 
inner join syscolumns c on c.id = o.id
inner join systypes t on t.xusertype = c.xusertype
where o.name not like '%dt_%' and o.category <> 2 and o.type = 'U'
order by o.id, c.colid 
Delete CheckTables where oname = 'CheckTables'

然后我把数据bcp到一个平面文件中当我运行升级时，我在升级数据库上创建了一个具有相同结构的表，并在其中bcp主数据库的数据。

然后我在我的Delphi应用程序中使用了这个脚本来检查发生了什么变化。

Select oname, cname, ctype, IsNull(tlength, 0), IsNull(cprec, 0), IsNull(cscale, 0), ctype, isnullable from CheckTables hr
where cname not in (Select name from syscolumns where id = object_id(oname)
and length = hr.tlength
and xusertype = hr.xusertype
and isnullable = hr.isnullable)
order by oname

这应该会让你继续前进。

如果你需要更多关于C#部分的信息，我可以给你一些代码。

下面是C#代码，让您开始工作。有些东西你必须自己添加，但如果你有困难，请告诉我。

    private void UpgradeDB()
    {
        SqlConnection conn = new SqlConnection("Some Connection String");
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        conn.Open();
        cmd.CommandText = "If 1 = (Select 1 from sysobjects where id = object_id('CheckTables'))'r'n" +
                          "  Drop Table CheckTables'r'n" +
                          "Create Table CheckTables'r'n" +
                          "(oid int,'r'n" +
                          "oname varchar(50),'r'n" +
                          "colid int,'r'n" +
                          "cname varchar(50),'r'n" +
                          "ctype varchar(50),'r'n" +
                          "cxtype int,'r'n" +
                          "tlength int,'r'n" +
                          "cPrec int,'r'n" +
                          "cScale int,'r'n" +
                          "isnullable int";
        cmd.ExecuteNonQuery();
        //BCP your data from MASTER TABLE into the CheckTables of the UpgradeDB
        cmd.CommandText = "Select oname, cname, ctype, IsNull(tlength, 0), IsNull(cprec, 0), IsNull(cscale, 0), isnullable from CheckTables hr'r'n" +
                          "where cname not in (Select name from syscolumns where id = object_id(oname)'r'n" +
                          "and length = hr.tlength'r'n" +
                          "and xusertype = hr.xusertype'r'n" +
                          "and isnullable = hr.isnullable)'r'n" +
                          "order by oname";
        SqlDataReader read = cmd.ExecuteReader();
        string LastTable = "";
        bool TableExists = false;
        bool ColumnExists = false;
        while(read.Read())
        {
            if(LastTable != read[0].ToString())
            {
                LastTable = read[0].ToString();
                TableExists = false;
                if (!CheckIfTableExist(LastTable))
                    TableExists = CreateTable(LastTable);
                else
                    TableExists = true;
            }
            if (TableExists)
            {
                if (!CheckIfColumnExists(read[0].ToString(), read[1].ToString()))
                {
                    CreateColumn(read[0].ToString(), read[1].ToString(), read[2].ToString(),     
                        Convert.ToInt32(read[3].ToString()), Convert.ToInt32(read[4].ToString()), 
                        Convert.ToInt32(read[5].ToString()), Convert.ToBoolean(read[6].ToString()));
                    ColumnExists = false; //You don't want to alter the column if you just created it
                }
                else
                    ColumnExists = true;
                if(ColumnExists)
                {
                    AlterColumn(read[0].ToString(), read[1].ToString(), read[2].ToString(),  
                        Convert.ToInt32(read[3].ToString()), Convert.ToInt32(read[4].ToString()), 
                        Convert.ToInt32(read[5].ToString()), Convert.ToBoolean(read[6].ToString()));
                }
            }
        }
        read.Close();
        read.Dispose();
        conn.Close();
        cmd.Dispose();
        conn.Dispose();
    }
    private bool CheckIfTableExist(string TableName)
    {
        SqlConnection conn = new SqlConnection("Connection String");
        SqlCommand cmd = new SqlCommand();
        conn.Open();
        cmd.Connection = conn;
        cmd.CommandText = "Select IsNull(object_id('" + TableName + "'), 0)";
        Int64 check = Convert.ToInt64(cmd.ExecuteScalar());
        conn.Close();
        cmd.Dispose();
        conn.Dispose();
        return check != 0;
    }
    private bool CreateTable(string TableName)
    {
        try
        {
            //Write your code here to create your table
            return true;
        }
        catch
        {
            return false;
        }
    }
    private bool CheckIfColumnExists(string TableName, string ColName)
    {
        SqlConnection conn = new SqlConnection("Connection String");
        SqlCommand cmd = new SqlCommand();
        conn.Open();
        cmd.Connection = conn;
        cmd.CommandText = "Select IsNull(id, 0) from syscolumns where id = object_id('" + TableName + "') and name = '" + ColName + "'";
        Int64 check = Convert.ToInt64(cmd.ExecuteScalar());
        conn.Close();
        cmd.Dispose();
        conn.Dispose();
        return check != 0;
    }
    private void CreateColumn(string TableName, string ColName, string ColType, int Length, int Precision, int Scale, bool Nullable)
    {
        //Write your code here to create your column
    }
    private void AlterColumn(string TableName, string ColName, string ColType, int Length, int Precision, int Scale, bool Nullable)
    {
        //Write your code here to alter your column
    }