SQL Server: acionar programaticamente a exportação do esquema do banco de dados

Hoje em dia, de longe, a melhor maneira é de código aberto e da Microsoft (em que mundo vivemos):

mssql-scripter

O README diz tudo, então vou apenas citá-lo:

mssql-scripter é o equivalente de linha de comando multiplataforma da experiência amplamente usada do assistente de geração de scripts no SSMS. Você pode usar mssql-scripter no Linux, macOS e Windows para gerar scripts T-SQL de linguagem de definição de dados (DDL) e linguagem de manipulação de dados (DML) para objetos de banco de dados no SQL Server em execução em qualquer lugar, Banco de Dados SQL do Azure e Armazém de Dados SQL do Azure . Você pode salvar o script T-SQL gerado em um arquivo .sql ou canalizá-lo para utilitários nix padrão (por exemplo, sed, awk, grep) para futuras transformações. Você pode editar o script gerado ou verificá-lo no controle de origem e, posteriormente, executar o script em seus processos de implantação de banco de dados SQL existentes e pipelines DevOps com ferramentas de linha de comando SQL multiplataforma padrão, como sqlcmd.

Para automação, você provavelmente seria melhor atendido usando o SQL Server Management Objects (SMO) . Isso exigirá um pouco de programação, mas foi projetado para facilitar a navegação no banco de dados/estrutura SQL. Muitas pessoas o usam para gerar scripts como este.

O SSMS não foi projetado para automação. E mesmo que você consiga fazer parte disso de uma maneira um tanto automatizada, provavelmente não há garantia de que essas definições de configuração permaneçam consistentes em computadores ou até mesmo em usuários no mesmo computador. Mas, usando o SMO (mencionado acima), você tem controle total e pode automatizá-lo facilmente e configurá-lo codificando as configurações ou tornando-o mais flexível colocando algumas/todas as configurações em um arquivo app.config.

Alguns exemplos de programação SMO são:

• Geração automatizada de scripts com Powershell e SMO (mencionado por @Kin em um comentário sobre a pergunta)
• SMO Tutorial 1 de n - Programando objetos de armazenamento de dados
• Introdução ao SQL Server Management Objects (SMO)
• Objetos de banco de dados de script usando SMO (atualizado)

Isso pode ser feito por meio de objetos SMO. Abaixo está um exemplo disso usando C#. Isso usa tabelas, mas pode ser feito para exibições, procedimentos armazenados, etc. alterando o Tableobjeto para View, StoredProcedureou qualquer outro objeto que você deseja criar script. Você pode usar a Namepropriedade para filtrar por nome como no comentário abaixo. Outra opção é o String.StartsWithmétodo de Nameobter apenas objetos com nomes que começam com um determinado texto, ou seja, Name.StartsWith("Prefix"). DDL é gravado em arquivos .sql que podem ser executados ou usados ​​para outros propósitos. Se você quiser automatizar isso, crie um pacote SSIS que contenha uma tarefa de script com esse código. Você pode agendar o pacote para ser executado como um trabalho usando o SQL Agent. Referências a Microsoft.SqlServer.Smo, Microsoft.SqlServer.Management.Sdk.Sfce Microsoft.SqlServer.ConnectionInfodlls também serão necessárias.

        StringBuilder sb = new StringBuilder();
        Server serv = new Server(@"YourSQLSeverInstance");

        //Windows authentication
        serv.ConnectionContext.LoginSecure = true;

        Database db = serv.Databases["YourDatabase"];
        ScriptingOptions scriptOpt = new ScriptingOptions();

        //this can be updated to views, stored procedures, or other objects.
        foreach (Table T in db.Tables)
        {
            //omit system objects
            //can use T.Name to filter by name
            if (!T.IsSystemObject)
            {
                scriptOpt.FileName = @"C:\Test\" + T.Name + ".sql";
                StringCollection sc = T.Script(scriptOpt);
                foreach (string s in sc)
                    Console.WriteLine(s);
            }
        }