I didn’t get a Synonym or User folder, those would be nice for a complete database generation.

the following script was generated, which produced an error when run against sql server.
"cannot use duplicate column names in index"

CREATE INDEX [IX_for_Purges_Widgets] ON [dbo].[RT_Check_Deduction]([Bus_Date], [Bus_Date], [Bus_Date]) INCLUDE (
[Location], [Check_Number], [Deduction_Time],
[Deduction_Amount], [Location], [Check_Number], [Table_Name],
[Check_Close_Time], [Location], [Check_Number], [Item_Order_Time],
[Item_ID], [Base_Price], [Sold_Price], [Void_Flag]
)

When I tried to use schemazen (1.1.3) to "Script" a SQL 2008R2 database (with tables using datetime2 columns) with this command:
schemazen.exe Script -s=cnXXXXXXXXdev -b XXXXXXXManagement -d=C:\output

The following error is returned:

SQL data type datetime2 is not supported.
at model.Column.Script()
at model.ColumnList.Script()
at model.Table.ScriptCreate()
at model.Database.ScriptToDir(Boolean overwrite)
at console.Script.Run(String[] args)
at ManyConsole.ConsoleCommandDispatcher.DispatchCommand(IEnumerable`1 command
s, String[] arguments, TextWriter consoleOut, Boolean skipExeInExpectedUsage)
at console.Program.Main(String[] args)

I would like to know if I got anything mis-configured. Thank you.

Given the following table,

CREATE TABLE [dbo].[CheckTest] (
    [CheckTestValue] [varchar](25) NOT NULL 
          CHECK (CheckTestValue != '')
)

SchemaZen scripts it as:

CREATE TABLE [dbo].[CheckTest] (
   [CheckTestValue] [varchar](25) NOT NULL 
)

GO

This shows that we can get check constraint information from SQL using:

SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS

but it lacks table information, but can be combined with:

SELECT OBJECT_NAME(OBJECT_ID) AS ConstraintName
    ,SCHEMA_NAME(schema_id) AS SchemaName
    ,OBJECT_NAME(parent_object_id) AS TableName
FROM sys.objects
WHERE type_desc = 'CHECK_CONSTRAINT'

(which lacks the check definition).

Hi.

The database I'm trying to script has several compound primary keys like this:

CONSTRAINT [PK_NAME] PRIMARY KEY CLUSTERED ([c_column], [a_column], [b_column])

The application generates then properly in the table creation script. The problem shows if there is a foreign key pointing to that primary key.

The code the application generates is the following:

ALTER TABLE [dbo].[FK_TABLE] WITH CHECK ADD CONSTRAINT [FK_TABLE_TO_PK_TABLE]
   FOREIGN KEY([a_column], [b_column], [c_column]) REFERENCES [dbo].[PK_TABLE] ([a_column], [b_column], [c_column])
   ON UPDATE NO ACTION
   ON DELETE NO ACTION

It changes the column order, causing an error when creating the database from the exported model. The PK is defined [c, a, b], but the FK is sorted [a, b, c]. It happens in all cases with compound PK's.

Regards.

Tested with schemazen (1.1.3), data export is not working, an empty data directory is created under C:\output after running the following command:
schemazen.exe Script -s=cnXXXXXXXXdev -b XXXXXXXManagement -d=C:\output

I suspect the error is due to a typo at Database.ExportData.
Instead of iterating the DataTables field, the Tables field should be used instead.

All,

Would it be possible for your to add a timeout to the command line parameters? I seem to have a problem with my database where some of the tables can be 400+MB and it seems to timeout during the import. Note that this was done directly on the server so it has nothing to do with network connectivity.

Lastly, does a way exist to create a database with a dash (-) in the name? I tried Test-DB as well as [Test-DB] but it does not seem to support the dash in any way.

Timeout Error:

Importing data for table dbo.FileAttachments...
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is n
ot responding.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is n
ot responding.
at SchemaZen.model.Database.ImportData(Action2 log) at SchemaZen.model.Database.CreateFromDir(Boolean overwrite, Action2 log)
at SchemaZen.console.Create.Run(String[] remainingArguments)
at ManyConsole.ConsoleCommandDispatcher.DispatchCommand(IEnumerable`1 commands, String[] argument
s, TextWriter consoleOut, Boolean skipExeInExpectedUsage)
at SchemaZen.console.Program.Main(String[] args)

Thank You!

If database object contains a character that is not supported by file system like "?" replace it with something else? Currently the export breaks with an error see below

