Wednesday, April 12, 2017

Amazing developer tool for SQL Server


SQL Assistant is an incredible tool that can be used for several tasks in SQL Server and other databases like the following:

Generate sample data

My favorite feature is the possibility to create sample data. With this feature, it is possible to generate sample data. You can populate tables with sample data by right clicking and selecting the option SQL Assistant>Generate Test Data:



You can specify the number of rows of the sample data (in this sample 1000 rows) and then press the Generate button to generate the sample of data:


The software will give you the choice to store the project settings:

If you open the table, you will be able to see that 1000 rows were created:


This is extremely useful to make tests in our apps with several rows and test the performance with real amounts of data when we connect to databases.

Generate scripts to insert data

With SQL Assistant, you can generate a script to insert all the data. This is a fast way to export the data for the tables or to generate the insert code in a fast and easy way. To do that, in the context menu, go to SQL Assistant>Script Data:

Select the tables that you want to use to generate scripts:



You will be able to see the T-SQL scripts created with the insert data code. This is a very simple way to populate empty tables or export data:



Generate scripts to create tables, views and other objects.

The script DDL option is used to define objects. Generate the objects' code for tables, views and stored procedures:


You can select database objects that you want to create generating T-SQL scripts:


As you can see, the scripts are generated to create tables or other objects using few clicks:


Generate stored procedures
The option Generate SQL Code allows creating stored procedures based on the tables. Chose a table with data and select the option to test:

As you can see, the code is generated to delete, select and update the data when the generate button is pressed:

What you will have is 3 stored procedures to delete data from a table with a given primary key specified as a parameter. Another procedure will get the columns with a primary key specified and finally, the third procedure updates the data:

The stored procedures are very important to integrate the tables with the software applications. It keeps a safer code and it is harder to attack with sql injections to the database.

Export data to Excel (xls, xlsx, csv, xml, JSON, TBL.
Other important feature is the export data option. SQL Assistant can export the data to different formats like XLSX, XLS, CSV, JSON, TBL, XML:



If we export to Excel, we can see the tables with a format specified by default. It is possible to customize the format according to your preferences:



To export to different formats, it is necessary to have MS Excel installed.

Diagram creator

It is also possible to create diagrams of tables and views using the Create Database Model option:




You can select the tables that you want to include in the diagram:

Also, you can include the views:

It is also possible to import the stored procedures in the diagram:



To end the diagram wizard, we have the diagrams that can be customized according the customer preferences:

Unit tests
The unit tests allow to test the right functionality of the database objects. We will press the right button on a table and specify the option SQL Assistant>Unit Tests:




Unit test creates insert, select, update and delete code. It is necessary to edit the code to run it:


Other features
  • There are several other features like the Data load where you can load data to test a heavy load, you can compare the objects of 2 databases, compare the data of 2 tables and more.
Conclusions

As you can see SQL Assistant is an incredible tool. This tool was tested here in SQL Server. However, this software can be used in MySQL, SAP, DB2, PostgreSQL, Amazon, Teradata, IBM Netezza, MS Access, SQLite and Oracle.

This software also includes intellisense features that helps a lot to write T-SQL code in an intellegent and efficient way.

I really recommend this software to help on daily tasks.




0 comentarios:

Post a Comment