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

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105

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>();    }