Illegal characters in path.
   at System.IO.Path.CheckInvalidPathChars(String path, Boolean checkAdditional)
   at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost)
   at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost)
   at System.IO.StreamWriter.CreateFile(String path, Boolean append, Boolean checkHost)
   at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize, Boolean checkHost)
   at System.IO.File.InternalWriteAllText(String path, String contents, Encoding encoding, Boolean checkHost)
   at model.Database.ScriptToDir(String tableHint)
   at console.Script.Run(String[] args)
   at ManyConsole.ConsoleCommandDispatcher.DispatchCommand(IEnumerable`1 commands, String[] arguments, TextWriter consoleOut, Boolean skipExeInExpectedUsage)
   at console.Program.Main(String[] args)

Script generated by SSMS for a filtered index (unique in this case):

CREATE UNIQUE NONCLUSTERED INDEX [IXUK_tbl_ventsClientes_1] ON [dbo].[tbl_ventsClientes]
(
[NIF] ASC, [TipoCliente] ASC, [IDEstado] ASC
)

WHERE ([NIF] IS NOT NULL)

Same script, generated by SchamaZen 1.26:

CREATE UNIQUE NONCLUSTERED INDEX [IXUK_tbl_ventsClientes_1] ON [dbo].[tbl_ventsClientes]
(
[NIF], [TipoCliente], [IDEstado]
)

Database: SQL Server 2008 R2

Hello All,

Thanks for such fast turn around on issues. This is a great tool!

Question on the UDT's that you added a couple releases back. I see that they are scripted, but when i create a database, the directory "Table_Types" is not loaded. Is this still under development from a create stand point??

Code (no table_types):
private static readonly string[] dirs = {
"tables", "foreign_keys", "assemblies", "functions", "procedures", "triggers",
"views", "xmlschemacollections", "data", "users", "synonyms"
};

Would it be possible, besides comparing two databases, to compare a database to the schema scripts directory directly?
The benefits are that the schema changes can be propagated via source control and compared to databases at different servers. The comparison to scritps directory avoids having to generate the database from scripts and then compare the two databases.

I found this example that might help: http://agimatec-tools.googlecode.com/svn/trunk/dbmigrate/src/main/java/com/agimatec/sql/script/SQLScriptParser.java

If a single script for a stored procedure or function, obtained by SchemaZen, perform on the database by the utility sqlcmd -i (after removing the existing object from the database), and then again get it via SchemaZen, the new version of the script will be different from the old version one empty line, although no manual modifications to the script file was not fulfilled.
It seems the new line is added to the definition of the object after it is created from a script by sqlcmd -i. Because the line with the following GO written to the definition as a carriage return.

Currently when an exception occurs the application crashes and prompts the user to debug or close. It should print the error to the console and exit instead.

Wildcard option for this would be cool, like

All tables

--dataTables %

Tables starting with "user"

--dataTables user%

File Input

--dataTables file=some_file.txt

And perhaps new option that could ignore some objects

https://msdn.microsoft.com/en-us/library/bb677290.aspx

Hi !

Fortunately, I found your great project. Thanks for your work.
I think a feature that would be nice would be to have an option checking object existence in creation scripts (like the MSSMS option having the same name), adding "IF NOT EXISTS" instruction on the different scripts before trying to recreate them.

That's just a suggestion :)

Hi,
I have just debugged your code because of a bug in DataTablePatterns. (->The assembly doesn't respond to DataTablePattern option).
I found the failing code in class Script, Method Script() and wrote the following patch (You used two times DataTables instead once DataTablePattern)

Thanks for your work, it helps me a lot
Roland

PATCH:

    public Script() : base(
        "Script", "Generate scripts for the specified database.") {
        HasOption(
            "dataTables=",
            "A comma separated list of tables to export data from.",
            o => DataTables = o);
        HasOption(
            "dataTablesPattern=",
            "A regular expression pattern that matches tables to export data from.",
            o => DataTablesPattern = o); //HERE !!!! --->
    }

The foreign keys include this text, but when I script them from SSMS it doesn’t include it. What's the rationale?

ON UPDATE NO ACTION
ON DELETE NO ACTION

Maybe log after each action is done.
Maybe accept verbose or silent as options.
If I get some time, I'll clone and do a PR.

If you run the "Create" command on the output from a "Script" command and you have object dependencies, the "Create" command will likely fail because objects are not created in the correct order.

It would be helpful to have the important object dependencies saved to a file by the "Script" command so that it could be used when comparing database versions or be added to a version control system. And of course the "Create" command could use this file as input in order to successfully create the tables in the correct order.

Here's some sample output from the Create command when trying to create a DB with dependencies.

C:\>schemazen.exe Create -s mySqlServer -b TrialDB -d .\DBSchema
18 errors occurred, retrying...
A SQL error occurred while executing scripts.
.\DBSchemaCf\foreign_keys\CapacityWorksheetComments.sql(Line 1): Cannot find the
 object "import.CapacityWorksheetComments" because it does not exist or you do not have permissions.

Hello,

Using version 1.1.0, and the following arguments:

I get the following error:
Object reference not set to an instance of an object.
at model.ForeignKey.ScriptCreate()
at model.Database.ScriptToDir(Boolean overwrite)
at console.Script.Run(String[] args)
at ManyConsole.ConsoleCommandDispatcher.DispatchCommand(IEnumerable`1 commands, String[] arguments, TextWriter consoleOut, Boolean skipExeInExpectedUsage)
at console.Program.Main(String[] args)

