· 7 years ago · Oct 13, 2018, 07:46 AM
1-- #########################################################
2-- Author: www.sqlbook.com
3-- Copyright: (c) www.sqlbook.com. You are free to use and redistribute
4-- this script as long as this comments section with the
5-- author and copyright details are not altered.
6-- Purpose: For a specified user defined table (or all user defined
7-- tables) in the database this script generates 4 Stored
8-- Procedure definitions with different Procedure name
9-- suffixes:
10-- 1) List all records in the table (suffix of _GetAll)
11-- 2) Get a specific record from the table (suffix of _GetById)
12-- 3) UPDATE or INSERT (UPSERT) - (suffix of _Save)
13-- 4) DELETE a specified row - (suffix of _Delete)
14-- e.g. For a table called location the script will create
15-- procedure definitions for the following procedures:
16-- schema.Location_GetAll
17-- schema.Location_GetById
18-- schema.Location_Save
19-- schema.Location_Delete
20-- Notes: The stored procedure definitions can either be printed
21-- to the screen or executed using EXEC sp_ExecuteSQL.
22-- The stored proc names are prefixed with udp_ to avoid
23-- conflicts with system stored procs.
24-- Assumptions: - This script assumes that the primary key is the first
25-- column in the table and that if the primary key is
26-- an integer then it is an IDENTITY (autonumber) field.
27-- - This script is not suitable for the link tables
28-- in the middle of a many to many relationship.
29-- - After the script has run you will need to add
30-- an ORDER BY clause into the '_GetAll' procedures
31-- according to your needs / required sort order.
32-- - Assumes you have set valid values for the
33-- config variables in the section immediately below
34-- #########################################################
35
36-- ##########################################################
37/* SET CONFIG VARIABLES THAT ARE USED IN SCRIPT */
38-- ##########################################################
39
40-- Do we want to generate the SP definitions for every user defined
41-- table in the database or just a single specified table?
42-- Assign a blank string - '' for all tables or the table name for
43-- a single table.
44DECLARE @GenerateProcsFor varchar(100)
45--SET @GenerateProcsFor = 'Orders'
46SET @GenerateProcsFor = ''
47
48-- which database do we want to create the procs for?
49-- Change both the USE and SET lines below to set the datbase name
50-- to the required database.
51USE databasename
52DECLARE @DatabaseName varchar(100)
53SET @DatabaseName = 'databasename'
54
55-- do we want the script to print out the CREATE PROC statements
56-- or do we want to execute them to actually create the procs?
57-- Assign a value of either 'Print' or 'Execute'
58DECLARE @PrintOrExecute varchar(10)
59--SET @PrintOrExecute = 'Execute'
60SET @PrintOrExecute = 'Print'
61
62
63-- Is there a table name prefix i.e. 'tbl_' which we don't want
64-- to include in our stored proc names?
65DECLARE @TablePrefix varchar(10)
66SET @TablePrefix = ''
67
68-- What schema do you want the stored procedures to be created under?
69DECLARE @SchemaName varchar(20)
70SET @SchemaName = 'dbo'
71
72-- For our '_lst' and '_sel' procedures do we want to
73-- do SELECT * or SELECT [ColumnName,]...
74-- Assign a value of either 1 or 0
75DECLARE @UseSelectWildCard bit
76SET @UseSelectWildCard = 0
77
78-- ##########################################################
79/* END SETTING OF CONFIG VARIABLE
80-- do not edit below this line */
81-- ##########################################################
82
83
84-- DECLARE CURSOR containing all columns from user defined tables
85-- in the database
86DECLARE TableCol Cursor FOR
87SELECT c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH
88FROM INFORMATION_SCHEMA.Columns c INNER JOIN
89 INFORMATION_SCHEMA.Tables t ON c.TABLE_NAME = t.TABLE_NAME
90WHERE t.Table_Catalog = @DatabaseName
91 AND t.TABLE_TYPE = 'BASE TABLE'
92 AND t.TABLE_SCHEMA = @SchemaName
93ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION
94
95-- Declare variables which will hold values from cursor rows
96DECLARE @TableSchema varchar(100), @TableName varchar(100)
97DECLARE @ColumnName varchar(100), @DataType varchar(30)
98DECLARE @CharLength int
99
100DECLARE @ColumnNameCleaned varchar(100)
101
102-- Declare variables which will track what table we are
103-- creating Stored Procs for
104DECLARE @CurrentTable varchar(100)
105DECLARE @FirstTable bit
106DECLARE @FirstColumnName varchar(100)
107DECLARE @FirstColumnDataType varchar(30)
108DECLARE @ObjectName varchar(100) -- this is the tablename with the
109 -- specified tableprefix lopped off.
110DECLARE @TablePrefixLength int
111
112-- init vars
113SET @CurrentTable = ''
114SET @FirstTable = 1
115SET @TablePrefixLength = Len(@TablePrefix)
116
117-- Declare variables which will hold the queries we are building use unicode
118-- data types so that can execute using sp_ExecuteSQL
119DECLARE @LIST nvarchar(4000), @UPSERT nvarchar(4000)
120DECLARE @SELECT nvarchar(4000), @INSERT nvarchar(4000), @INSERTVALUES nvarchar(4000)
121DECLARE @UPDATE nvarchar(4000), @DELETE nvarchar(4000), @EXISTS nvarchar(200)
122
123
124-- open the cursor
125OPEN TableCol
126
127-- get the first row of cursor into variables
128FETCH NEXT FROM TableCol INTO @TableSchema, @TableName, @ColumnName, @DataType, @CharLength
129
130-- loop through the rows of the cursor
131WHILE @@FETCH_STATUS = 0 BEGIN
132
133 SET @ColumnNameCleaned = Replace(@ColumnName, ' ', '')
134
135 -- is this a new table?
136 IF @TableName <> @CurrentTable BEGIN
137
138 -- if is the end of the last table
139 IF @CurrentTable <> '' BEGIN
140 IF @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable BEGIN
141
142 -- first add any syntax to end the statement
143
144 -- _lst
145 SET @LIST = @List + Char(13) + 'FROM [' + @TableSchema + '].[' + @CurrentTable + '] WITH(NOLOCK)' + Char(13)
146 SET @LIST = @LIST + Char(13) + Char(13) + 'SET NOCOUNT OFF' + Char(13) + Char(13)
147 SET @LIST = @LIST + Char(13)
148
149 -- _sel
150 SET @SELECT = @SELECT + Char(13) + 'FROM [' + @TableSchema + '].[' + @CurrentTable + '] WITH(NOLOCK)' + Char(13)
151 SET @SELECT = @SELECT + 'WHERE [' + @FirstColumnName + '] = @' + Replace(@FirstColumnName, ' ', '') + Char(13)
152 SET @SELECT = @SELECT + Char(13) + Char(13) + 'SET NOCOUNT OFF' + Char(13) + Char(13)
153 SET @SELECT = @SELECT + Char(13)
154
155
156 -- UPDATE (remove trailing comma and append the WHERE clause)
157 SET @UPDATE = SUBSTRING(@UPDATE, 0, LEN(@UPDATE)- 1) + Char(13) + Char(9) + 'WHERE [' + @FirstColumnName + '] = @' + Replace(@FirstColumnName, ' ', '') + Char(13)
158
159 -- INSERT
160 SET @INSERT = SUBSTRING(@INSERT, 0, LEN(@INSERT) - 1) + Char(13) + Char(9) + ')' + Char(13)
161 SET @INSERTVALUES = SUBSTRING(@INSERTVALUES, 0, LEN(@INSERTVALUES) -1) + Char(13) + Char(9) + ')'
162 SET @INSERT = @INSERT + @INSERTVALUES
163
164 SET @EXISTS = ''
165 SET @EXISTS = @EXISTS + 'EXISTS (SELECT * FROM [' + @TableSchema + '].[' + @CurrentTable + '] WITH(NOLOCK) '
166 SET @EXISTS = @EXISTS + 'WHERE [' + @FirstColumnName + '] = @' + Replace(@FirstColumnName, ' ', '') + ')'
167
168 -- _ups
169 SET @UPSERT = @UPSERT + Char(13) + 'AS' + Char(13)
170 SET @UPSERT = @UPSERT + 'SET NOCOUNT ON' + Char(13)
171 IF @FirstColumnDataType IN ('int', 'bigint', 'smallint', 'tinyint', 'float', 'decimal')
172 BEGIN
173 SET @UPSERT = @UPSERT + 'IF @' + Replace(@FirstColumnName, ' ', '') + ' = 0 BEGIN' + Char(13)
174 END ELSE BEGIN
175 SET @UPSERT = @UPSERT + 'IF NOT ' + @EXISTS + ' BEGIN' + Char(13)
176 END
177 SET @UPSERT = @UPSERT + ISNULL(@INSERT, '') + Char(13)
178 SET @UPSERT = @UPSERT + Char(9) + 'SELECT SCOPE_IDENTITY() As InsertedID' + Char(13)
179 SET @UPSERT = @UPSERT + 'END' + Char(13)
180 SET @UPSERT = @UPSERT + 'ELSE BEGIN' + Char(13)
181 SET @UPSERT = @UPSERT + ISNULL(@UPDATE, '') + Char(13)
182 SET @UPSERT = @UPSERT + 'END' + Char(13) + Char(13)
183 SET @UPSERT = @UPSERT + 'SET NOCOUNT OFF' + Char(13) + Char(13)
184 SET @UPSERT = @UPSERT + Char(13)
185
186 -- _del
187 -- delete proc completed already
188
189 -- --------------------------------------------------
190 -- now either print the SP definitions or
191 -- execute the statements to create the procs
192 -- --------------------------------------------------
193 IF @PrintOrExecute <> 'Execute' BEGIN
194 PRINT @LIST
195 PRINT @SELECT
196 PRINT @UPSERT
197 PRINT @DELETE
198 END ELSE BEGIN
199 EXEC sp_Executesql @LIST
200 EXEC sp_Executesql @SELECT
201 EXEC sp_Executesql @UPSERT
202 EXEC sp_Executesql @DELETE
203 END
204 END -- end @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable
205 END
206
207 -- update the value held in @CurrentTable
208 SET @CurrentTable = @TableName
209 SET @FirstColumnName = @ColumnName
210 SET @FirstColumnDataType = @DataType
211
212 IF @TablePrefixLength > 0 BEGIN
213 IF SUBSTRING(@CurrentTable, 1, @TablePrefixLength) = @TablePrefix BEGIN
214 --PRINT Char(13) + 'DEBUG: OBJ NAME: ' + RIGHT(@CurrentTable, LEN(@CurrentTable) - @TablePrefixLength)
215 SET @ObjectName = RIGHT(@CurrentTable, LEN(@CurrentTable) - @TablePrefixLength)
216 END ELSE BEGIN
217 SET @ObjectName = @CurrentTable
218 END
219 END ELSE BEGIN
220 SET @ObjectName = @CurrentTable
221 END
222
223 IF @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable BEGIN
224
225 -- ----------------------------------------------------
226 -- now start building the procedures for the next table
227 -- ----------------------------------------------------
228
229 -- _lst
230 SET @LIST = 'CREATE PROC [' + @SchemaName + '].[' + @ObjectName + '_GetAll]' + Char(13)
231 SET @LIST = @LIST + 'AS' + Char(13)
232 SET @LIST = @LIST + 'SET NOCOUNT ON' + Char(13)
233 IF @UseSelectWildcard = 1 BEGIN
234 SET @LIST = @LIST + Char(13) + 'SELECT * '
235 END
236 ELSE BEGIN
237 SET @LIST = @LIST + Char(13) + 'SELECT [' + @ColumnName + ']'
238 END
239
240 -- _sel
241 SET @SELECT = 'CREATE PROC [' + @SchemaName + '].[' + @ObjectName + '_GetById]' + Char(13)
242 SET @SELECT = @SELECT + Char(9) + '@' + @ColumnNameCleaned + ' ' + @DataType
243 IF @DataType IN ('varchar', 'nvarchar', 'char', 'nchar') BEGIN
244 SET @SELECT = @SELECT + '(' + CAST(@CharLength As varchar(10)) + ')'
245 END
246 SET @SELECT = @SELECT + Char(13) + 'AS' + Char(13)
247 SET @SELECT = @SELECT + 'SET NOCOUNT ON' + Char(13)
248 IF @UseSelectWildcard = 1 BEGIN
249 SET @SELECT = @SELECT + Char(13) + 'SELECT * '
250 END
251 ELSE BEGIN
252 SET @SELECT = @SELECT + Char(13) + 'SELECT [' + @ColumnName + ']'
253 END
254
255 -- _ups
256 SET @UPSERT = 'CREATE PROC [' + @SchemaName + '].[' + @ObjectName + '_Save]' + Char(13)
257 SET @UPSERT = @UPSERT + Char(13) + Char(9) + '@' + @ColumnNameCleaned + ' ' + @DataType
258 IF @DataType IN ('varchar', 'nvarchar', 'char', 'nchar') BEGIN
259 SET @UPSERT = @UPSERT + '(' + CAST(@CharLength As Varchar(10)) + ')'
260 END
261
262 -- UPDATE
263 SET @UPDATE = Char(9) + 'UPDATE ' + @TableName + ' SET ' + Char(13)
264
265 -- INSERT -- don't add first column to insert if it is an
266 -- integer (assume autonumber)
267 SET @INSERT = Char(9) + 'INSERT INTO [' + @TableSchema + '].[' + @CurrentTable + '] (' + Char(13)
268 SET @INSERTVALUES = Char(9) + 'VALUES (' + Char(13)
269
270 IF @FirstColumnDataType NOT IN ('int', 'bigint', 'smallint', 'tinyint')
271 BEGIN
272 SET @INSERT = @INSERT + Char(9) + Char(9) + '[' + @ColumnName + '],' + Char(13)
273 SET @INSERTVALUES = @INSERTVALUES + Char(9) + Char(9) + '@' + @ColumnNameCleaned + ',' + Char(13)
274 END
275
276 -- _del
277 SET @DELETE = 'CREATE PROC [' + @SchemaName + '].[' + @ObjectName + '_Delete]' + Char(13)
278 SET @DELETE = @DELETE + Char(9) + '@' + @ColumnNameCleaned + ' ' + @DataType
279 IF @DataType IN ('varchar', 'nvarchar', 'char', 'nchar') BEGIN
280 SET @DELETE = @DELETE + '(' + CAST(@CharLength As Varchar(10)) + ')'
281 END
282 SET @DELETE = @DELETE + Char(13) + 'AS' + Char(13)
283 SET @DELETE = @DELETE + 'SET NOCOUNT ON' + Char(13) + Char(13)
284 SET @DELETE = @DELETE + 'DELETE FROM [' + @TableSchema + '].[' + @CurrentTable + ']' + Char(13)
285 SET @DELETE = @DELETE + 'WHERE [' + @ColumnName + '] = @' + @ColumnNameCleaned + Char(13)
286 SET @DELETE = @DELETE + Char(13) + 'SET NOCOUNT OFF' + Char(13)
287 SET @DELETE = @DELETE + Char(13)
288
289 END -- end @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable
290 END
291 ELSE BEGIN
292 IF @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable BEGIN
293
294 -- is the same table as the last row of the cursor
295 -- just append the column
296
297 -- _lst
298 IF @UseSelectWildCard = 0 BEGIN
299 SET @LIST = @LIST + ', ' + Char(13) + Char(9) + '[' + @ColumnName + ']'
300 END
301
302 -- _sel
303 IF @UseSelectWildCard = 0 BEGIN
304 SET @SELECT = @SELECT + ', ' + Char(13) + Char(9) + '[' + @ColumnName + ']'
305 END
306
307 -- _ups
308 SET @UPSERT = @UPSERT + ',' + Char(13) + Char(9) + '@' + @ColumnNameCleaned + ' ' + @DataType
309 IF @DataType IN ('varchar', 'nvarchar', 'char', 'nchar') BEGIN
310 SET @UPSERT = @UPSERT + '(' + CAST(@CharLength As varchar(10)) + ')'
311 END
312
313 -- UPDATE
314 SET @UPDATE = @UPDATE + Char(9) + Char(9) + '[' + @ColumnName + '] = @' + @ColumnNameCleaned + ',' + Char(13)
315
316 -- INSERT
317 SET @INSERT = @INSERT + Char(9) + Char(9) + '[' + @ColumnName + '],' + Char(13)
318 SET @INSERTVALUES = @INSERTVALUES + Char(9) + Char(9) + '@' + @ColumnNameCleaned + ',' + Char(13)
319
320 -- _del
321 -- delete proc completed already
322 END -- end @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable'
323 END
324
325 -- fetch next row of cursor into variables
326 FETCH NEXT FROM TableCol INTO @TableSchema, @TableName, @ColumnName, @DataType, @CharLength
327END
328
329-- ----------------
330-- clean up cursor
331-- ----------------
332CLOSE TableCol
333DEALLOCATE TableCol
334
335-- ------------------------------------------------
336-- repeat the block of code from within the cursor
337-- So that the last table has its procs completed
338-- and printed / executed
339-- ------------------------------------------------
340
341-- if is the end of the last table
342IF @CurrentTable <> '' BEGIN
343 IF @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable BEGIN
344
345 -- first add any syntax to end the statement
346
347 -- _lst
348 SET @LIST = @List + Char(13) + 'FROM ' + @CurrentTable + Char(13)
349 SET @LIST = @LIST + Char(13) + Char(13) + 'SET NOCOUNT OFF' + Char(13)
350 SET @LIST = @LIST + Char(13)
351
352 -- _sel
353 SET @SELECT = @SELECT + Char(13) + 'FROM ' + @CurrentTable + Char(13)
354 SET @SELECT = @SELECT + 'WHERE [' + @FirstColumnName + '] = @' + Replace(@FirstColumnName, ' ', '') + Char(13)
355 SET @SELECT = @SELECT + Char(13) + Char(13) + 'SET NOCOUNT OFF' + Char(13)
356 SET @SELECT = @SELECT + Char(13)
357
358
359 -- UPDATE (remove trailing comma and append the WHERE clause)
360 SET @UPDATE = SUBSTRING(@UPDATE, 0, LEN(@UPDATE)- 1) + Char(13) + Char(9) + 'WHERE [' + @FirstColumnName + '] = @' + Replace(@FirstColumnName, ' ', '') + Char(13)
361
362 -- INSERT
363 SET @INSERT = SUBSTRING(@INSERT, 0, LEN(@INSERT) - 1) + Char(13) + Char(9) + ')' + Char(13)
364 SET @INSERTVALUES = SUBSTRING(@INSERTVALUES, 0, LEN(@INSERTVALUES) -1) + Char(13) + Char(9) + ')'
365 SET @INSERT = @INSERT + @INSERTVALUES
366
367 SET @EXISTS = ''
368 SET @EXISTS = @EXISTS + 'EXISTS (SELECT * FROM [' + @TableSchema + '].[' + @CurrentTable + '] WITH(NOLOCK) '
369 SET @EXISTS = @EXISTS + 'WHERE [' + @FirstColumnName + '] = @' + Replace(@FirstColumnName, ' ', '') + ')'
370
371 -- _ups
372 SET @UPSERT = @UPSERT + Char(13) + 'AS' + Char(13)
373 SET @UPSERT = @UPSERT + 'SET NOCOUNT ON' + Char(13)
374 IF @FirstColumnDataType IN ('int', 'bigint', 'smallint', 'tinyint', 'float', 'decimal')
375 BEGIN
376 SET @UPSERT = @UPSERT + 'IF @' + Replace(@FirstColumnName, ' ', '') + ' = 0 BEGIN' + Char(13)
377 END ELSE BEGIN
378 SET @UPSERT = @UPSERT + 'IF NOT ' + @EXISTS + ' BEGIN' + Char(13)
379 END
380 SET @UPSERT = @UPSERT + ISNULL(@INSERT, '') + Char(13)
381 SET @UPSERT = @UPSERT + Char(9) + 'SELECT SCOPE_IDENTITY() As InsertedID' + Char(13)
382 SET @UPSERT = @UPSERT + 'END' + Char(13)
383 SET @UPSERT = @UPSERT + 'ELSE BEGIN' + Char(13)
384 SET @UPSERT = @UPSERT + ISNULL(@UPDATE, '') + Char(13)
385 SET @UPSERT = @UPSERT + 'END' + Char(13) + Char(13)
386 SET @UPSERT = @UPSERT + 'SET NOCOUNT OFF' + Char(13)
387 SET @UPSERT = @UPSERT + Char(13)
388
389 -- _del
390 -- delete proc completed already
391
392 -- --------------------------------------------------
393 -- now either print the SP definitions or
394 -- execute the statements to create the procs
395 -- --------------------------------------------------
396 IF @PrintOrExecute <> 'Execute' BEGIN
397 PRINT @LIST
398 PRINT @SELECT
399 PRINT @UPSERT
400 PRINT @DELETE
401 END ELSE BEGIN
402 EXEC sp_Executesql @LIST
403 EXEC sp_Executesql @SELECT
404 EXEC sp_Executesql @UPSERT
405 EXEC sp_Executesql @DELETE
406 END
407 END -- end @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable
408END