USE master GO IF NOT EXISTS SELECT name FROM sys databases WHERE name

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
USE master
GO
IF NOT EXISTS (
SELECT [name]
FROM sys.databases
WHERE [name] = N'Library'
)
CREATE DATABASE Library
ON (
NAME = Library_data,
FILENAME = "/home/robert_boston/DBlabs/lab5/librarydata.mdf",
SIZE = 10,
MAXSIZE = UNLIMITED,
FILEGROWTH = 5%
)
LOG ON (
NAME = Library_log,
FILENAME ="/home/robert_boston/DBlabs/lab5/librarylog.ldf",
SIZE = 5 MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB
)
GO
USE Library
Go
IF OBJECT_ID('[dbo].[Books]', 'U') IS NOT NULL
DROP TABLE [dbo].[Books]
GO
CREATE TABLE [dbo].[Books]
(
[Name] NVARCHAR(50) NOT NULL,
[YearOfPub] INT NOT NULL
);
GO
USE master
GO
ALTER DATABASE Library
ADD FILEGROUP LibFileGroup;
GO
ALTER DATABASE Library
ADD FILE (
NAME = libFile,
FILENAME = '/home/robert_boston/DBlabs/lab5/libFile.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)
TO FILEGROUP LibFileGroup;
GO
ALTER DATABASE Library
MODIFY FILEGROUP LibFileGroup DEFAULT;
GO
USE Library
GO
IF OBJECT_ID('[dbo].[Authors]', 'U') IS NOT NULL
DROP TABLE [dbo].[Authors]
GO
CREATE TABLE [dbo].[Authors]
(
[FirstName] NVARCHAR(50) NOT NULL,
[LastName] NVARCHAR(50) NOT NULL
);
GO
CREATE UNIQUE CLUSTERED INDEX Author
ON [dbo].[Authors] (FirstName, LastName)
ON [PRIMARY]
GO
ALTER DATABASE Library
MODIFY FILEGROUP [PRIMARY] DEFAULT
GO
ALTER DATABASE Library
REMOVE FILE LibFile
GO
ALTER DATABASE Library
REMOVE FILEGROUP LibFileGroup
GO
CREATE SCHEMA dep
GO
ALTER SCHEMA dep TRANSFER dbo.Books
GO
ALTER SCHEMA dbo TRANSFER dep.Books
GO
DROP SCHEMA dep