Here is a small piece of code I created to clone a database but only using the first 10 records for each table

[OscarAgreda]

//
var sqlQueryStr = "";
var sqlQueryTruncate = "";
foreach (var table in schema.Tables)
{
// I dont want to clone tables that meet this condition
var goodTable = false;
if (!table.Name.StartsWith(""))
{
goodTable = true;

            }

            var goodColumns = false;
            foreach (var column in table.Columns)
            {
            // i use this a condition each table i want must have
                if (column.Name == "RowId")
                {
                    goodColumns = true;

                }
          
            }

            if (goodColumns && goodTable)
            {

                var className = table.NetName;
                var cw = new ClassWriter(table, _codeWriterSettings);
                var txt = cw.Write();

                sqlQueryTruncate = sqlQueryTruncate + "TRUNCATE TABLE [New_Database_Name].[dbo].["+table.Name+"]\r\nGo\r\n";

                sqlQueryStr = sqlQueryStr + "\r\n\r\nSET IDENTITY_INSERT [New_Database_Name].[dbo].["+table.Name+"] ON \r\nGo\r\n\r\nINSERT TOP (10) \r\nINTO [New_Database_Name].[dbo].["+table.Name+"] (";

                var insertStr = "";
                foreach (var column in table.Columns)
                {
                    insertStr = insertStr + "["+column.Name+"], ";

                }

                insertStr = insertStr.Substring(0, insertStr.Length - 2);
                sqlQueryStr = sqlQueryStr + "\r\n\t" + insertStr + ")";
                sqlQueryStr = sqlQueryStr + "\r\nSELECT\r\n\t" + insertStr;
                sqlQueryStr = sqlQueryStr + "\r\nFROM\r\n\t[OldBig_Database_Name].[dbo].["+table.Name+"]\r\nORDER BY\r\n\tRowId\r\nGo\r\n\r\nSET IDENTITY_INSERT [New_Database_Name].[dbo].["+table.Name+"] OFF\r\nGo";


            }



        }

        var insertTop10Script = WriteSqlFile(directory, "SqlInsertTop10", sqlQueryStr);
        var truncateScript = WriteSqlFile(directory, "TruncateTables", sqlQueryTruncate);

        //

[OscarAgreda]

There is nothing like that on the web, i searched and nothing , even the very expensive tools don't do that

you are welcome to add it to the project under
public void Execute(DirectoryInfo directory)
{

[OscarAgreda]

you may want to add it (with a check mark to be checked) to the project --
let's say you have a huge database to clone into another small one because you don't want all the data from the old one, and also don't need all tables.

For example from a table called RobotLogic, you may need all of the records, , also you may need all data for tables where the table name ends with the word "Type", but then for other tables you only need a few rows of data, for example, you may just need one record for the table called AuditLog, and 15 records for the tables that meet another condition, etc.

Using this awesome project you can create all of your logic in C#, and with that logic generate the SQL server script then run the T-SQL script on MS SQL Console.

[martinjw]

We use this to generate scripts (which are executed by other db admin tools), to clone/refresh databases

            using (var connection = new SqlConnection(connectionString))
            {
                var dr = new DatabaseReader(connection);
                var schema = dr.ReadAll();
                //custom extension to clear out not required tables using RemoveTable()
                schema.RemoveTables(TableFilter);

                var factory = new DdlGeneratorFactory(SqlType.SqlServer);
                var tableGenerator = factory.AllTablesGenerator(schema);
                tableGenerator.IncludeSchema = false;
                var ddl = tableGenerator.Write();

                var scriptWriter = new DatabaseSchemaReader.Data.ScriptWriter
                {
                    PageSize = 200 //how many records per table
                };
                var dml = new StringBuilder();
                foreach (var table in SchemaTablesSorter.TopologicalSort(schema))
                {
                    dml.AppendLine(scriptWriter.ReadTable(table, connection)); //insert sql
                }

                File.WriteAllText(pathDdl, ddl);
                File.WriteAllText(pathDml, dml.ToString());
            }

The CopyToSQLite project contains similar code to clone say a SqlServer db to SQLite - again, I've used bits of that to clone to other SqlServer.

[OscarAgreda]

@martinjw
I think is of extreme importance, because now everybody doing AI model training, and using SQL Lite because LangChain likes to work with SQLite at this point. this is pure gold !!

[OscarAgreda]

I did not know about this awesome sauce!