Monday, February 20, 2012

Programatically create a script

I need some help with the following issue. I need vb.net code that will create a T-SQL script. For example given the orders table in northwind I would get the followingwith indexes, RI, ect. :
CREATE TABLE [dbo].[Orders] (
[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmployeeID] [int] NULL ,
[OrderDate] [datetime] NULL ,
[RequiredDate] [datetime] NULL ,
[ShippedDate] [datetime] NULL ,
[ShipVia] [int] NULL ,
[Freight] [money] NULL ,
[ShipName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipAddress] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipCity] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipRegion] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipPostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipCountry] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[OrderID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD
CONSTRAINT [DF_Orders_Freight] DEFAULT (0) FOR [Freight]
GO

CREATE INDEX [CustomerID] ON [dbo].[Orders]([CustomerID]) ON [PRIMARY]
GO

CREATE INDEX [CustomersOrders] ON [dbo].[Orders]([CustomerID]) ON [PRIMARY]
GO

CREATE INDEX [EmployeeID] ON [dbo].[Orders]([EmployeeID]) ON [PRIMARY]
GO

CREATE INDEX [EmployeesOrders] ON [dbo].[Orders]([EmployeeID]) ON [PRIMARY]
GO

CREATE INDEX [OrderDate] ON [dbo].[Orders]([OrderDate]) ON [PRIMARY]
GO

CREATE INDEX [ShippedDate] ON [dbo].[Orders]([ShippedDate]) ON [PRIMARY]
GO

CREATE INDEX [ShippersOrders] ON [dbo].[Orders]([ShipVia]) ON [PRIMARY]
GO

CREATE INDEX [ShipPostalCode] ON [dbo].[Orders]([ShipPostalCode]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Orders] ADD
CONSTRAINT [FK_Orders_Customers] FOREIGN KEY
(
[CustomerID]
) REFERENCES [dbo].[Customers] (
[CustomerID]
),
CONSTRAINT [FK_Orders_Employees] FOREIGN KEY
(
[EmployeeID]
) REFERENCES [dbo].[Employees] (
[EmployeeID]
),
CONSTRAINT [FK_Orders_Shippers] FOREIGN KEY
(
[ShipVia]
) REFERENCES [dbo].[Shippers] (
[ShipperID]
)
GO

It's not VB.NET code, it's a component, but here's something that does what you want:
http://thycotic.com/dotnet_dbscripter.html

No comments:

Post a Comment