I know the Server Name, and the Sa account password...
Using an ado connection object how do I create a DB at runtime...?
Woka
I know the Server Name, and the Sa account password...
Using an ado connection object how do I create a DB at runtime...?
Woka
I'm not near my SQL server (at home now), so I couldn't test any code, but here is the sql statement syntax . . . .
Quote:
CREATE DATABASE Statement
Creates a new database. You must be in the master database to create a new database.
Syntax
CREATE DATABASE database_name
[ON {DEFAULT | database_device} [= size]
[, database_device [= size]]...]
[LOG ON database_device [= size]
[, database_device [= size]]...]
[FOR LOAD]
where
database_name
Is the name of the new database. Database names must conform to the rules for identifiers.
ON
Indicates that you will specify a location and (optionally) a size for the database.
DEFAULT
Indicates that CREATE DATABASE can put the new database on any default database device(s) shown in the sysdevices table. To specify a size for the database without specifying a location, use ON DEFAULT = size. To change the status of a database device to default, use the sp_diskdefault system stored procedure.
database_device
Is the logical name of the database device on which you want to put a database. A database can occupy different amounts of space on each of several database devices. To create a device, use the DISK INIT statement.
size
Specifies the amount of space, in megabytes, allocated to the database. A database can range from 1 to 2^24 MB.
When an explicit value is not supplied, SQL Server will use the larger value between the size of the model database or the configured database size (configured with sp_configure). For example, if database size is configured to 2 (the default) and the model database has been altered to 3MB, the database will be 3MB.
LOG ON
Is the name of the device that stores the database log. You can specify more than one device in the LOG ON clause.
FOR LOAD
Prevents anyone from using the database between the CREATE DATABASE, ALTER DATABASE, and LOAD statements. FOR LOAD can also be specified with the ALTER DATABASE statement, but only if the database was created with FOR LOAD.
When a database is created with FOR LOAD, SQL Server ensures that only the database owner, by setting the database to dbo use only, can use the database. After the database has been loaded, you can change the database option by using the sp_dboption system stored procedure.
Note This capability is similar to that provided in SQL Server 4.21a via trace flag 1802, but FOR LOAD guarantees that only the database owner can use the database between the CREATE and LOAD statements.
Remarks
When the CREATE DATABASE statement is executed, SQL Server makes a copy of the model database (the database catalog). You can update the model database just like any other database and add your own tables, stored procedures, user-defined datatypes, and so on.
In SQL Server 6.0, CREATE DATABASE is intelligent during the initialization of the device(s) on which the database will reside. When executing a CREATE DATABASE statement, SQL Server determines whether the devices are newly created. If the devices have not previously contained data, the devices will not be reinitialized.
SQL Server can manage up to 32,767 databases, each of which can be up to 1 TB in size. Each database can have a maximum of 32 device fragments. Each time you use CREATE DATABASE or ALTER DATABASE to allocate space on a database device, that allocation represents a device fragment and is entered as a row in the sysusages system table.
The total number of physical devices that can be used or the total size of each logical device is not limited (this is limited by the physical device(s) used, with an absolute maximum of 32 GB for each logical device). Instead, only the total number of logical device fragments is limited. Each device fragment can actually exist on one or more physical devices if some form of hardware or software striping is used. Whenever possible, it is recommended that striping is used. Striping can provide performance improvements and, in some cases, fault tolerance. For more information, see the Microsoft SQL Server Administrator's Companion.
If you don't specify a location and size for a database, the default location is any default database device, and the default size is the size of the model database or the configuration setting for database size. To display available devices, use the sp_helpdevice system stored procedure. To see configuration settings, use the sp_configure system stored procedure.
If SQL Server can't give as much space as you've requested, SQL Server creates the database, allocating as much space as possible (in 0.5-MB increments) on each database device, and then prints a message showing how much space was allocated and where.
Each new database inherits its database option settings from the model database. For example, the database option select into/bulkcopy is originally set to "off." You can change that setting for each newly created database by using the sp_dboption system stored procedure in the model database.
It is important to back up the master database with the DUMP DATABASE statement after each use of CREATE DATABASE. This makes recovery easier and safer in case the master database is damaged. (If you create a database and fail to back up master, you may be able to recover the changes with the DISK REFIT statement.)
To display a report on a database or on all the databases on a SQL Server, execute the sp_helpdb system stored procedure. For a report on the space used in a database, use the sp_spaceused system stored procedure.
Using the LOG ON Clause
For full recoverability, place the transaction log on a different physical device or devices from the device that stores the data. If a disk crashes, you can recover the database from database dumps and from the transaction log dumps. Although you can create a database on a single device and store the transaction log together with the rest of the database, this is not recommended because you would have to rely solely on the DUMP DATABASE statement for recovery.
Note If you do not create the database with the LOG ON option, you can still move the transaction log later by using the sp_logdevice system stored procedure.
The size of the database device required for the transaction log varies according to the amount of update activity and the frequency of transaction log dumps. As a rule, allocate to the transaction log 10 to 25 percent of the space you allocate to the database. It is best to start small, since space allocated to a transaction log database device cannot be reclaimed and cannot be used for storage of data.
Permission
CREATE DATABASE permission defaults to the system administrator, who can transfer it. However, CREATE DATABASE permission is often centralized to maintain control over disk allocation. CREATE DATABASE permission is not included when you use the GRANT statement. For more information, see the Microsoft SQL Server Administrator's Companion.
Examples
A. Create a Single Database on a Single Device
This example creates a 12-MB database called testing on DEVICE1.
CREATE DATABASE testing
ON DEVICE1 = 12
B. Create a Single Database on Two Devices ¾ Separate Data and Log
This example creates a single database (sales) with the data portion on DEVICE3 and the log portion on DEVICE4.
CREATE DATABASE sales
ON DEVICE3 = 125
LOG ON DEVICE4 = 60
C. Create a Database FOR LOAD
This example creates a single database (personnel) with the data portion spanning three devices (DEVICE5, DEVICE6, and DEVICE7) and the log portion on DEVICE8. The total database size is 200 MB composed of 150 MB for data and 50 MB for log.
CREATE DATABASE personnel
ON DEVICE5 = 50, DEVICE6 = 50, DEVICE7 = 50
LOG ON DEVICE8 = 50
FOR LOAD
go
Here is what books online tells.
CREATE DATABASE
Creates a new database and the files used to store the database, or attaches a database from the files of a previously created database.
Syntax
CREATE DATABASE database_name
[ ON
[ < filespec > [ ,...n ] ]
[ , < filegroup > [ ,...n ] ]
]
[ LOG ON { < filespec > [ ,...n ] } ]
[ COLLATE collation_name ]
[ FOR LOAD | FOR ATTACH ]
< filespec > ::=
[ PRIMARY ]
( [ NAME = logical_file_name , ]
FILENAME = 'os_file_name'
[ , SIZE = size ]
[ , MAXSIZE = { max_size | UNLIMITED } ]
[ , FILEGROWTH = growth_increment ] ) [ ,...n ]
< filegroup > ::=
FILEGROUP filegroup_name < filespec > [ ,...n ]
Arguments
database_name
Is the name of the new database. Database names must be unique within a server and conform to the rules for identifiers. database_name can be a maximum of 128 characters, unless no logical name is specified for the log. If no logical log file name is specified, Microsoft® SQL Server™ generates a logical name by appending a suffix to database_name. This limits database_name to 123 characters so that the generated logical log file name is less than 128 characters.
ON
Specifies that the disk files used to store the data portions of the database (data files) are defined explicitly. The keyword is followed by a comma-separated list of <filespec> items defining the data files for the primary filegroup. The list of files in the primary filegroup can be followed by an optional, comma-separated list of <filegroup> items defining user filegroups and their files.
n
Is a placeholder indicating that multiple files can be specified for the new database.
LOG ON
Specifies that the disk files used to store the database log (log files) are explicitly defined. The keyword is followed by a comma-separated list of <filespec> items defining the log files. If LOG ON is not specified, a single log file is automatically created with a system-generated name and a size that is 25 percent of the sum of the sizes of all the data files for the database.
FOR LOAD
This clause is supported for compatibility with earlier versions of Microsoft SQL Server. The database is created with the dbo use only database option turned on, and the status is set to loading. This is not required in SQL Server version 7.0 because the RESTORE statement can recreate a database as part of the restore operation.
FOR ATTACH
Specifies that a database is attached from an existing set of operating system files. There must be a <filespec> entry specifying the first primary file. The only other <filespec> entries needed are those for any files that have a different path from when the database was first created or last attached. A <filespec> entry must be specified for these files. The database attached must have been created using the same code page and sort order as SQL Server. Use the sp_attach_db system stored procedure instead of using CREATE DATABASE FOR ATTACH directly. Use CREATE DATABASE FOR ATTACH only when you must specify more than 16 <filespec> items.
If you attach a database to a server other than the server from which the database was detached, and the detached database was enabled for replication, you should run sp_removedbreplication to remove replication from the database.
collation_name
Specifies the default collation for the database. Collation name can be either a Windows collation name or a SQL collation name. If not specified, the database is assigned the default collation of the SQL Server instance.
For more information about the Windows and SQL collation names, see COLLATE.
PRIMARY
Specifies that the associated <filespec> list defines the primary file. The primary filegroup contains all of the database system tables. It also contains all objects not assigned to user filegroups. The first <filespec> entry in the primary filegroup becomes the primary file, which is the file containing the logical start of the database and its system tables. A database can have only one primary file. If PRIMARY is not specified, the first file listed in the CREATE DATABASE statement becomes the primary file.
NAME
Specifies the logical name for the file defined by the <filespec>. The NAME parameter is not required when FOR ATTACH is specified.
logical_file_name
Is the name used to reference the file in any Transact-SQL statements executed after the database is created. logical_file_name must be unique in the database and conform to the rules for identifiers. The name can be a character or Unicode constant, or a regular or delimited identifier.
FILENAME
Specifies the operating-system file name for the file defined by the <filespec>.
'os_file_name'
Is the path and file name used by the operating system when it creates the physical file defined by the <filespec>. The path in os_file_name must specify a directory on an instance of SQL Server. os_file_name cannot specify a directory in a compressed file system.
If the file is created on a raw partition, os_file_name must specify only the drive letter of an existing raw partition. Only one file can be created on each raw partition. Files on raw partitions do not autogrow; therefore, the MAXSIZE and FILEGROWTH parameters are not needed when os_file_name specifies a raw partition.
SIZE
Specifies the size of the file defined in the <filespec>. When a SIZE parameter is not supplied in the <filespec> for a primary file, SQL Server uses the size of the primary file in the model database. When a SIZE parameter is not specified in the <filespec> for a secondary or log file, SQL Server makes the file 1 MB.
size
Is the initial size of the file defined in the <filespec>. The kilobyte (KB), megabyte (MB), gigabyte (GB), or terabyte (TB) suffixes can be used. The default is MB. Specify a whole number; do not include a decimal. The minimum value for size is 512 KB. If size is not specified, the default is 1 MB. The size specified for the primary file must be at least as large as the primary file of the model database.
MAXSIZE
Specifies the maximum size to which the file defined in the <filespec> can grow.
max_size
Is the maximum size to which the file defined in the <filespec> can grow. The kilobyte (KB), megabyte (MB), gigabyte (GB), or terabyte (TB) suffixes can be used. The default is MB. Specify a whole number; do not include a decimal. If max_size is not specified, the file grows until the disk is full.
Note The Microsoft Windows NT® S/B system log warns the SQL Server system administrator if a disk is almost full.
UNLIMITED
Specifies that the file defined in the <filespec> grows until the disk is full.
FILEGROWTH
Specifies the growth increment of the file defined in the <filespec>. The FILEGROWTH setting for a file cannot exceed the MAXSIZE setting.
growth_increment
Is the amount of space added to the file each time new space is needed. Specify a whole number; do not include a decimal. A value of 0 indicates no growth. The value can be specified in MB, KB, GB, TB, or percent (%). If a number is specified without an MB, KB, or % suffix, the default is MB. When % is specified, the growth increment size is the specified percentage of the size of the file at the time the increment occurs. If FILEGROWTH is not specified, the default value is 10 percent and the minimum value is 64 KB. The size specified is rounded to the nearest 64 KB.
Remarks
You can use one CREATE DATABASE statement to create a database and the files that store the database. SQL Server implements the CREATE DATABASE statement in two steps:
SQL Server uses a copy of the model database to initialize the database and its meta data.
SQL Server then fills the rest of the database with empty pages, except for pages that have internal data recording how the space is used in the database.
Any user-defined objects in the model database are therefore copied to all newly created databases. You can add to the model database any objects, such as tables, views, stored procedures, data types, and so on, to be included in all databases.
Each new database inherits the database option settings from the model database (unless FOR ATTACH is specified). For example, the database option select into/bulkcopy is set to OFF in model and any new databases you create. If you use ALTER DATABASE to change the options for the model database, these option settings are in effect for new databases you create. If FOR ATTACH is specified on the CREATE DATABASE statement, the new database inherits the database option settings of the original database.
A maximum of 32,767 databases can be specified on a server.
There are three types of files used to store a database:
The primary file contains the startup information for the database. The primary file is also used to store data. Every database has one primary file.
Secondary files hold all of the data that does not fit in the primary data file. Databases need not have any secondary data files if the primary file is large enough to hold all of the data in the database. Other databases may be large enough to need multiple secondary data files, or they may use secondary files on separate disk drives to spread the data across multiple disks.
Transaction log files hold the log information used to recover the database. There must be at least one transaction log file for each database, although there may be more than one. The minimum size for a transaction log file is 512 KB.
Every database has at least two files, a primary file and a transaction log file.
Although 'os_file_name' can be any valid operating system file name, the name more clearly reflects the purpose of the file if you use the following recommended extensions.
Continue...
File type File name extension
Primary data file .mdf
Secondary data file .ndf
Transaction log file .ldf
Note The master database should be backed up when a user database is created.
Fractions cannot be specified in the SIZE, MAXSIZE, and FILEGROWTH parameters. To specify a fraction of a megabyte in SIZE parameters, convert to kilobytes by multiplying the number by 1,024. For example, specify 1,536 KB instead of 1.5 MB (1.5 multiplied by 1,024 equals 1,536).
When a simple CREATE DATABASE database_name statement is specified with no additional parameters, the database is made the same size as the model database.
All databases have at least a primary filegroup. All system tables are allocated in the primary filegroup. A database can also have user-defined filegroups. If an object is created with an ON filegroup clause specifying a user-defined filegroup, then all the pages for the object are allocated from the specified filegroup. The pages for all user objects created without an ON filegroup clause, or with an ON DEFAULT clause, are allocated from the default filegroup. When a database is first created the primary filegroup is the default filegroup. You can specify a user-defined filegroup as the default filegroup using ALTER DATABASE:
ALTER DATABASE database_name MODIFY FILEGROUP filegroup_name DEFAULT
Each database has an owner who has the ability to perform special activities in the database. The owner is the user who creates the database. The database owner can be changed with sp_changedbowner.
To display a report on a database, or on all the databases for an instance of SQL Server, execute sp_helpdb. For a report on the space used in a database, use sp_spaceused. For a report on the filegroups in a database use sp_helpfilegroup, and use sp_helpfile for a report of the files in a database.
Earlier versions of SQL Server used DISK INIT statements to create the files for a database before the CREATE DATABASE statement was executed. For backward compatibility with earlier versions of SQL Server, the CREATE DATABASE statement can also create a new database on files or devices created with the DISK INIT statement. For more information, see SQL Server Backward Compatibility Details.
Permissions
CREATE DATABASE permission defaults to members of the sysadmin and dbcreator fixed server roles. Members of the sysadmin and securityadmin fixed server roles can grant CREATE DATABASE permissions to other logins. Members of the sysadmin and dbcreator fixed server role can add other logins to the dbcreator role. The CREATE DATABASE permission must be explicitly granted; it is not granted by the GRANT ALL statement.
CREATE DATABASE permission is usually limited to a few logins to maintain control over disk usage on an instance of SQL Server.
Examples
A. Create a database that specifies the data and transaction log files
This example creates a database called Sales. Because the keyword PRIMARY is not used, the first file (Sales_dat) becomes the primary file. Because neither MB or KB is specified in the SIZE parameter for the Sales_dat file, it defaults to MB and is allocated in megabytes. The Sales_log file is allocated in megabytes because the MB suffix is explicitly stated in the SIZE parameter.
USE master
GO
CREATE DATABASE Sales
ON
( NAME = Sales_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\saledat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
FILENAME = 'c:\program files\microsoft sql server\mssql\data\salelog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
B. Create a database specifying multiple data and transaction log files
This example creates a database called Archive with three 100-MB data files and two 100-MB transaction log files. The primary file is the first file in the list and is explicitly specified with the PRIMARY keyword. The transaction log files are specified following the LOG ON keywords. Note the extensions used for the files in the FILENAME option: .mdf is used for primary data files, .ndf is used for the secondary data files, and .ldf is used for transaction log files.
USE master
GO
CREATE DATABASE Archive
ON
PRIMARY ( NAME = Arch1,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\archdat1.mdf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Arch2,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\archdat2.ndf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Arch3,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\archdat3.ndf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20)
LOG ON
( NAME = Archlog1,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\archlog1.ldf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Archlog2,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\archlog2.ldf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20)
GO
C. Create a simple database
This example creates a database called Products and specifies a single file. The file specified becomes the primary file, and a 1-MB transaction log file is automatically created. Because neither MB or KB is specified in the SIZE parameter for the primary file, the primary file is allocated in megabytes. Because there is no <filespec> for the transaction log file, the transaction log file has no MAXSIZE and can grow to fill all available disk space.
USE master
GO
CREATE DATABASE Products
ON
( NAME = prods_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\prods.mdf',
SIZE = 4,
MAXSIZE = 10,
FILEGROWTH = 1 )
GO
D. Create a database without specifying files
This example creates a database named mytest and creates a corresponding primary and transaction log file. Because the statement has no <filespec> items, the primary database file is the size of the model database primary file. The transaction log is the size of the model database transaction log file. Because MAXSIZE is not specified, the files can grow to fill all available disk space.
CREATE DATABASE mytest
E. Create a database without specifying SIZE
This example creates a database named products2. The file prods2_dat becomes the primary file with a size equal to the size of the primary file in the model database. The transaction log file is created automatically and is 25 percent of the size of the primary file, or 512 KB, whichever is larger. Because MAXSIZE is not specified, the files can grow to fill all available disk space.
USE master
GO
CREATE DATABASE Products2
ON
( NAME = prods2_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\prods2.mdf' )
GO
F. Create a database with filegroups
This example creates a database named sales with three filegroups:
The primary filegroup with the files Spri1_dat and Spri2_dat. The FILEGROWTH increments for these files is specified as 15 percent.
A filegroup named SalesGroup1 with the files SGrp1Fi1 and SGrp1Fi2.
A filegroup named SalesGroup2 with the files SGrp2Fi1 and SGrp2Fi2.
CREATE DATABASE Sales
ON PRIMARY
( NAME = SPri1_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\SPri1dat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15% ),
( NAME = SPri2_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\SPri2dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15% ),
FILEGROUP SalesGroup1
( NAME = SGrp1Fi1_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG1Fi1dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
( NAME = SGrp1Fi2_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG1Fi2dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
FILEGROUP SalesGroup2
( NAME = SGrp2Fi1_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG2Fi1dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
( NAME = SGrp2Fi2_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG2Fi2dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
FILENAME = 'c:\program files\microsoft sql server\mssql\data\salelog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
G. Attach a database
Example B creates a database named Archive with the following physical files:
c:\program files\microsoft sql server\mssql\data\archdat1.mdf
c:\program files\microsoft sql server\mssql\data\archdat2.ndf
c:\program files\microsoft sql server\mssql\data\archdat3.ndf
c:\program files\microsoft sql server\mssql\data\archlog1.ldf
c:\program files\microsoft sql server\mssql\data\archlog2.ldf
The database can be detached using the sp_detach_db stored procedure, and then reattached using CREATE DATABASE with the FOR ATTACH clause:
sp_detach_db Archive
GO
CREATE DATABASE Archive
ON PRIMARY (FILENAME = 'c:\program files\microsoft sql server\mssql\data\archdat1.mdf')
FOR ATTACH
GO
Continue...
H. Use raw partitions
This example creates a database called Employees using raw partitions. The raw partitions must exist when the statement is executed, and only one file can go on each raw partition.
USE master
GO
CREATE DATABASE Employees
ON
( NAME = Empl_dat,
FILENAME = 'f:',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
FILENAME = 'g:',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
I. Use mounted drives
This example creates a database called Employees using mounted drives pointing to raw partitions. This feature is available only in Microsoft® Windows® 2000 Server. The mounted drives and raw partitions must exist when the statement is executed, and only one file can go on each raw partition. When creating a database file on a mounted drive, a trailing backslash (\) must end the drive path.
USE master
GO
CREATE DATABASE Employees
ON
( NAME = Empl_dat,
FILENAME = 'd:\sample data dir\',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
FILENAME = 'd:\sample log dir\',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
Here is an example of what a script looks like.
Code:/****** Object: Database StarSchema Script Date: 14/06/2003 05:54:02 PM ******/
CREATE DATABASE [StarSchema] ON (NAME = 'StarSchema', FILENAME =
'D:\Program Files\Microsoft SQL Server\MSSQL$SQLSERVER\Data\StarSchema.mdf' ,
SIZE = 4, FILEGROWTH = 10%) LOG ON (NAME = 'StarSchema_log', FILENAME =
'D:\Program Files\Microsoft SQL Server\MSSQL$SQLSERVER\Data\StarSchema.ldf' ,
SIZE = 1, FILEGROWTH = 10%)
GO
use [StarSchema]
GO
/****** Object: Table [dbo].[Categories] Script Date: 14/06/2003 05:54:02 PM ******/
CREATE TABLE [dbo].[Categories] (
[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryName] [nvarchar] (15) COLLATE Latin1_General_CI_AS NOT NULL ,
[Description] [ntext] COLLATE Latin1_General_CI_AS NULL ,
[Picture] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[Customers] Script Date: 14/06/2003 05:54:03 PM ******/
CREATE TABLE [dbo].[Customers] (
[CustomerID] [nchar] (5) COLLATE Latin1_General_CI_AS NOT NULL ,
[CompanyName] [nvarchar] (40) COLLATE Latin1_General_CI_AS NOT NULL ,
[ContactName] [nvarchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[ContactTitle] [nvarchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[Address] [nvarchar] (60) COLLATE Latin1_General_CI_AS NULL ,
[City] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[Region] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[PostalCode] [nvarchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[Country] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[Phone] [nvarchar] (24) COLLATE Latin1_General_CI_AS NULL ,
[Fax] [nvarchar] (24) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Employees] Script Date: 14/06/2003 05:54:03 PM ******/
CREATE TABLE [dbo].[Employees] (
[EmployeeID] [int] IDENTITY (1, 1) NOT NULL ,
[LastName] [nvarchar] (20) COLLATE Latin1_General_CI_AS NOT NULL ,
[FirstName] [nvarchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[Title] [nvarchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[TitleOfCourtesy] [nvarchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[BirthDate] [datetime] NULL ,
[HireDate] [datetime] NULL ,
[Address] [nvarchar] (60) COLLATE Latin1_General_CI_AS NULL ,
[City] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[Region] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[PostalCode] [nvarchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[Country] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[HomePhone] [nvarchar] (24) COLLATE Latin1_General_CI_AS NULL ,
[Extension] [nvarchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[Photo] [image] NULL ,
[Notes] [ntext] COLLATE Latin1_General_CI_AS NULL ,
[ReportsTo] [int] NULL ,
[PhotoPath] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[FactTable] Script Date: 14/06/2003 05:54:03 PM ******/
CREATE TABLE [dbo].[FactTable] (
[OrderID_Key] [int] NOT NULL ,
[CustomerID_Key] [nchar] (5) COLLATE Latin1_General_CI_AS NOT NULL ,
[ShipperID_Key] [int] NOT NULL ,
[EmployeeID_Key] [int] NOT NULL ,
[ProductID_Key] [int] NOT NULL ,
[SupplierID_Key] [int] NOT NULL ,
[CategoryID_Key] [int] NOT NULL ,
[TimeID_Key] [datetime] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Order Details] Script Date: 14/06/2003 05:54:04 PM ******/
CREATE TABLE [dbo].[Order Details] (
[OrderID] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
[UnitPrice] [money] NOT NULL ,
[Quantity] [smallint] NOT NULL ,
[Discount] [real] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Orders] Script Date: 14/06/2003 05:54:04 PM ******/
CREATE TABLE [dbo].[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 ,
[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
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Products] Script Date: 14/06/2003 05:54:04 PM ******/
CREATE TABLE [dbo].[Products] (
[ProductID] [int] IDENTITY (1, 1) NOT NULL ,
[ProductName] [nvarchar] (40) COLLATE Latin1_General_CI_AS NOT NULL ,
[SupplierID] [int] NULL ,
[CategoryID] [int] NULL ,
[QuantityPerUnit] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[UnitPrice] [money] NULL ,
[UnitsInStock] [smallint] NULL ,
[UnitsOnOrder] [smallint] NULL ,
[ReorderLevel] [smallint] NULL ,
[Discontinued] [bit] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Shippers] Script Date: 14/06/2003 05:54:04 PM ******/
CREATE TABLE [dbo].[Shippers] (
[ShipperID] [int] IDENTITY (1, 1) NOT NULL ,
[CompanyName] [nvarchar] (40) COLLATE Latin1_General_CI_AS NOT NULL ,
[Phone] [nvarchar] (24) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Suppliers] Script Date: 14/06/2003 05:54:04 PM ******/
CREATE TABLE [dbo].[Suppliers] (
[SupplierID] [int] IDENTITY (1, 1) NOT NULL ,
[CompanyName] [nvarchar] (40) COLLATE Latin1_General_CI_AS NOT NULL ,
[ContactName] [nvarchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[ContactTitle] [nvarchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[Address] [nvarchar] (60) COLLATE Latin1_General_CI_AS NULL ,
[City] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[Region] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[PostalCode] [nvarchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[Country] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[Phone] [nvarchar] (24) COLLATE Latin1_General_CI_AS NULL ,
[Fax] [nvarchar] (24) COLLATE Latin1_General_CI_AS NULL ,
[HomePage] [ntext] COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[TimeDimension] Script Date: 14/06/2003 05:54:04 PM ******/
CREATE TABLE [dbo].[TimeDimension] (
[TimeID] [datetime] NOT NULL ,
[Week] [int] NOT NULL ,
[MonthName] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[MonthNumber] [int] NOT NULL ,
[Quarter] [int] NOT NULL ,
[YearNumber] [int] NOT NULL
) ON [PRIMARY]
GO
Continue...
Code:ALTER TABLE [dbo].[Categories] WITH NOCHECK ADD
CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED
(
[CategoryID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Customers] WITH NOCHECK ADD
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
[CustomerID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Employees] WITH NOCHECK ADD
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
(
[EmployeeID]
) ON [PRIMARY] ,
CONSTRAINT [CK_Birthdate] CHECK ([BirthDate] < getdate())
GO
ALTER TABLE [dbo].[FactTable] WITH NOCHECK ADD
CONSTRAINT [PK_FactTable] PRIMARY KEY CLUSTERED
(
[OrderID_Key],
[CustomerID_Key],
[ShipperID_Key],
[EmployeeID_Key],
[ProductID_Key],
[SupplierID_Key],
[CategoryID_Key],
[TimeID_Key]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Order Details] WITH NOCHECK ADD
CONSTRAINT [DF_Order_Details_UnitPrice] DEFAULT (0) FOR [UnitPrice],
CONSTRAINT [DF_Order_Details_Quantity] DEFAULT (1) FOR [Quantity],
CONSTRAINT [DF_Order_Details_Discount] DEFAULT (0) FOR [Discount],
CONSTRAINT [PK_Order Details] PRIMARY KEY CLUSTERED
(
[OrderID],
[ProductID]
) ON [PRIMARY] ,
CONSTRAINT [CK_Discount] CHECK ([Discount] >= 0 and [Discount] <= 1),
CONSTRAINT [CK_Quantity] CHECK ([Quantity] > 0),
CONSTRAINT [CK_UnitPrice] CHECK ([UnitPrice] >= 0)
GO
ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD
CONSTRAINT [DF_Orders_Freight] DEFAULT (0) FOR [Freight],
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[OrderID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Products] WITH NOCHECK ADD
CONSTRAINT [DF_Products_UnitPrice] DEFAULT (0) FOR [UnitPrice],
CONSTRAINT [DF_Products_UnitsInStock] DEFAULT (0) FOR [UnitsInStock],
CONSTRAINT [DF_Products_UnitsOnOrder] DEFAULT (0) FOR [UnitsOnOrder],
CONSTRAINT [DF_Products_ReorderLevel] DEFAULT (0) FOR [ReorderLevel],
CONSTRAINT [DF_Products_Discontinued] DEFAULT (0) FOR [Discontinued],
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
[ProductID]
) ON [PRIMARY] ,
CONSTRAINT [CK_Products_UnitPrice] CHECK ([UnitPrice] >= 0),
CONSTRAINT [CK_ReorderLevel] CHECK ([ReorderLevel] >= 0),
CONSTRAINT [CK_UnitsInStock] CHECK ([UnitsInStock] >= 0),
CONSTRAINT [CK_UnitsOnOrder] CHECK ([UnitsOnOrder] >= 0)
GO
ALTER TABLE [dbo].[Shippers] WITH NOCHECK ADD
CONSTRAINT [PK_Shippers] PRIMARY KEY CLUSTERED
(
[ShipperID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Suppliers] WITH NOCHECK ADD
CONSTRAINT [PK_Suppliers] PRIMARY KEY CLUSTERED
(
[SupplierID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TimeDimension] WITH NOCHECK ADD
CONSTRAINT [PK_TimeDimension] PRIMARY KEY CLUSTERED
(
[TimeID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[FactTable] ADD
CONSTRAINT [FK_FactTable_Categories] FOREIGN KEY
(
[CategoryID_Key]
) REFERENCES [dbo].[Categories] (
[CategoryID]
),
CONSTRAINT [FK_FactTable_Customers] FOREIGN KEY
(
[CustomerID_Key]
) REFERENCES [dbo].[Customers] (
[CustomerID]
),
CONSTRAINT [FK_FactTable_Employees] FOREIGN KEY
(
[EmployeeID_Key]
) REFERENCES [dbo].[Employees] (
[EmployeeID]
),
CONSTRAINT [FK_FactTable_Orders] FOREIGN KEY
(
[OrderID_Key]
) REFERENCES [dbo].[Orders] (
[OrderID]
),
CONSTRAINT [FK_FactTable_Products] FOREIGN KEY
(
[ProductID_Key]
) REFERENCES [dbo].[Products] (
[ProductID]
),
CONSTRAINT [FK_FactTable_Shippers] FOREIGN KEY
(
[ShipperID_Key]
) REFERENCES [dbo].[Shippers] (
[ShipperID]
),
CONSTRAINT [FK_FactTable_Suppliers] FOREIGN KEY
(
[SupplierID_Key]
) REFERENCES [dbo].[Suppliers] (
[SupplierID]
),
CONSTRAINT [FK_FactTable_TimeDimension] FOREIGN KEY
(
[TimeID_Key]
) REFERENCES [dbo].[TimeDimension] (
[TimeID]
)
GO
ALTER TABLE [dbo].[Order Details] ADD
CONSTRAINT [FK_Order Details_Orders] FOREIGN KEY
(
[OrderID]
) REFERENCES [dbo].[Orders] (
[OrderID]
)
GO
I know this is a little late...but thanks for your help on this post. Much appreciated :D
There is no difference in how this is done in .NET or vb6 is there?
Woof
If it's SQL isnt it independent of the calling pratform?
yup. but .net might of had some function DBUpgrade class somewhere in System.Data.SQLClient, or something like that.
Woof
EASYEST way Woka is to create the DB in EM and all the tables and fields etc. Then right click the db > "All Tasks" > "Generate Script...".
This will write out all the SQL code necessary to create the db as you currently have it. You can select options to script keys, users,
extended properties,etc. Then take the script over to QA and run it with a different DB name and/or server name. Once it executes
and creates your db the way you need it you can execute the SQL from a Command Object.
Ruff! ;)
yup...that would work...if it's what I wanted :D
I am writting a generic Database class for my software.
I have a decent one running in vb6. Just porting it to .NET
I use it fopr DB upgrades etc. Makes my life SOOO much easier, and makes maintaining and updating the DB well easy.
I'll post it here tonight.
Woof
Oh, but if you only knew that this is what you want. :D
Can you take a look at my ASP.NET thread Badger?