October 27, 2005 at 2:03 pm
Hi,
I am creating an ASP.NET application to create a database programmatically. The application will allow us to select a database from the list. Once the user selects the database it needs to create a copy of that database. The new database doesn't need to have all the tables that are in the selected database.
Lets say I have a table named stores in the database XYZ. I want to create a database named XYZ1 and create a table named stores that has the same structure as the table stores in XYZ. How could I do that programmatically?
Thanks,
Sridhar.
October 27, 2005 at 3:03 pm
I'll tell U if you hire me.
Try searching for and reading up on SQL DMO
October 27, 2005 at 3:37 pm
can i use SQL DMO inside ASP.NET? I do not have any idea about SQL DMO. will it allow to pass variables? can you give me a sample?
Thanks,
Sridhar.
October 28, 2005 at 3:07 am
First you can copy the required tables vie the select * into newdb.newuser.Newtable from olddb.olduser.oldtable order
Then you have to create the required indexes.
After that the views, SPs, trigger. You can copy them from the syscomments table.
Bye
Gabor
October 28, 2005 at 5:45 am
To copy only table schema without data you can use the same query with SELECT TOP 0 * INTO ...
Thanks,
Andrey
October 28, 2005 at 6:46 am
this copies the basic structure, but it doesn't copy foreign keys, column defaults, check constraints, etc.
there are several scripts here on SSC that can programatically export the structure of a table:
an old one i made personally a while ago is here:
http://www.sqlservercentral.com/scripts/contributions/730.asp
but there are otehrs on this site; the one i contributed is not complete, but it'll get you started at teh very least if you want to use it.
Lowell
October 28, 2005 at 6:55 am
Yes, I know this.
But my post was only an addition to previous post.
The full copy of table with all dependent objects can be done by using system tables.
October 28, 2005 at 8:49 am
Take a look at this article that I wrote a few years ago...
http://www.sqlservercentral.com/columnists/rrandall/creatingascriptfromastoredprocedure.asp
The full title was originally 'Creating a 'Create Table' script from a stored procedure', but it got edited somewhere down the line.
If you create all the stored procedures there, running the 3 lines below will give you the green output (i.e. the output includes FKs, indexes etc.).
DECLARE @Object_Text VARCHAR(8000)
EXEC Get_Create_Table_Script @Object_Text OUTPUT, '<your server name>', 'Northwind', 'Orders'
SELECT @Object_Text
if exists (select * from dbo.sysobjects where id = object_id(N'[Orders]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [Orders]
GO
CREATE TABLE [Orders] (
[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerID] [nchar] (5) COLLATE Latin1_General_CI_AS NULL ,
[EmployeeID] [int] NULL ,
[OrderDate] [datetime] NULL ,
[RequiredDate] [datetime] NULL ,
[ShippedDate] [datetime] NULL ,
[ShipVia] [int] NULL ,
[Freight] [money] NULL CONSTRAINT [DF_Orders_Freight] DEFAULT (0),
[ShipName] [nvarchar] (40) COLLATE Latin1_General_CI_AS NULL ,
[ShipAddress] [nvarchar] (60) COLLATE Latin1_General_CI_AS NULL ,
[ShipCity] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[ShipRegion] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[ShipPostalCode] [nvarchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[ShipCountry] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[OrderID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Orders_Customers] FOREIGN KEY
(
[CustomerID]
) REFERENCES [Customers] (
[CustomerID]
),
CONSTRAINT [FK_Orders_Employees] FOREIGN KEY
(
[EmployeeID]
) REFERENCES [Employees] (
[EmployeeID]
),
CONSTRAINT [FK_Orders_Shippers] FOREIGN KEY
(
[ShipVia]
) REFERENCES [Shippers] (
[ShipperID]
)
) ON [PRIMARY]
GO
CREATE INDEX [CustomerID] ON [Orders]([CustomerID]) ON [PRIMARY]
GO
CREATE INDEX [CustomersOrders] ON [Orders]([CustomerID]) ON [PRIMARY]
GO
CREATE INDEX [EmployeeID] ON [Orders]([EmployeeID]) ON [PRIMARY]
GO
CREATE INDEX [EmployeesOrders] ON [Orders]([EmployeeID]) ON [PRIMARY]
GO
CREATE INDEX [OrderDate] ON [Orders]([OrderDate]) ON [PRIMARY]
GO
CREATE INDEX [ShippedDate] ON [Orders]([ShippedDate]) ON [PRIMARY]
GO
CREATE INDEX [ShippersOrders] ON [Orders]([ShipVia]) ON [PRIMARY]
GO
CREATE INDEX [ShipPostalCode] ON [Orders]([ShipPostalCode]) ON [PRIMARY]
GO
Regards,
Ryan
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
October 28, 2005 at 1:16 pm
Hi All,
Thank you for your replies. It worked. Now I need to do the same thing for views and functions also. I need to script out the view from the view in a database. same thing for the functions. is there a way to do that?
Thanks,
Sridhar.
October 28, 2005 at 1:48 pm
views ,functions and procs are easy:
sp_helptext viewname
sp_helptext functionname
sp_helptext procedurename
gives you the DDL for the view, or the text of the function/proc:
Lowell
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply