Some of the common routine activities that we do on Sql Server are :
1. Backup
2. Restore
3. Generate table scripts
For the point mentioned 1 and 2 above, I had tried to ease with this powershell utility http://www.sqlservercentral.com/scripts/Backup/151723/
In this post, I will talk about a utility which would ease generating table scripts even by an app developer without accessing SSMS.
The advantage of this powershell utility is:
1. It allows the user to exclude identity columns in the data scripts
2. It allows the user to specify the number of rows to be populated in the data scripts.
Let me do a walkthrough !!
On executing this script, it asks for :
- Server name
- Database name
- Mode of authentication : Windows/SQL
- If mode of authentication is SQL, then it would ask Sql User name and Password
- Table and Schema name for scripting
- Various options for scripting
- Generate schema
- Generate data
- Check for object existence
- Script indexes
- Script foreign keys
- Whether to exclude identity columns
- Whether to limit the number of rows for data script
as seen in figure 1.0.
Fig1.0 we get the scripts generated as shown in figure 1.1
Fig1.1
If the user enables the feature to exclude identity columns by passing “y” to the option “Do you want to ignore identity columns while scripting data” as shown in figure 2.0 we get the output with the identity column “ResellerKey” as shown in figure 2.1
Fig2.0
Fig2.1
The data script contains around 701 rows and we do not put a limit on number of rows as shown in figure 3.0 and 3.1
Fig3.0
Fig3.1
However, we may require a subset of rows for our testing purpose. Let’s say we need just 2 rows, then we need to pass below parameters:
Do you want to create data scripts for fixed number of rows : y
Enter the number of rows required in the data script: 2
as shown in figure 4.0
Fig4.0
We see that we got just 2 rows in the data script as showing in figure 4.1
Fig4.1
How to use the script
Follow the below steps for executing the script:
1. Paste the powershell code mentioned in this article in a text editor and save the file with extension “ps1”, let’s say the file is saved as GenerateScripts.ps1
2. Let’s say the file is saved to a location : c:\utility\GenerateScripts.ps1, create a batch file with the name GenerateScripts.bat in the same location as the powershell file and paste below mentioned code in the batch file
@echo off powershell -executionpolicy bypass -File .\GenerateScripts.ps1 pause
3. In order to execute the powershell, execute the batch file GenerateScripts.bat
3. After executing this utility, the scripts are saved in the same folder within the file “scripts.sql” file.
Note:
To execute the option for exclude identity columns and limit the number of rows in data scripts, the user should be a member of db_ddlAdmin or db_Owner role.
I would love to hear the feedback and suggestions on further improvements to this script !!
More »
