c# 遍历 SqlServer 所有表所有列，查找目标数据

在 SqlServer 的 SYSOBJECTS, SYSCOLUMNS 表中存放了所有表的所有列。

 class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("mode:0宽松模式，1严格模式");
            string modestr = Console.ReadLine();
            int mode = 0;
            if (modestr == "1")
            {
                mode = 1;
            }
            string conn = "Data Source = localhost,1433\\SQLEXPRESS; Initial Catalog = aaa; Integrated Security = TRUE";
            List<MyTable> list = GetTableList(conn);
            while (true)
            {
                string str = Console.ReadLine();
                if (str == "quit")
                {
                    return;
                }
                Query(conn, list, str, mode);
                Console.WriteLine("over");
                Console.WriteLine("enter quit to exit");
            }
        }
        static List<MyTable> GetTableList(string connstr)
        {
            using (SqlConnection conn = GetConnection(connstr))
            {
                Dictionary<int, MyTable> dic = new Dictionary<int, MyTable>();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT name,id FROM SYSOBJECTS WHERE XTYPE='U'";
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        string table = reader.GetString(0);
                        int id = reader.GetInt32(1);
                        dic.Add(id, new MyTable() { Table = table });
                    }
                }
                cmd.CommandText = "SELECT name,id FROM SYSCOLUMNS";
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        string column = reader.GetString(0);
                        int id = reader.GetInt32(1);
                        if (dic.TryGetValue(id, out MyTable table))
                        {
                            table.ColumnList.Add(column);
                        }
                    }
                }
                return dic.Values.ToList();
            }
        }
   
        static void Query(string connStr, List<MyTable> list, string str, int mode)
        {
            using (SqlConnection conn = GetConnection(connStr))
            {
                SqlCommand cmd = conn.CreateCommand();
                foreach (MyTable table in list)
                {
                    foreach (string column in table.ColumnList)
                    {
                        try
                        {
                            if (mode == 0)
                            {
                                cmd.CommandText = string.Format("select count(*) from {0} where {1} like '%{2}%'", table.Table, column, str);
                            }
                            else
                            {
                                cmd.CommandText = string.Format("select count(*) from {0} where {1} = '{2}'", table.Table, column, str);
                            }
                            object obj = cmd.ExecuteScalar();
                            if (Convert.ToInt32(obj) > 0)
                            {
                                Console.WriteLine(string.Format("TableName: {0}, ColumnName: {1}", table.Table, column));
                            }
                        }
                        catch (Exception)
                        {

                        }
                    }
                }
            }
        }

        static SqlConnection GetConnection(string connstr)
        {
            SqlConnection conn = new SqlConnection(connstr);
            conn.Open();
            return conn;
        }
    }

    public class MyTable
    {
        public string Table { get; set; }
        public List<string> ColumnList { get; set; } = new List<string>();
    }