How to drop all tables from specific schema in C# SQL Server?

Question

I'm working on a database migration where I need to drop all the tables in a specific schema and then run another script to recreate them from another database.

I'm running into issues with trying to delete specific tables in the proper order.

Is there a SQL query that will order the tables in the correct order so they can be dropped properly?

Here is the code I am trying so far, but the tables are not in the proper order:

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

score 0 · Answer 1 · answered Feb 16 '21 at 22:37

0

Remember that there might be circular references between tables. There might be foreign key constraints from A -> B -> C -> A, for example.

Have a look at the approach in How to drop all tables in a SQL Server database? - but you will have to alter it to work with just your schema.

answered Feb 16 '21 at 22:37

gnud

77,584
5
64
78

I checked this out but I couldn't get it to work. I posted the answer that worked for me! Thanks for your input though! – ttaylor27272727 Feb 17 '21 at 14:11

score 0 · Answer 2 · answered Feb 17 '21 at 14:19

Here is my answer:

Focus on the ORDER BY dependency_level desc and then the where schema_Name = '{0}'

Here is where I found my answer: How to list tables in their dependency order (based on foreign keys)?

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

How to drop all tables from specific schema in C# SQL Server?

2 Answers2