kLdvRPVu

1-- Modify table column type
2DROP PROC IF EXISTS uspModifyColumnType
3GO
4
5CREATE PROC uspModifyColumnType
6@table VARCHAR(30),
7@column VARCHAR(30),
8@desiredDataType VARCHAR(30)
9AS
10	DECLARE @statement NVARCHAR(4000)
11	SELECT @statement = 'ALTER TABLE ' + QUOTENAME(@table) + ' ALTER COLUMN ' + QUOTENAME(@column) + QUOTENAME(@desiredDataType)
12	EXECUTE sp_executesql @statement
13GO
14EXEC uspModifyColumnType 'Products', 'price', 'INT'
15
16-- Add a column to a table
17DROP PROC IF EXISTS uspAddColumn 
18GO
19
20CREATE PROC uspAddColumn
21@table VARCHAR(30),
22@column VARCHAR(30),
23@desiredDataType VARCHAR(30)
24AS
25	DECLARE @statement NVARCHAR(4000)
26	SELECT @statement = 'ALTER TABLE ' + QUOTENAME(@table) + ' ADD ' + QUOTENAME(@column) + QUOTENAME(@desiredDataType)
27	EXECUTE sp_executesql @statement
28GO
29EXEC uspAddColumn 'Products', 'available_quantity', 'INT'
30
31-- Drop a column from a table
32DROP PROC IF EXISTS uspDropColumn 
33GO
34
35CREATE PROC uspDropColumn
36@table VARCHAR(30),
37@column VARCHAR(30)
38AS
39	DECLARE @statement NVARCHAR(4000)
40	SELECT @statement = 'ALTER TABLE ' + QUOTENAME(@table) + ' DROP COLUMN ' + QUOTENAME(@column)
41	EXECUTE sp_executesql @statement
42GO
43EXEC uspDropColumn 'Products', 'available_quantity'
44
45-- Add default constraint
46DROP PROC IF EXISTS uspAddDefaultConstraint 
47GO
48
49CREATE PROC uspAddDefaultConstraint
50@table VARCHAR(30),
51@constraint_name VARCHAR(30),
52@default_value VARCHAR(30),
53@column VARCHAR(30)
54AS
55	DECLARE @statement NVARCHAR(4000)
56	SELECT @statement = 'ALTER TABLE ' + QUOTENAME(@table) + ' ADD CONSTRAINT ' + 
57										 QUOTENAME(@constraint_name) + ' DEFAULT ' + @default_value + ' FOR ' + QUOTENAME(@column)
58	EXECUTE sp_executesql @statement
59GO
60EXEC uspAddDefaultConstraint 'Products', 'price_constraint', 50, 'price'
61 
62-- Drop default constraint
63DROP PROC IF EXISTS uspDropDefaultConstraint 
64GO
65
66CREATE PROC uspDropDefaultConstraint
67@table VARCHAR(30),
68@constraint_name VARCHAR(30)
69AS
70	DECLARE @statement NVARCHAR(4000)
71	SELECT @statement = 'ALTER TABLE ' + QUOTENAME(@table) + ' DROP CONSTRAINT ' + QUOTENAME(@constraint_name)
72
73	EXEC sp_executesql @statement
74GO
75EXEC uspDropDefaultConstraint 'Products', 'price_constraint'
76
77-- Add primary key to table
78DROP PROC IF EXISTS uspAddPrimaryKey 
79GO
80
81CREATE PROC uspAddPrimaryKey
82@table VARCHAR(30),
83@column VARCHAR(30)
84AS
85	DECLARE @statement NVARCHAR(4000)
86	SELECT @statement = 'ALTER TABLE ' + QUOTENAME(@table) + ' ADD PRIMARY KEY (' + QUOTENAME(@column) + ')'
87	EXECUTE sp_executesql @statement
88GO
89EXEC uspAddPrimaryKey 'Ratings', 'id'
90
91-- Delete primary key from table
92DROP PROC IF EXISTS uspDropPrimaryKey
93GO
94
95CREATE PROC uspDropPrimaryKey
96@table VARCHAR(30),
97@column VARCHAR(30)
98AS
99	DECLARE @statement NVARCHAR(4000)
100	SELECT @statement = 'ALTER TABLE ' + QUOTENAME(@table) + ' DROP CONSTRAINT ' + QUOTENAME(@column) + ';'
101	FROM sys.key_constraints
102	WHERE [type] = 'PK'
103	AND [parent_object_id] = OBJECT_ID(@table);
104
105	EXEC sp_executesql @statement
106GO
107EXEC uspDropPrimaryKey 'Ratings', 'PK__Ratings__3213E83F586BC2B1'
108
109-- Add a candidate key to a table
110DROP PROC IF EXISTS uspAddCandidateKey
111GO
112
113CREATE PROC uspAddCandidateKey
114@table VARCHAR(30),
115@column VARCHAR(30),
116@constraint_name VARCHAR(30)
117AS
118	DECLARE @statement NVARCHAR(4000)
119	SELECT @statement = 'ALTER TABLE ' + QUOTENAME(@table) + ' ADD CONSTRAINT ' + QUOTENAME(@constraint_name) + ' UNIQUE(' + QUOTENAME(@column) + ')'
120	PRINT @statement
121	EXEC sp_executesql @statement
122GO
123EXEC uspAddCandidateKey 'Products', 'price', 'CK_Products_Price'
124
125-- Remove a candidate key from a table
126DROP PROC IF EXISTS uspDropCandidateKey 
127GO
128
129CREATE PROC uspDropCandidateKey
130@table VARCHAR(30),
131@constraint_name VARCHAR(30)
132AS
133	DECLARE @statement NVARCHAR(4000)
134	SELECT @statement = 'ALTER TABLE ' + QUOTENAME(@table) + ' DROP CONSTRAINT ' + QUOTENAME(@constraint_name)
135	EXEC sp_executesql @statement
136GO
137EXEC uspDropCandidateKey 'Products', 'CK_Products_Price'
138
139-- Add a foreign key to a table
140DROP PROC IF EXISTS uspAddForeignKey 
141GO
142
143CREATE PROC uspAddForeignKey
144@fk_table VARCHAR(30),
145@pk_table VARCHAR(30),
146@constraint_name VARCHAR(30),
147@fk VARCHAR(30),
148@pk VARCHAR(30)
149AS 
150	DECLARE @statement NVARCHAR(4000)
151	SELECT @statement = 'ALTER TABLE ' + QUOTENAME(@fk_table) + ' ADD CONSTRAINT ' + QUOTENAME(@constraint_name) + ' FOREIGN KEY ' + '(' + QUOTENAME(@fk) + ')' +
152						' REFERENCES ' + QUOTENAME(@pk_table) + ' (' + QUOTENAME(@pk) + ')'
153	PRINT @statement
154	EXEC sp_executesql @statement	
155GO
156EXEC uspAddForeignKey 'Products', 'Categories', 'FK__Products_Category', 'category_id', 'id'
157
158-- Remove a foreign key from a table
159DROP PROC IF EXISTS uspDropForeignKey 
160GO
161
162CREATE PROC uspDropForeignKey
163@table VARCHAR(30),
164@constraint_name VARCHAR(30)
165AS
166	DECLARE @statement NVARCHAR(4000)
167	SELECT @statement = 'ALTER TABLE ' + QUOTENAME(@table) + ' DROP CONSTRAINT ' + QUOTENAME(@constraint_name)
168	EXEC sp_executesql @statement
169GO
170EXEC uspDropForeignKey 'Products', 'FK__Products_Category'
171
172-- Create a table
173DROP PROC IF EXISTS uspCreateRatingsTable 
174GO
175
176CREATE PROC uspCreateRatingsTable
177AS
178	CREATE TABLE Ratings(
179		id INT PRIMARY KEY IDENTITY(1, 1),
180		no_stars INT CHECK(no_stars BETWEEN 0 AND 5),
181		product_id INT FOREIGN KEY REFERENCES Products(id),
182		user_id INT FOREIGN KEY REFERENCES Users(id),
183	)
184GO
185EXEC uspCreateRatingsTable
186
187-- Remove a table
188DROP PROC IF EXISTS uspDropTable 
189GO 
190
191CREATE PROC uspDropTable
192@table VARCHAR(30)
193AS
194	DECLARE @statement NVARCHAR(4000)
195	SELECT @statement = 'DROP TABLE ' + QUOTENAME(@table)
196	EXEC sp_executesql @statement
197GO
198EXEC uspDropTable 'Ratings'
199
200
201IF OBJECT_ID('Versions', 'U') IS NOT NULL
202	DROP TABLE Versions
203
204CREATE TABLE Versions(
205	id INT PRIMARY KEY IDENTITY(1, 1),
206	version_no INT
207)