The error appears only on one database. Using the same server and arguments with another database it works ok.

Heya,

I'm getting this error when running against SQL Server 2016 CTP3

Would be nice if it's fixed eventually

Cheers,
Jonny

A lot of Shared Hosting providers will not let you perform any actions on the 'master' database, so a CREATE DATABASE command will fail.

I have a SQL script that drops all objects within a database that could be used as an alternative to dropping the whole database itself before restoring it's contents. (Note I did not write this script)
DropAllObjects.txt

SchemaZen does not script User-defined table types (UDTTs).

User-defined table types (UDTTs) are defined just like tables, so the SchemaZen Table class could be extended to capture UDTTs. However, the metadata is not available from INFORMATION_SCHEMA.

The following query lists user-defined table types:

SELECT *
  FROM sys.table_types
  WHERE is_user_defined = 1

The following query can be used to get all of the UDTT columns:

SELECT 
    s.name as schema_name,
    tt.name as table_type_name, 
    c.*
FROM sys.columns c
INNER JOIN sys.table_types tt on tt.type_table_object_id = c.object_id
INNER JOIN sys.schemas s on tt.schema_id = s.schema_id 
WHERE tt.is_user_defined = 1
ORDER BY schema_name, table_type_name, column_id

I have not been able to use any releases after 1.19 as starting with 1.2.0 i get an error "Specified cast is not valid". I was wondering if this is a known issue, or if you had some tips that I could use to see what you do not like in our database?

SchemaZen119 script -s 127.0.0.1 -b TestDB -d TestDB -u admin -p xx
Snapshot successfully created at TestDB

SchemaZen120 script -s 127.0.0.1 -b TestDB -d TestDB -u admin -p xx
Specified cast is not valid.
at SchemaZen.model.Database.LoadColumnsBase(IDataReader dr, List1 tables) at SchemaZen.model.Database.LoadColumns(SqlCommand cm) at SchemaZen.model.Database.Load() at SchemaZen.console.Script.Run(String[] args) at ManyConsole.ConsoleCommandDispatcher.DispatchCommand(IEnumerable1 command
s, String[] arguments, TextWriter consoleOut, Boolean skipExeInExpectedUsage)
at SchemaZen.console.Program.Main(String[] args)

This results in an error when the database is created from the snapshot.

Procs, Functions, and Triggers have the following text but it’s not in Tables. I personally prefer to not have this in any scripts, is there a way?

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

Why are the foreign keys being scripted into their own folder when the primary keys, defaults and indexes are included in the create table statement? I’d prefer it all in one file. Plus the file names are the same and that’s really confusing.

Hi, thanks for SchemaZen, saved me many hours of headaches :)

Just so you know, it does not work directly on Azure SQL, because sys.server_principals does not exist.

I just commented out LoadUsersAndLogins, that I did not need anyway, and it worked perfectly it seems.

SchemaZen does not script "CREATE SCHEMA" statements for each schema in the database.

Hi, I want to get the script of a Local 4.0 Compact SQL database but I dont know If schemazen might be able to achieve this, It would be great. Thanks.

I tried --dataTablesPattern=.* and it does not export any table data.
I also tried formatting the regex in various different ways (quotes, brackets, etc.), still no luck.

I hardcoded this.DataTablesPattern = ".*"; into Script.cs.Run() ... That worked.

I'm not able to debug through ManyConsole / NDesk.Options, but I expect the problem is in there somewhere.

BTW, great tool. Thank you.

Do you think it's possible to extract that attribute when the DB is Scripted.

I have many PK that are UniqueIdentifier with the ROWGUIDCOL that I can't extract.

Thanks for your great programme!
André

