Thursday, August 13, 2009

Sql Server 200 Generating Script

An Sql Script is a text file containing the database objects like like table, views, stored procedures etc.

Sql Server 2000 Enterprise Manager or Sql Server 2005/2008 Management Studio can be used for generating Sql Script.







Here is the generated script

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Trans__ExpenseId__7C8480AE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)ALTER TABLE [dbo].[Trans] DROP CONSTRAINT FK__Trans__ExpenseId__7C8480AEGO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Expense__GroupId__79A81403]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)ALTER TABLE [dbo].[Expense] DROP CONSTRAINT FK__Expense__GroupId__79A81403GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spAllExpense]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[spAllExpense]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Expense]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[Expense]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ExpenseGroup]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[ExpenseGroup]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Trans]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[Trans]
GO

CREATE TABLE [dbo].[Expense] ( [ExpenseId] [int] IDENTITY (1, 1) NOT NULL , [GroupId] [int] NULL , [ExpenseName] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Description] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ExpenseGroup] ( [GroupId] [int] IDENTITY (1, 1) NOT NULL , [GroupName] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Description] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Trans] ( [TranId] [int] IDENTITY (1, 1) NOT NULL , [ExpenseId] [int] NULL , [Date] [datetime] NULL , [Amount] [numeric](8, 2) NULL , [Description] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY]GO
SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON
GO

create procedure spAllExpense asselect * from Expense
GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON
GO

No comments:

Post a Comment