kvnsdr
asked on
SQL Create Database and Tables Neccessary code??????
This may be a C# question but I'm sure SQL EE will know as well.
I need to know what actual code is needed to create a new database on a new install computer.
I can save the MS SQL scripts easy enough, but the don't code well in C#....
Q. What SQL syntax is really neccessary for a new Database install including permissions?
Here is some of the questionable code possibly not needed in C#?
" BEGIN CREATE DATABASE [Accounting] ;"+
" EXEC dbo.sp_dbcmptlevel @dbname=N'Accounting', @new_cmptlevel=80 ;"+
" ALTER DATABASE [Accounting] SET ANSI_NULL_DEFAULT OFF ;"+
" ALTER DATABASE [Accounting] SET ANSI_NULLS OFF ;"+
" ALTER DATABASE [Accounting] SET ANSI_PADDING OFF ;"+
" ALTER DATABASE [Accounting] SET ANSI_WARNINGS OFF ;"+
" ALTER DATABASE [Accounting] SET ARITHABORT OFF ;"+
" ALTER DATABASE [Accounting] SET AUTO_CLOSE ON ;"+
" ALTER DATABASE [Accounting] SET AUTO_CREATE_STATISTICS ON ;"+
" ALTER DATABASE [Accounting] SET AUTO_SHRINK OFF ;"+
" ALTER DATABASE [Accounting] SET AUTO_UPDATE_STATISTICS ON ;"+
" ALTER DATABASE [Accounting] SET CURSOR_CLOSE_ON_COMMIT OFF ;"+
" ALTER DATABASE [Accounting] SET CURSOR_DEFAULT GLOBAL ;"+
" ALTER DATABASE [Accounting] SET CONCAT_NULL_YIELDS_NULL OFF ;"+
" ALTER DATABASE [Accounting] SET NUMERIC_ROUNDABORT OFF ;"+
" ALTER DATABASE [Accounting] SET QUOTED_IDENTIFIER OFF ;"+
" ALTER DATABASE [Accounting] SET RECURSIVE_TRIGGERS OFF ;"+
" ALTER DATABASE [Accounting] SET READ_WRITE ;"+
" ALTER DATABASE [Accounting] SET RECOVERY SIMPLE ;"+
" ALTER DATABASE [Accounting] SET MULTI_USER ;"+
" ALTER DATABASE [Accounting] SET TORN_PAGE_DETECTION ON ;"+
" if ( ((@@microsoftversion / power(2, 24) = 8) and "+
" (@@microsoftversion & 0xffff >= 760)) or (@@microsoftversion / power(2, 24) >= 9) ) ;"+
" begin exec dbo.sp_dboption @dbname = N'Accounting', @optname = 'db chaining', @optvalue = 'OFF' end ;"+
" USE [Accounting] ;"+
@" IF NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name = N'BUILTIN\Administrators') "+
@" EXEC dbo.sp_grantdbaccess @loginame = N'BUILTIN\Administrators', @name_in_db = N'BUILTIN\Administrators' ;"+
" SET ANSI_NULLS ON ; "+
" SET QUOTED_IDENTIFIER ON ;"+
" IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Reports ]') "+
" AND OBJECTPROPERTY(id, N'IsUserTable') = 1) ;"+
" BEGIN CREATE TABLE [dbo].[Reports]([ID] [int] IDENTITY(1,1) NOT NULL, [DateTime] [datetime] NULL, "+
" [Priority] [varchar](100) NULL, [User] [varchar](50) NULL, [Name] [varchar](50) NULL, [Middle] "+
" [varchar](50) NULL, [Last] [varchar](50) NULL, [Address] [varchar](100) NULL, [Zip] [int] NULL, "+
" ON [PRIMARY] END ;"+
" SET ANSI_NULLS ON ; "+
" SET QUOTED_IDENTIFIER ON ;"+
" SET ANSI_NULLS ON ;"+
" SET QUOTED_IDENTIFIER ON ;"+
I need to know what actual code is needed to create a new database on a new install computer.
I can save the MS SQL scripts easy enough, but the don't code well in C#....
Q. What SQL syntax is really neccessary for a new Database install including permissions?
Here is some of the questionable code possibly not needed in C#?
" BEGIN CREATE DATABASE [Accounting] ;"+
" EXEC dbo.sp_dbcmptlevel @dbname=N'Accounting', @new_cmptlevel=80 ;"+
" ALTER DATABASE [Accounting] SET ANSI_NULL_DEFAULT OFF ;"+
" ALTER DATABASE [Accounting] SET ANSI_NULLS OFF ;"+
" ALTER DATABASE [Accounting] SET ANSI_PADDING OFF ;"+
" ALTER DATABASE [Accounting] SET ANSI_WARNINGS OFF ;"+
" ALTER DATABASE [Accounting] SET ARITHABORT OFF ;"+
" ALTER DATABASE [Accounting] SET AUTO_CLOSE ON ;"+
" ALTER DATABASE [Accounting] SET AUTO_CREATE_STATISTICS ON ;"+
" ALTER DATABASE [Accounting] SET AUTO_SHRINK OFF ;"+
" ALTER DATABASE [Accounting] SET AUTO_UPDATE_STATISTICS ON ;"+
" ALTER DATABASE [Accounting] SET CURSOR_CLOSE_ON_COMMIT OFF ;"+
" ALTER DATABASE [Accounting] SET CURSOR_DEFAULT GLOBAL ;"+
" ALTER DATABASE [Accounting] SET CONCAT_NULL_YIELDS_NULL OFF ;"+
" ALTER DATABASE [Accounting] SET NUMERIC_ROUNDABORT OFF ;"+
" ALTER DATABASE [Accounting] SET QUOTED_IDENTIFIER OFF ;"+
" ALTER DATABASE [Accounting] SET RECURSIVE_TRIGGERS OFF ;"+
" ALTER DATABASE [Accounting] SET READ_WRITE ;"+
" ALTER DATABASE [Accounting] SET RECOVERY SIMPLE ;"+
" ALTER DATABASE [Accounting] SET MULTI_USER ;"+
" ALTER DATABASE [Accounting] SET TORN_PAGE_DETECTION ON ;"+
" if ( ((@@microsoftversion / power(2, 24) = 8) and "+
" (@@microsoftversion & 0xffff >= 760)) or (@@microsoftversion / power(2, 24) >= 9) ) ;"+
" begin exec dbo.sp_dboption @dbname = N'Accounting', @optname = 'db chaining', @optvalue = 'OFF' end ;"+
" USE [Accounting] ;"+
@" IF NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name = N'BUILTIN\Administrators')
@" EXEC dbo.sp_grantdbaccess @loginame = N'BUILTIN\Administrators',
" SET ANSI_NULLS ON ; "+
" SET QUOTED_IDENTIFIER ON ;"+
" IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Reports
" AND OBJECTPROPERTY(id, N'IsUserTable') = 1) ;"+
" BEGIN CREATE TABLE [dbo].[Reports]([ID] [int] IDENTITY(1,1) NOT NULL, [DateTime] [datetime] NULL, "+
" [Priority] [varchar](100) NULL, [User] [varchar](50) NULL, [Name] [varchar](50) NULL, [Middle] "+
" [varchar](50) NULL, [Last] [varchar](50) NULL, [Address] [varchar](100) NULL, [Zip] [int] NULL, "+
" ON [PRIMARY] END ;"+
" SET ANSI_NULLS ON ; "+
" SET QUOTED_IDENTIFIER ON ;"+
" SET ANSI_NULLS ON ;"+
" SET QUOTED_IDENTIFIER ON ;"+
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.