Script Utility

Introduction

This tool is focused on enhancing the data load process by removing the indexes on tables. While data is being inserted in tables which have indexes on them, the keys are added to the index. This could considerably slow down the data load process. Hence to perform a bulk load operation it would be faster to drop the indexes, load the data and rebuild the indexes.

The Script Utility wizard helps you to connect to a SQL Server instance, script the Indexes, foreign keys and triggers from the chosen tables in the database. It then generates create and drop scripts for those objects. These scripts could be run using tools like SQL Server Management Studio to drop the objects and re-create them once the data load is complete.

About the tool

It is a windows application written in C#, which uses SMO to connect to the relational database and script out the objects.

A lot of people have requested a need for this tool, primarily to perform the bulk data load process. The tool is selective in scripting objects. You could script just the clustered indexes, or all indexes from a small set of tables which are performing considerably slow in the data load process. It also provides scripts for foreign keys and triggers along with scripting the indexes.

Using the tool

Screen1:
scnsht1.JPG

• On the introduction screen click on the Next button.

Screen 2:
scnsht2.JPG

• This is the options screen where you setup the database connection settings and scripting options
scnsht2a.JPG
  • Click on Edit
  • The connection properties window opens. Type in the Server name.
  • Choose the authentication mode.
  • Choose the database from the drop down list.
  • Click on Test connection to make sure you have permissions on the database.
  • Click on Ok.
scnsht2b.JPG

• From the list of tables, choose the tables from which you want to script indexes.
• Choose the objects to be scripted (All Indexes/Clustered/Non Clustered, Foreign Keys and Triggers) and click on the Script button.

scnsht2c.JPG

• The tool scripts all the selected objects from the chosen tables.
• Once the scripting is complete, click on View Script.

Screen 3:
scnsht3.JPG

• You can copy the script from this pane or have the tool save the script in a file.
• Click on the Back button.
• On the Options screen click on the Next button.

Screen 4:
scnsht4.JPG

• The list of tables and the objects that were scripted are shown in this page.
• Click on Save Script to save the script to a file.
• Click on Finish to exit.

Contact Information:

For further details/queries regarding this tool or to report bugs and issues mail to: syed.sameer@sonata-software.com

Last edited Mar 22, 2007 at 9:39 PM by nuanw, version 7

Comments

roul174 May 24, 2012 at 7:40 AM 
guys there is some issue on WinXP either WinServer2008R2 after i try to edit connetion string. look at log bellow.
I'll be much thank full for this tool!!
Ondrej

See the end of this message for details on invoking
just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text **************
System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
File name: 'Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'
at Index_Management.Form3.SetConnection(Object sender, EventArgs e)
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

WRN: Assembly binding logging is turned OFF.
To enable assembly bind failure logging, set the registry value [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1.
Note: There is some performance penalty associated with assembly bind failure logging.
To turn this feature off, remove the registry value [HKLM\Software\Microsoft\Fusion!EnableLog].



************** Loaded Assemblies **************
mscorlib
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.5448 (Win7SP1GDR.050727-5400)
CodeBase: file:///C:/Windows/Microsoft.NET/Framework64/v2.0.50727/mscorlib.dll
----------------------------------------
Script Utility
Assembly Version: 1.0.0.0
Win32 Version: 1.0.0.0
CodeBase: file://wa935.ds.kb.cz/c$/%23Documents/Install/MSSQL/DBScript%20Utility/DBScript%20Utility.exe
----------------------------------------
System.Windows.Forms
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.5446 (Win7SP1GDR.050727-5400)
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Windows.Forms/2.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
----------------------------------------
System
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.5453 (Win7SP1GDR.050727-5400)
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System/2.0.0.0__b77a5c561934e089/System.dll
----------------------------------------
System.Drawing
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.5458 (Win7SP1GDR.050727-5400)
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Drawing/2.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
----------------------------------------

************** JIT Debugging **************
To enable just-in-time (JIT) debugging, the .config file for this
application or computer (machine.config) must have the
jitDebugging value set in the system.windows.forms section.
The application must also be compiled with debugging
enabled.

For example:

<configuration>
<system.windows.forms jitDebugging="true" />
</configuration>

When JIT debugging is enabled, any unhandled exception
will be sent to the JIT debugger registered on the computer
rather than be handled by this dialog box.

ansonee Mar 9, 2011 at 7:43 PM 
This tool is fantastic! I can't believe it took me four years to find it!!