c# - 如何从 C# SQL Server 中的特定架构中删除所有表？

Question

我正在进行数据库迁移，我需要删除特定模式中的所有表，然后运行另一个脚本以从另一个数据库重新创建它们。

我在尝试以正确的顺序删除特定表时遇到了问题。

是否有一个 SQL 查询可以按正确的顺序对表进行排序，以便可以正确删除它们？

这是到目前为止我正在尝试的代码，但表格的顺序不正确：

        private void CreateDropStatementsAndRun(string schema)
        {
            string sql = string.Format(@"SELECT table_name
                            FROM information_schema.tables
                            WHERE table_schema = '{0}';", schema);

            var connectionString = ConfigurationManager.ConnectionStrings["TARGET_DefaultConnection"];
            StringBuilder sb = new StringBuilder();

            var listOfTables = new List<string>();

            using (SqlConnection conn = new SqlConnection(connectionString.ConnectionString))
            {
                conn.Open();
                using (var command = new SqlCommand(sql, conn))
                {
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            listOfTables.Add(reader.GetString(0));
                        }
                    }
                }

                foreach (var item in listOfTables)
                {
                    sb.AppendFormat("alter table {0}.{1} nocheck constraint all;", schema, item).AppendLine();
                    sb.AppendFormat("DROP TABLE IF EXISTS {0}.{1};", schema, item).AppendLine();
                }

                using (var cmd = new SqlCommand(sb.ToString(), conn))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                }
            }
        }

标签： c#sql-server

Answer 1

这是我的答案：

专注于ORDER BY dependency_level desc然后where schema_Name = '{0}'

这是我找到答案的地方：如何按依赖顺序列出表（基于外键）？

        private void CreateDropStatementsAndRun(string schema)
        {
            string sql = string.Format(@"WITH cte (lvl, object_id, name, schema_Name) AS
                                          (SELECT 1, object_id, sys.tables.name, sys.schemas.name as schema_Name
                                           FROM sys.tables Inner Join sys.schemas on sys.tables.schema_id = sys.schemas.schema_id
                                           WHERE type_desc = 'USER_TABLE'
                                             AND is_ms_shipped = 0
                                           UNION ALL SELECT cte.lvl + 1, t.object_id, t.name, S.name as schema_Name
                                           FROM cte
                                           JOIN sys.tables AS t ON EXISTS
                                             (SELECT NULL FROM sys.foreign_keys AS fk
                                              WHERE fk.parent_object_id = t.object_id
                                                AND fk.referenced_object_id = cte.object_id )
                                           JOIN sys.schemas as S on t.schema_id = S.schema_id
                                           AND t.object_id <> cte.object_id
                                           AND cte.lvl < 30
                                           WHERE t.type_desc = 'USER_TABLE'
                                             AND t.is_ms_shipped = 0 )
                                        SELECT schema_Name, name, MAX (lvl) AS dependency_level
                                        FROM cte
                                        where schema_Name = '{0}'
                                        GROUP BY schema_Name, name
                                        ORDER BY dependency_level desc,schema_Name, name;", schema);

            var connectionString = ConfigurationManager.ConnectionStrings["TARGET_DefaultConnection"];
            StringBuilder sb = new StringBuilder();

            var listOfTables = new List<string>();

            using (SqlConnection conn = new SqlConnection(connectionString.ConnectionString))
            {
                conn.Open();
                using (var command = new SqlCommand(sql, conn))
                {
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            listOfTables.Add(reader.GetString(1));
                        }
                    }
                }

                if (listOfTables.Count > 0)
                {
                    foreach (var item in listOfTables)
                    {
                        sb.AppendFormat("DROP TABLE IF EXISTS {0}.{1};", schema, item).AppendLine();
                    }

                    using (var cmd = new SqlCommand(sb.ToString(), conn))
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.ExecuteNonQuery();
                    }
                }
            }
        }