Geek Answers Handbook

Free schema export for SQL Server 2008?

Not sure if it belongs here or ServerFault, but I wonder if anyone knows a free tool to export SQL Server 2008 schemas? This is only for tables and their indexes, foreign keys, etc., And it should be a command line tool to execute as part of the build process. If it can read the .net connection string, that would be awesome (hence the .net tag)

Data is not needed, and any type of version / diff is also "Nice, but not needed." And yes, I know about Red-Gate awesome SQL Server tools, unfortunately this is a hobby project with a budget of 0 :-(

+4
source share
5 answers

I'm not sure about the finished tool, but it is quite simple to use the SMO library (Microsoft.SqlServer.Smo, .SmoEnum, .SqlEnum):

using Microsoft.SqlServer.Management.Smo; var server = new Server("localhost"); var database = server.Databases["databaseName"]; var transfer = new Transfer(database); var options = new ScriptingOptions(); // set transfer and options object properties to reflect what you want to script: // ie all tables, indexes, triggers, etc. options.FileName = "c:\\temp\\databaseName_schema.sql"; transfer.Options = options; transfer.ScriptTransfer();

I built a simple tool using this method to restore the creation of my script product database as part of the pre-build steps in the settings builder.

+5
source

Just out of curiosity, were you trying to use SQL Server projects in Visual Studio?

Another way to do this is through SQL scripts, as I'm sure you know. The script generation command can be executed to run on the command line, I think.

+2
source

You can always use SQL Server Management Studio to do this, right-click the database, select Tasks, and then Generate Scripts.

+2
source

CodePlex has several free command line tools like this. One of them is Scriptio, here .

+1
source

Microsoft released a new tool a few weeks ago called mssql-scripter. This is a free Python-based open source command line tool, and you can find the official announcement here . Essentially, the script allows you to generate a T-SQL script for your database object / database as a .sql file. You can generate the file and then execute it. This may be a good solution to create the schema (by default) of your db and its objects, such tables and indexes, so that you can also see diff, since it generates a .sql file. Here's a quick use example to get you started:

 $ pip install mssql-scripter # script the database schema and data piped to a file. $ mssql-scripter -S localhost -d AdventureWorks -U sa > ./adventureworks.sql

Additional usage examples are provided on our GitHub page here: https://github.com/Microsoft/sql-xplat-cli/blob/dev/doc/usage_guide.md

0
source

More articles:


All Articles