Database Scripter – AgileSQLWare

SQLSmart Database Scripter, developed primarily to facilitate fast and error-free SQL Server Database object scripting, is a unique scripting and change management tool with a simple and intuitive graphical user interface that provides functionality missing in conventional tools
including SQL Server Management Studio (SSMS).

SQLSmart Database Scripter is a powerful application that, with only a single click and in just one iteration, provides the ability to generate DDL and DML (where applicable) SQL scripts for practically all SQL Server database objects like tables, views, and synonyms.

You would be surprised to find out that there are many situations that SSMS and many other tool in the market come short of easily providing sql scripts for your desired objects.

There is no easy way to generate SQL scripts for the any combination of DML, DML & DDL, and DDL cases in one attempt using any tool in the market. SQLSmart Database Scripter was developed to cover this gap and can easily generate general sql scripts as well as change scripts (ALTER and UPDATE) for just about any database object in one iteration.

Main features

Real-life example

Other features

Sample use case

Below is a non-exhaustive list of features offered by SQLSmart Database Scripter along with samples as used in the GUI:

Script out any numbers of tables along with their data in the proper foreign key order
Create Table: [HumanResources].[Department](+data); HumanResources].[JobCandidate];
Script out any changes to columns while preserving dependencies (indexes, defaults, etc.)
Alter Column:[HumanResources].[EmployeePayHistory]([PayFrequency])
Script out varying list of individual columns, indexes, defaults, foreign keys, etc. for one or more tables
Alter Column:[HumanResources].[EmployeePayHistory]([Rate]); [HumanResources].[Shift]([ShiftID]);
Script out data changes (UPDATEs and DELETEs) by ranges
Update: [HumanResources].[Shift](1–2);
Delete: [HumanResources].[Shift](3);
Script out data by ranges
Insert: [dbo].[Person](1–15);
Script out stored procedures, functions, synonyms, etc.
Stored procedure: [dbo].[uspGetBillOfMaterials]; [dbo].[uspGetEmployeeManagers];

Suppose, as part of your development work, you were tasked with generating the scripts for the following:

CREATE TABLE scripts for two tables [HumanResources].[Department] and [HumanResources].[JobCandidate], as well as the data in table [HumanResources].[Department].
DROP TABLE script for table [HumanResources].[EmployeeDepartmentHistory].
ADD COLUMN script for columns [Rate] and [PayFrequency] in table [HumanResources].[EmployeePayHistory].
DROP COLUMN script for column [ModifiedDate] in table [HumanResources].[EmployeePayHistory].
CREATE INDEX scripts for indexes [AK_Employee_LoginID] and [AK_Employee_NationalIDNumber],[AK_Employee_rowguid] in table [HumanResources].[Employee].
CREATE TRIGGER script for trigger [dEmployee] in table [HumanResources].[Employee].
INSERT statements for a subset records in table [HumanResources].[Shift] (ShiftID in range 1-2).

Using SSMS and perhaps most of the SQL scripting tool in the market:

1. You would need to visit each and every object*.
2. You may not get the tables scripted in the proper foreign key order*.
3. You would not be able to script the table schema and its data at the same time*.
4. You will not get a list of dependent objects for DROP TABLE script.
5. You will not get a complete script with all the dependent objects when scripting for adding columns.
6. You will not get a complete script with all the dependent objects when scripting for dropping columns.
7. You would not be able to script the data using a range (cannot filter any records).

*Unless you use “Generate Scripts…” at the database level.

In contrast, using SQLSmart Database Scripter, you can generate the scripts for all of the above items in one attempt:

Pick a desired “SQL_Operations” (Figure 1).
Fill in the column “Objects_to_be_scripted” (Figure 2).
Pick the app name (as defined in the config file) (Figure 2).
Click the “Generate SQL” button to get the output in seconds (Figure 3).

Here are the screenshots for the steps in SQLSmart Database Scripter:

Figure 1

Figure 2

Figure 3

Here is the abridged SQL output generated by SQLSmart Database Scripter:

...
CREATE TABLE [HumanResources].[Department](
    [DepartmentID] [smallint] IDENTITY(1,1) NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [GroupName] [dbo].[Name] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Department_DepartmentID] PRIMARY KEY CLUSTERED 
...
IF NOT EXISTS(SELECT TOP 1 * FROM [HumanResources].[Department] )
BEGIN
     PRINT 'Adding records to [HumanResources].[Department] '
     INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) 
     VALUES (1, N'Engineering1', N'Research and Development', CAST(N'2008-04-30T00:00:00.000' AS DateTime))
...
IF NOT EXISTS(SELECT name FROM sys.columns WHERE object_name(object_id) = 'EmployeePayHistory' AND
     OBJECT_SCHEMA_NAME(object_id) = 'HumanResources' AND name IN ('Rate','PayFrequency'))
BEGIN
     ALTER TABLE [HumanResources].[EmployeePayHistory] ADD [Rate] [money] NOT NULL
...

One of the interesting features of SQLSmart Database Scripter is that in many cases it allows the use of wildcards for script generation.

For instance, you can:
1. script all stored procedures whose name starts with “sp_some” using “sp_some*”.
2. script all tables whose name starts with “Employee” using “[HumanResources].[Employee*]”.
3. script all tables belonging to the [HumanResources] schema using “[HumanResources].[*]”.
4. script all tables and their data whose name starts with “Employee” using “[HumanResources].[Employee*](+data)”.
5. script all indexes whose name starts with “AK” using [HumanResources].[Employee]([AK_*]).

To get the CREATE TABLE script for “[HumanResources].[Employee]”, simply type fill in GUI as shown to the right:

Create Table: [HumanResources].[Employee];

Pick your “app” (app1 here); and
Click the “Generate SQL” button.

The above will generate the output shown on the right once the “Generate SQL” button is clicked:

Just putting an asterisk “*” in front of “Employee” will direct the application to generate the scripts for all tables whose names start with “Employee” and belong to the [HumanResources] schema:

Create Table: [HumanResources].[Employee*];

And if you need to get the data as well for the same tables, add the string “(+data”):

Create Table: [HumanResources].[Employee*](+data);