2y8R2BfM

1USE master;
2GO
3--удаление БД
4GO
5IF DB_ID (N'Library') IS NOT NULL
6DROP DATABASE Library;
7GO
8
9USE master;
10GO
11--создание БД
12CREATE DATABASE Library
13    ON ( NAME = Library_dat, 
14        FILENAME = '/tmp/data/librarydat.mdf',
15        SIZE = 10, MAXSIZE = 150, FILEGROWTH = 5% )
16    LOG ON ( NAME = Library_log, 
17        FILENAME = '/tmp/data/librarydat.ldf',
18        SIZE = 5MB, MAXSIZE = 150MB, FILEGROWTH = 5MB )
19GO
20
21USE Library;
22GO
23--удаление таблицы
24GO
25IF OBJECT_ID (N'Bibliotheque') IS NOT NULL
26DROP TABLE Bibliotheque;
27GO
28
29USE Library;
30GO
31--создание таблицы
32CREATE TABLE Bibliotheque (
33    author VARCHAR (25),
34    book VARCHAR (25),
35    year INT,
36    audiobook VARCHAR (25),
37    country VARCHAR (25)) ;
38GO
39
40USE Library;
41GO
42INSERT INTO Bibliotheque (author, book, year, audiobook, country)
43VALUES ('Den Brown', 'Inferno', 2013, 'Inferno', 'USA'),
44       ('Unknown', 'Excalibur', 2018, 'Excalibur', 'England');
45GO
46
47USE Library;
48--вывод таблицы на экран
49SELECT * FROM Bibliotheque;
50
51USE master;
52GO
53--добавление файловой группы и файла данных
54ALTER DATABASE Library
55ADD FILEGROUP LargeFileGroup;
56GO
57USE master;
58ALTER DATABASE Library 
59ADD FILE 
60    (
61        NAME = Library_PrimaryData,
62        FILENAME = '/tmp/data/library.ndf',
63        SIZE = 5MB,
64        MAXSIZE = 100MB,
65        FILEGROWTH = 5MB
66    ),
67    (
68        NAME = Library_SecondaryData,
69        FILENAME = '/tmp/data/library1.ndf',
70        SIZE = 5MB,
71        MAXSIZE = 100MB,
72        FILEGROWTH = 5MB
73    )
74TO FILEGROUP LargeFileGroup;
75GO
76
77USE master;
78GO
79--делаем файловую группу группой по умолчанию
80ALTER DATABASE Library
81MODIFY FILEGROUP LargeFileGroup DEFAULT;
82GO
83
84
85USE Library;
86GO
87--удаление таблицы
88GO
89IF OBJECT_ID (N'TimeTable') IS NOT NULL
90DROP TABLE TimeTable;
91GO
92
93USE Library;
94GO
95--создание временной таблицы
96CREATE TABLE TimeTable (
97    myName VARCHAR (25),
98    hobby VARCHAR (25),
99    year INT,
100    book VARCHAR (25),
101    country VARCHAR(25)) ;
102GO
103
104INSERT INTO TimeTable (myName, hobby, year, book, country)
105VALUES ('Nika', 'climbing', 1999, 'Adventures of Tomek', 'Russia');
106GO
107
108--вывод таблицы на экран
109SELECT * FROM TimeTable;
110
111--вывод файловой группы
112USE Library;
113SELECT name, type_desc
114FROM sys.filegroups;
115
116CREATE UNIQUE CLUSTERED INDEX TimeTable
117    ON TimeTable (myName, hobby, year, book, country)
118    ON [primary]
119
120USE master;
121GO
122
123ALTER DATABASE Library
124MODIFY FILEGROUP [primary] DEFAULT;
125GO
126
127--ALTER SCHEMA Lab5 TRANSFER dbo.TimeTable;
128--GO
129
130/*
131
132USE Library;
133GO
134--удаление таблицы
135GO
136IF OBJECT_ID (N'TimeTable') IS NOT NULL
137DROP TABLE TimeTable;
138GO
139
140USE Library;
141GO
142--создание временной таблицы
143CREATE TABLE TimeTable (
144    myName VARCHAR (25),
145    hobby VARCHAR (25),
146    year INT,
147    book VARCHAR (25),
148    country VARCHAR(25)) ;
149GO
150
151INSERT INTO TimeTable (myName, hobby, year, book, country)
152VALUES ('Nika', 'climbing', 1999, 'Adventures of Tomek', 'Russia');
153GO
154
155--вывод таблицы на экран
156SELECT * FROM TimeTable;
157*/
158
159
160--удаление таблицы
161GO
162IF OBJECT_ID (N'Reserve') IS NOT NULL
163DROP TABLE Reserve;
164GO
165
166--SELECT INTO Reserve (names, hobby, year, book, country)
167--SELECT * INTO Reserve FROM TimeTable
168--SELECT * FROM Reserve
169
170/*DROP TABLE TimeTable;
171GO
172*/
173
174
175
176USE Library;
177GO
178
179/*
180--ALTER DATABASE Library
181    DROP TABLE TimeTable;
182GO
183*/
184
185--удаление файла из файловой группы
186ALTER DATABASE Library
187    REMOVE FILE Library_PrimaryData;
188GO
189ALTER DATABASE Library
190REMOVE FILE Library_SecondaryData;
191GO
192
193USE master;
194GO
195--удаление файловой группы
196ALTER DATABASE Library
197REMOVE FILEGROUP LargeFileGroup;
198GO
199
200use Library;
201GO
202
203--создание схемы
204CREATE SCHEMA Lab5
205   
206GO
207
208/*GO
209IF OBJECT_ID (N'Lab') IS NOT NULL
210DROP SCHEMA Lab;
211GO
212
213CREATE SCHEMA Lab;
214GO
215
216ALTER SCHEMA lab TRANSFER OBJECT::dbo.TimeTable;
217GO
218
219DROP SCHEMA lab;
220GO*/
221
222--добавление таблицы в схему
223ALTER SCHEMA Lab5 TRANSFER dbo.TimeTable;
224GO
225
226--SELECT * INTO Reserve2 FROM TimeTable
227
228--ALTER SCHEMA dbo TRANSFER Lab5.Reserve2;
229--GO
230
231--удаление схемы
232DROP TABLE Lab5.TimeTable;
233DROP SCHEMA Lab5;
234GO
235
236--DROP SCHEMA  [ IF EXISTS ] S
237
238SELECT name FROM sys.schemas
239SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA