SDU Tools: Script SQL Server Table
In our free SDU Tools for developers and DBAs, we’ve added a lot of scripting tools. The tool that I’m describing today is one of the most sophisticated tools in our scripting options. It’s ScriptTable.
It’s very flexible. For example, it can change the name of the table, or the schema that it’s in. It can force ANSI_NULLS and ANSI_PADDING on or off. It can change user-defined data types to their base types, change compression strategies, and more.
Have a bunch of pesky collation settings that you didn’t want? It can remove them too, and it can let you choose things like the indents to be used during scripting.
You can see how to use it in the main image above. It’s a procedure, that takes the following parameters:
@DatabaseName sysname -> Database name for the table to be scripted @ExistingSchemaName sysname -> Schema name for the table to be scripted @ExistingTableName sysname -> Table name for the table to be scripted @OutputSchemaName sysname -> Schema name for the output script (defaults to same as existing schema) @OutputTableName sysname -> Table name for the output script (defaults to same as existing table) @OutputDataCompressionStyle nvarchar(10) -> must be one of SAME, NONE, ROW, PAGE (SAME uses whatever the table’s first partition currently has) @AreCollationsScripted bit -> Should all collations be scripted (default is 0 for no) @AreUsingBaseTypes bit -> Should the table use the underlying base types instead of alias types (default is 1 for yes) @AreForcingAnsiNulls bit -> Should the script include code to force ANSI_NULLS on (default is 1 for yes) @AreForcingAnsiPadding bit -> Should hte script include code to force ANSI_PADDING on (default is 1 for yes) @ColumnIndentSize -> How far should columns be indented from the table definition (defaults to 4) @ScriptIndentSize -> How far indented should the script be? (defaults to 0) @TableScript nvarchar(max) OUTPUT -> The output script
The script in the main image produced the following output:
I’ve mentioned that one key advantage of having these scripting procedures and functions is that you incorporate them into other applications, and programmatically perform the scripting if you need to.
But if you just want to create a script, that works too.
You can watch it in action here:
To become an SDU Insider and to get our free tools and eBooks, please just visit here:
http://sdutools.sqldownunder.com
2019-01-30