SQL Server – Customize Database object Script

SSMS provides functionality to generate script for database objects. I’ve seen developer use this functionality to generate script for various objects like Tables, Procedures, Triggers and other database objects but after generating it, they manually modify the generated scripts. When I asked one of the developers why you need to modify the generated scripts? He said the generated one is not meeting his requirement.

Below are some of the requirements –

  • It should not start with USE <database name>.
  • It should check for existence of the object prior to the create statement.
  • It should include collation
  • If the object is Table or view it should generate scripts for all the associated index and Triggers.

SSMS allow to customize our script as per our requirement. SSMS has options to customize it.

Follow below steps to customize your scripts.

Step 1 – Go to Tools – Option. It will open the Option Page

ssms1

Step 2 – Click on SQL Server Object Explorer and select Scripting

ssms2

Step 3 – On left side panel you can find various customization option grouped into below three categories.

  • General scripting options
  • Object scripting options
  • Table and view options

You can set these options as per your requirement.

Example: If you don’t want Use <Database name> statement at the beginning of your script then Set Script USE <database> to false.

ssms3

Avatar

About Sandip Pani

Sandip Pani is a Database/BI developer and speaker and is located in Bangalore, India. He has been working with SQL Server over 11 years. He is MCITP for SQL Server 2008 and specializes in SQL Server for application developers and performance and query tuning. His specialization is into Query Tuning and performance troubleshooting. He is working as Senior Technical Specilist for NextGen Healthcare. He is active in SQL community world. He share and enhance his knowledge of SQL Server by spending time at newsgroups and forums, reading and writing blogs, and attending and speaking at conferences.

View all posts by Sandip Pani →

Leave a Reply

Your email address will not be published. Required fields are marked *