Writing re runnable sql server scripts Part 66 — Transcript

Full transcript

1. 0:00this is part 66 of SQL Server tutorial
2. 0:02in this video we'll discuss writing
3. 0:04runnable SQL scripts please watch part
4. 0:0765 before proceeding so what is a
5. 0:10runnable SQL script a runnable SQL
6. 0:13script is a script that when run more
7. 0:15than once will not throw errors let's
8. 0:18understand what we mean by this
9. 0:19statement with an example let's flip to
10. 0:21SQL Server management Studio notice that
11. 0:24here we have this create table script
12. 0:26this is going to create this table TBL
13. 0:28employee I want this table to be created
14. 0:31in the sample database so that's why we
15. 0:33are using use sample so this table is
16. 0:36going to be created within the sample
17. 0:38database so when I run this query at the
18. 0:40moment notice that I don't have the
19. 0:41stable TBL employee so when I run this
20. 0:44the first time this is going to execute
21. 0:46successfully and the table gets created
22. 0:49so when we refresh this notice that we
23. 0:51have the table within that sample
24. 0:53database then if I try to run this query
25. 0:56once again look at what's going to
26. 0:57happen we get an error why because we
27. 1:00have the table already that's why it
28. 1:02says there is already an object named
29. 1:04TBL
30. 1:05employee okay so this script right here
31. 1:08is not runnable so how do we make the
32. 1:11script runnable pretty straightforward
33. 1:13check for the existence of the table if
34. 1:16the table doesn't exist only then try to
35. 1:19create the table else print a message
36. 1:21stating the table already exists so
37. 1:24obviously the next question is how do we
38. 1:27check for the existence of a table in
39. 1:30the previous session we discussed using
40. 1:32information on score schema. tables view
41. 1:35to list all the tables in a SQL Server
42. 1:37database so we're going to make that
43. 1:39make use of that view to check for the
44. 1:41existence of the table so look at this
45. 1:43query select star from information
46. 1:45andore schema. tabls where table name is
47. 1:48equal to the the name of the table that
48. 1:50we are trying to create so if that table
49. 1:53exists this query is going to return a
50. 1:55row and then we are passing the result
51. 1:58of this query to this function exist so
52. 2:01if at all if there is anything returned
53. 2:03by this query then this exist function
54. 2:05is going to return true otherwise it's
55. 2:07going to return false so basically look
56. 2:10at this expression right here if not
57. 2:12exists so if the table does not exist
58. 2:16then go ahead and create the table print
59. 2:17a message stating the table is
60. 2:19successfully created else print a
61. 2:21message stating the table already exist
62. 2:24pretty
63. 2:25straightforward let's look at this query
64. 2:27and action and just speed things up I
65. 2:30have this query already typed so let me
66. 2:32copy and paste that in SQL Server
67. 2:34management
68. 2:36studio so since we already have the
69. 2:38table TBL employee when we execute this
70. 2:40query um look at this first of all this
71. 2:43query when we execute this query we get
72. 2:46a row back and we are passing that to
73. 2:48this exist function so obviously you
74. 2:50know this is going to check this
75. 2:52expression right here is going to check
76. 2:53if the table is there or not and if the
77. 2:56table is not there then create the the
78. 3:00table else print this message
79. 3:02straightforward so let's execute this
80. 3:05look at that table TBL employee already
81. 3:07exists on the other hand if I delete
82. 3:09this
83. 3:10table and execute this query notice that
84. 3:14table TBL employee successfully created
85. 3:17refresh this TBL employee there I
86. 3:20execute this query once again table TBL
87. 3:22employee already exists no matter how
88. 3:24many times we execute this we don't get
89. 3:27an error
90. 3:30so this script is
91. 3:34runable now to check for the existence
92. 3:36of the table we have used information
93. 3:39schema. tabls there is another way to
94. 3:41check for the existence of the table you
95. 3:43can simply use this function this SQL
96. 3:46Server buil-in function object ID and
97. 3:49pass the name of the object here the
98. 3:51name of the object is our table name so
99. 3:53if this object already exists in SQL
100. 3:55Server database then it will have an ID
101. 3:59and this function is simply going to
102. 4:00return that ID so if there is an ID for
103. 4:03that object then we know the table is
104. 4:06already there if it is null then we know
105. 4:10you know we don't have a table there so
106. 4:11we'll go ahead and create that so just
107. 4:14to show you that we already have the
108. 4:17table right here this table TBL employee
109. 4:20so when we say select object ID and then
110. 4:23pass the name of the object which is TPL
111. 4:27employee and then look at this when I I
112. 4:29execute this query I get the ID of that
113. 4:33object okay so if that is null if object
114. 4:37ID for a given object is null then we
115. 4:39know for sure that object doesn't exist
116. 4:41we can go ahead and create that object
117. 4:43in this case table and then print a
118. 4:45message saying um table created
119. 4:48successfully uh else will print a
120. 4:50message saying the table already exists
121. 4:52and just to show you that in action let
122. 4:55me copy this
123. 4:56query and paste that within SQL ser
124. 4:59management Studio the table is already
125. 5:01there so when we execute this table TBL
126. 5:04employee already
127. 5:07exists all right now depending on what
128. 5:10we want to achieve sometimes you know um
129. 5:13we may want to drop and recreate the
130. 5:15table so if the table already exists we
131. 5:18want to drop and recreate it okay this
132. 5:21is especially true if we are building
133. 5:23some test
134. 5:24databases okay so how do we drop and
135. 5:27recreate the tables you know again check
136. 5:29for the existence of the table and then
137. 5:32only drop the table um because if you
138. 5:35don't check for the existence of the
139. 5:36table and then try to drop a table and
140. 5:38if the table doesn't exist you will get
141. 5:41again an error so that's why when
142. 5:43dropping a table check if the table
143. 5:45already exist and again to check for the
144. 5:47existence of the table you can either
145. 5:49use information schema. tables view or
146. 5:53this object ID function okay notice that
147. 5:57if object ID then drop the table so if
148. 6:00there is if that is not null then we
149. 6:02know that object is there in which case
150. 6:04go ahead and drop it and then recreate
151. 6:06the table okay let's look at that query
152. 6:09in
153. 6:12action so let's copy this to SQL Server
154. 6:15management studio so the table is
155. 6:17already there so when I execute this
156. 6:19it's going to drop the table and then
157. 6:21recreate that okay so no matter how many
158. 6:24times we execute this it's going to drop
159. 6:27the table recreate it drop the table
160. 6:28recreate it
161. 6:30okay now you might be wondering let me
162. 6:32actually delete
163. 6:35this if you don't have this condition
164. 6:37right here the check to make sure that
165. 6:39the object is there before we try to
166. 6:41delete if we don't have that condition
167. 6:44and notice that we don't have the table
168. 6:45tval employee now if I try to execute
169. 6:48this we will get an error why because
170. 6:51the table doesn't exist anymore so the
171. 6:53query errors out right there and then
172. 6:56this doesn't get executed that's why
173. 6:58make sure you check for the existence of
174. 7:01the table before you you know try to
175. 7:04drop
176. 7:06it let's look at another example now
177. 7:10this script right here is not runnable
178. 7:13look at what the script is trying to do
179. 7:15it's trying to add a column email
180. 7:17address to this stable TBL employee when
181. 7:19we run the script the first time it's
182. 7:21going to run without any problem if that
183. 7:23column doesn't exist if I run it again
184. 7:26then it's going to throw an error
185. 7:28because a table cannot have um two
186. 7:30columns with the same name so this
187. 7:32script again is not runnable so how do
188. 7:35we make the script runnable obviously
189. 7:37check for the existence of that column
190. 7:39so how do you check for the existence of
191. 7:41the column again you can use this
192. 7:43information schema. columns we discussed
193. 7:46about this in previous session as well
194. 7:48and notice that we are checking the name
195. 7:50of the column not only that actually
196. 7:53let's look at that query and action
197. 8:04so when I execute this select star from
198. 8:07information schema. columns where column
199. 8:10name is equal to email address so when
200. 8:13we execute this we we we we didn't get
201. 8:15anything why because we don't have that
202. 8:17column there so let me actually execute
203. 8:20this query so it's going to add that
204. 8:23colum to our table TPL
205. 8:27employee so if we expand that
206. 8:30we should have email address column
207. 8:32right there so now when we execute
208. 8:37this we should get a row look at that
209. 8:39the column name is email address and we
210. 8:42are also having here table name filter
211. 8:47and that's very important because if
212. 8:49there is another table with the same
213. 8:51column name you know you will get that
214. 8:53true as well that's why we need to
215. 8:55filter you know on table name so if that
216. 9:00column email address doesn't exist on
217. 9:02the stable TBL employee only then try to
218. 9:06add that column okay and notice that
219. 9:08here I have another condition table
220. 9:10schema again this is very important
221. 9:13especially if your SQL server has
222. 9:15multiple schemas okay in multiple
223. 9:17schemas you can have same table name and
224. 9:20same column name so obviously you don't
225. 9:23want to be altering a table that is
226. 9:25present in a different schema okay so
227. 9:28that's why it's very important important
228. 9:29to check the schema as well just in case
229. 9:31if your SQL server has got multiple
230. 9:34schemas if that's not the case it's
231. 9:36enough if you check just the table name
232. 9:38and the column name again this script is
233. 9:41runnable no matter how many times you
234. 9:43run you know this is not going to
235. 9:45through an error if the column exists
236. 9:47it's going to print that message if it
237. 9:49doesn't exist it's going to add that
238. 9:52column again to check for the column
239. 9:55existence you can either use this view
240. 9:57or you can use this column length
241. 9:59function okay again just
242. 10:02like you know object ID function so
243. 10:06column length you pass the name of the
244. 10:08table and the column that you're looking
245. 10:10for if that column length function is
246. 10:13not null then we know that the column
247. 10:17already exists on that table else the
248. 10:20column doesn't exist in which case you
249. 10:22can have that you know the script to add
250. 10:24the column to the
251. 10:26table all right on this slide you can
252. 10:29find resources for asp.net cop and SQL
253. 10:31Ser interview questions that's it for
254. 10:33today thank you for listening have a
255. 10:35great day