Script to create database xmla file

Question

I'm looking for an automatic method for generating the file that is created in the first screenshot to see if it can be done:

It is an xmla file detailing the structure of the tables within the database. Preferably a solution in C# or PowerShell, but any method would be appreciated.

The second image is the format of the desired output file. This is easily generated from SSMS but I would like to run a script to generate this instead:

What have you tried so far? What does the xmla file contain if you try to open it with a text editor? XML? SQL? — Mathias R. Jessen, Jul 22 '21 at 09:33
Does this answer in StackExchange help? https://dba.stackexchange.com/questions/51129/script-out-xmla-for-all-databases — JohnnyPrognose, Jul 22 '21 at 09:38

mohabbati · Answer 1 · 2021-07-22T13:10:05.950

If you are using SQL Server Analysis Services database:

In C# you can use Microsoft.AnalysisServices.retail.amd64 package as follow:

Server yourServer = new Server();
yourServer.Connect(@"Data Source=ServerName\InstanceName");

MajorObject[] yourDatabase = new MajorObject[1];
yourDatabase[0] = yourServer.Databases["DbName"];

var scripter = new Scripter();
XmlWriter xmlWriter = XmlWriter.Create(@"FilePath")

Take a look at the following link. The question is similar to your one:

Need c# script for SMO which creates XMLA script from analysis service database

If you are using SQL Server database:

In C# you can use Microsoft.SqlServer.SqlManagementObjects package to generate script for database objects as follow:

SQL Server Management Objects (SMO) Programming Guide

Server yourServer = new Server("ServerName");

Scripter scripter = new Scripter(yourServer);

Database yourDb = yourServer.Databases["DbName"];

foreach (Table table in yourDb.Tables)
{
    tableScripts = table.Script();
    foreach (string script in tableScripts)
        Console.WriteLine(script);
}

You can also use scripting options to IfNotExists , ScriptDrops, and so on. I suggest you to take a look at the following links:

Generate Scripts for database objects with SMO for SQL Server

Scripting Database Objects using SMO

And then, you can create your script file using C# code.

Script to create database xmla file

1 Answers1