SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Powershell Script for regular DBA activities_GenerateScripts

By Mayank Shukla,

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 :

  1. Server name
  2. Database name
  3. Mode of authentication : Windows/SQL
  4. If mode of authentication is SQL, then it would ask Sql User name and Password
  5. Table and Schema name for scripting
  6. 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 !!

Total article views: 574 | Views in the last 30 days: 10
 
Related Articles
FORUM

Alter Column with Identity

Alter Column with Identity

FORUM

How to handle Identity column in replication

Sajeev -Identity column & Replication

FORUM

identity column

identity column

FORUM

NOT FOR REPLICATION identity column

NOT FOR REPLICATION identity column

FORUM

Inserting zero in Identity Column

Inserting zero in Identity Column

 
Contribute