Microsoft SQL

Microsoft SQL 2008 Generate script for the whole database

Andre MargonoMicrosoft SQL, , , ,

Today I come across a task that require me to backup all the scripts in ms sql server 2008 R2. I take a look at all possibilities then come into this solution:

1. Right Click the database that we wish to generate script from, then select -> tasks -> generate scripts…

2. Then it will open up the wizard to create the script

3. We also can specify the script for whole database or even to specific tables/stored procedures

4. Other than that we also can specify our preferred output type

DROP TABLE IF EXIST in MS SQL

Andre MargonoDatabase, Microsoft SQL, MySQL

MS SQL equivalent to DROP TABLE IF EXIST

When developing an application using MySQL I generally use this syntax:


DROP TABLE IF EXISTS [table_name];
CREATE TABLE [table_name] ( Columns declaration here )

This sql script is useful when I’m about to clear out the data in a table. However in Microsoft SQL (MS SQL) this method will not work.
After I do some searching, I come up with this syntax:


IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[table_name]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[table_name];
CREATE TABLE [dbo].[table_name] ( Columns declaration here );

The above sql script will be equivalent to MySQL syntax before. Hope this will be helpful 🙂