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 the script 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 script is not meeting his requirement.

Below are some of the requirements –

• Script 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

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

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.

If you liked this post, do like on Facebook at https://www.facebook.com/s4sql

SQL Server– Template Explorer

Yesterday when I was about to leave office , I got a call on my mobile from one of my friend. He wanted to know how to write a recursive query?

I said use Template Explorer, Where you can get examples for recursive query but He was unaware of this feature of SQL server. So I thought to write on this.

SQL server provides number of templates which can be used to Create, Alter and Drop different database objects  like Table, Index, Triggers, View and many more. With the help of Template Explorer you can do DDL operation on object as well as you can learn How to write recursive query too.

Follow below steps to open the recursive query example:

Step 1- Open SQL Server Management Studio

Step 2 – Use shortcut key Ctrl + Alt + T or You can find this option under View-> Template Explorer

Step 3: Expand Recursive Queries section

 

We can create our own Custom Template. Refer How to create custom template?. When first time the template explorer is opened, a copy of the templates are placed in the user’s folder in C:\Users, under <username>\AppData\Roaming\Microsoft\SQL Server Management Studio\11.0\Templates. So that all users can have their own copy of templates.

 

If you liked this post, do like on Facebook at https://www.facebook.com/s4sql

IntelliSense feature in SSMS 2008 R2 Is Not Working

After Installing Visual Studio 2010 SP1 , My SSMS 2008 R2 intellisense stop working.

So here I’ll tell you how to solve this issue?

At first instance I got some light from my favourite blog at blog.sqlauthority.com.

But After enabling the intellisense, it didn’t work for me. So The above solution enable your SSMS intellisense but it will not work if you have VS 2010 SP1 installed.

This is a bug and which has been fixed in SQL Server 2008 R2 Service Pack 1.

You can Install the Service pack from here :SQL SERVER 2008 R2 SP 1.

To enable intellisense install the SQL SERVER 2008 R2 SP1 and then follow the instruction from blog.sqlauthority.com.