The views are being generated in the Procs folder. They need to go into a Views folder. This is a big deal.

While trying to script a database from a sql2005 server I get the following exception

  Invalid object name 'sys.table_types'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolea
n breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObj
ect stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand
 cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler,
TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, Run
Behavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBe
havior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 time
out, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameter
EncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehav
ior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletio
nSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehav
ior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, S
tring method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader()
   at SchemaZen.model.Database.LoadConstraintsAndIndexes(SqlCommand cm)
   at SchemaZen.model.Database.Load()
   at SchemaZen.console.Script.Run(String[] args)
   at ManyConsole.ConsoleCommandDispatcher.DispatchCommand(IEnumerable`1 command
s, String[] arguments, TextWriter consoleOut, Boolean skipExeInExpectedUsage)
   at SchemaZen.console.Program.Main(String[] args)

This is from Database.LoadConstraintsAndIndexes().  Commenting out the union select from sys.table_types allows the scripting 

When the following message is encountered
Warning: SQL_STORED_PROCEDURE dbo.SOMEPROCNAME has been renamed since it's definition.

It reports "Snapshot successfully created at" but when the stored procedure folder is viewed none of the other stored procedures are scripted.

The problem is fixed when a alter statement is run on the stored proc

There needs to be an option whether to generate system objects or not. I definitely don’t want them and they're currently being generated.

If you have such type

CREATE TYPE [dbo].[SessionData] AS TABLE(
    [OperatorName] [nvarchar](max) NULL
)

after scripting you receive

CREATE TYPE [dbo].[SessionData] AS TABLE(
    [OperatorName] [nvarchar](0) NULL
)

It can't be recreated later.

SQL Edition: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64)

Would be nice to have an option to ask SchemaZen to not to create empty folders in the ScriptDir

Currently i have my script like this, to remove the empty folders

mkdir sqldump
SchemaZen.exe Script --server localhost --database test --user test --pass test --scriptDir sqldump\ --overwrite Y
cd sqldump
FOR /F delims^= %%A IN ('DIR/AD/B/S^|SORT/R') DO RD "%%A"

If your database contains two tables with different schemas but the same table name, neither table is scripted properly. In addition, there would be a file naming problem if both were scripted, since the filename does not include the schema.

When you run the "Create" command on such a database, the scripting appears to run correctly. No error is thrown.

Here's a test case. Add the following in a test database and then run the "Create" command on the DB and look at the "Tables\Batch.sql" file that is generated.

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'import')
    EXEC sys.sp_executesql N'CREATE SCHEMA [import] AUTHORIZATION [dbo]'
GO

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'app')
    EXEC sys.sp_executesql N'CREATE SCHEMA [app] AUTHORIZATION [dbo]'
GO

CREATE TABLE [app].[Batch](
   [PK] [int] NOT NULL IDENTITY (1,1),
   [User] [nvarchar](100) NULL,
   [ProcessingStatus] [varchar](50) NULL,
   [StartTS] [datetime] NULL,
   [EndTS] [datetime] NULL,
   CONSTRAINT [PK_App_Batch] PRIMARY KEY CLUSTERED ([PK])
)
GO

CREATE TABLE [import].[Batch](
   [PK] [int] NOT NULL IDENTITY (1,1),
   [User] [nvarchar](100) NULL,
   [ProcessingStatus] [varchar](50) NULL,
   [StartTS] [datetime] NULL,
   [EndTS] [datetime] NULL,
   CONSTRAINT [PK_Import_Batch] PRIMARY KEY CLUSTERED ([PK])
)
GO

Hi, on a server:
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64)
Aug 19 2014 12:21:34
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

using the command on
schemazen.exe script -s="SERVERNAME" -b="DATABASENAME" -u="USERNAME" -p=
"PASSWORD" -d="LOCALFOLDER" -o=Y

I receive an error:
Unable to cast object of type 'System.DBNull' to type 'System.String'.
at SchemaZen.model.Database.LoadConstraintsAndIndexes(SqlCommand cm)
at SchemaZen.model.Database.Load()
at SchemaZen.console.Script.Run(String[] args)
at ManyConsole.ConsoleCommandDispatcher.DispatchCommand(IEnumerable`1 commands, String[] arguments, TextWriter consoleOut, Boolean skipExeInExpectedUsage)
at SchemaZen.console.Program.Main(String[] args)

The create command doesn't consider dependencies when creating objects so if udf_A depends on udf_B it will fail with error "invalid object name 'udf_B'.

This would allow someone to be able to completely restore the database manually without the use of SchemaZen.