Auto generate crud sprocs. must grant permissions after running.

auto_crud_sproc_generator.sql

  SET NOCOUNT ON;
-- User Preferences

DECLARE @prefix varchar(5);
SET @prefix = 'usp_';

-- End User Preferences

-- Create CRUD procedures
DECLARE DBtables CURSOR FOR SELECT name FROM sysobjects WHERE xtype = 'u' AND name != 'sysdiagrams'
DECLARE @tblName varchar(max);
OPEN DBtables
FETCH NEXT FROM DBtables INTO @tblName 
WHILE @@FETCH_STATUS = 0 
BEGIN
  BEGIN
		-- Holds column attributes for each column of the table
		DECLARE @tblColumns TABLE
		( 
			Id BIGINT IDENTITY(1,1),
			Name VARCHAR(max),
			DataType VARCHAR(max),
			MaxLength BIGINT,
			IsNullable BIT,
			IsAnsiPadded BIT
		)
		-- Holds the primary key attributes
		DECLARE @PKey TABLE
		(
			TABLE_QUALIFIER sysname,
			TABLE_OWNER sysname,
			TABLE_NAME sysname,
			COLUMN_NAME sysname,
			KEY_SEQ smallint,
			PK_NAME sysname
		)			
		-- If the table variables currently have rows, delete them
		IF ((SELECT COUNT(*) FROM @tblColumns) > 0)
		BEGIN
			DELETE FROM @tblColumns;
		END
		IF ((SELECT COUNT(*) FROM @PKey) > 0)
		BEGIN
			DELETE FROM @PKey;
		END
		
		-- Insert the table attributes into table variable
		INSERT INTO @tblColumns (Name, DataType, MaxLength, IsNullable, IsAnsiPadded)
		SELECT c.name , t.name, c.max_length, c.is_nullable, c.is_ansi_padded
		FROM sys.columns AS c 
			 INNER JOIN sys.types AS t ON t.system_type_id = c.system_type_id
		WHERE (c.object_id = object_id(@tblName)) 
			  AND t.system_type_id = t.user_type_id
		ORDER BY c.column_id
		
		-- Insert the primary key attributes into table variables
		INSERT INTO @PKey
		exec sp_pkeys @tblName;
				
		DECLARE @sql NVARCHAR(MAX);
		
		DECLARE @max BIGINT;
		DECLARE @min BIGINT;
		DECLARE @pkeyName varchar(100);				
		DECLARE @ColumnName varchar(max);						
		DECLARE @AnsiPadded BIT;
		SET @AnsiPadded = 0;
		-- Check whether the current table has a primary key
		IF((SELECT COUNT(*) FROM @PKey) = 0)
		BEGIN
			SET @pkeyName = '';
		END
		ELSE
		BEGIN
			DECLARE @schema nvarchar(128);
			
			SELECT @schema = CAST(TABLE_SCHEMA AS NVARCHAR) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_Name = ''+ @tblName + ''
			-- Get the primary key column
			SELECT @pkeyName = 	CAST(COLUMN_NAME AS NVARCHAR) FROM @PKey WHERE TABLE_Name = CAST(@tblName as sysname)	
		END						
		DECLARE @value varchar(100);		
		SELECT @value = CAST(DataType AS NVARCHAR) FROM @tblColumns WHERE Name = @pkeyName		
		-- Get the min Id for table variable
		SELECT @min = MIN(Id) FROM @tblColumns;
		-- Get the max Id for table variable
		SELECT @max = MAX(Id) FROM @tblColumns;		
		IF(LEN(@pkeyName) > 0)
		BEGIN
			PRINT 'Creating Procedure: [' + @schema + '].['+ @prefix + @tblName+'Select]'
			-- Check the the select procedure currently exists, if so delete
			SET @sql = 'IF OBJECT_ID(''[' + @schema + '].[' + @prefix + +@tblName+'Select]'') IS NOT NULL' + CHAR(10)
					 +	'BEGIN' + CHAR(10) + CHAR(9)
					 +		'DROP PROC [' + @schema + '].[' + @prefix + @tblName+'Select]' + CHAR(10)
					 +	'END' + CHAR(10)
					
			exec sp_executesql @sql;
			-- Create select procedure
			SET @sql = 'CREATE PROC [' + @schema + '].[' + @prefix +@tblName+'Select]' + CHAR(10) + CHAR(9)
					+ '@'+@pkeyName + ' ' + @value + CHAR(10) + 'AS ' + CHAR(10) + CHAR(9) + 'SET NOCOUNT ON;' + CHAR(10) + CHAR(9) + 'SET XACT_ABORT ON;'
					+ CHAR(10) + CHAR(10) + CHAR(9) + 'BEGIN TRAN' + CHAR(10) + CHAR(10) + CHAR(9) + 'SELECT '
			 --Loop through table columns
			WHILE(@min <= @max)
			BEGIN
				-- Get column name in table
				SELECT @ColumnName = Name FROM @tblColumns WHERE Id = @min;
				SET @sql = @sql + '[' + @ColumnName +']';
				SET @min = @min + 1;
				IF(@min <= @max)
				BEGIN
					-- Column names must be separated by a comma
					SET @sql = @sql + ', ';
				END
			END
			-- Set the table name and where conditions
			SET @sql = @sql + CHAR(10) + CHAR(9) + '  FROM [' + @schema + '].[' + @tblName + ']' + CHAR(10) + CHAR(9)
			SET @sql = @sql + ' WHERE  (['+ @pkeyName + '] = @' + @pkeyName + ' OR @' + @pkeyName + ' IS NULL)'
					 + CHAR(10) + CHAR(10) + CHAR(9) + 'COMMIT;' + CHAR(10) + CHAR(10) --+ 'GO'
			
			exec sp_executesql @sql;
			-- Create Insert procedure
			PRINT 'Creating Procedure: [' + @schema + '].[' + @prefix +@tblName+'Insert]'
			-- Check the the insert prcedure currently exists, if so delete
			SET @sql = 'IF OBJECT_ID(''[' + @schema + '].[' + @prefix +@tblName+'Insert]'') IS NOT NULL' + CHAR(10)
					 +	'BEGIN' + CHAR(10) + CHAR(9)
					 +		'DROP PROC [' + @schema + '].[' + @prefix + @tblName+'Insert]' + CHAR(10)
					 +	'END' + CHAR(10)					 
			
			exec sp_executesql @sql;
					
			SET @sql = 'CREATE PROC [' + @schema + '].[' + @prefix +@tblName+'Insert]' + CHAR(10) + CHAR(9)
			-- Get the min Id for table variable
			SELECT @min = MIN(Id) FROM @tblColumns;
			-- Get the max Id for table variable
			SELECT @max = MAX(Id) FROM @tblColumns;
			-- Loop through table columns
			WHILE(@min <= @max)
			BEGIN						
				-- Get column name in table
				SELECT @ColumnName = Name FROM @tblColumns WHERE Id = @min;
				-- The identity column does not belong in the procedure parameters
				IF(@ColumnName != @pkeyName)
				BEGIN
					-- Gets the value data type				
					SELECT @value = CAST(DataType AS NVARCHAR) FROM @tblColumns WHERE Name = @ColumnName;			
					SET @sql = @sql + '@' + @ColumnName + ' ' + @value;
					-- Determines whether the datatype is ansi padded
					SELECT @AnsiPadded = IsAnsiPadded FROM @tblColumns WHERE Id = @min;
					IF(@AnsiPadded = 1)
					BEGIN
						-- Gets the ansi padded length						
						SELECT @value = CASE WHEN LEFT(@value, 1) = 'n' THEN (MaxLength / 2) ELSE MaxLength END FROM @tblColumns WHERE Id = @min;						
						SET @sql = @sql + CASE WHEN @value > 0 THEN '(' + @value + ')' ELSE '(MAX)' END;

					END
					IF(@min < @max)
					BEGIN
						-- Parameters must be separated by a comma
						SET @sql = @sql + ',' + CHAR(10) + CHAR(9)
					END
				END		
				SET @min = @min + 1;
			END
			SET @sql = @sql + CHAR(10) + 'AS ' + CHAR(10) + CHAR(9) + 'SET NOCOUNT ON;' + CHAR(10) + CHAR(9) + 'SET XACT_ABORT ON;'
					+ CHAR(10) + CHAR(10) + CHAR(9) + 'BEGIN TRAN' + CHAR(10) + CHAR(10) + CHAR(9)
				    + 'INSERT INTO [' + @schema + '].['+@tblName+'] (' 
		    SELECT @min = MIN(Id) FROM @tblColumns;
			-- Get the max Id for table variable
			SELECT @max = MAX(Id) FROM @tblColumns;
			-- Loop through table columns
			WHILE(@min <= @max)
			BEGIN						
				-- Get column name in table
				SELECT @ColumnName = Name FROM @tblColumns WHERE Id = @min;
				IF(@ColumnName != @pkeyName)
				BEGIN	
					-- Get the column data type			
					SELECT @value = CAST(DataType AS NVARCHAR) FROM @tblColumns WHERE Name = @ColumnName;
					-- timestamps cannot be updated or inserted
					IF(@value <> 'timestamp')
					BEGIN
						-- Set the insert column list
						SET @sql = @sql + '[' + @ColumnName + ']';
						IF(@min < @max)
						BEGIN
							SET @sql = @sql + ', '
						END
					END
				END		
				SET @min = @min + 1;
			END
			SET @sql = @sql + ')' + CHAR(10) + CHAR(9) + 'VALUES ('
			SELECT @min = MIN(Id) FROM @tblColumns;
			-- Get the max Id for table variable
			SELECT @max = MAX(Id) FROM @tblColumns;
			-- Loop through table columns
			WHILE(@min <= @max)
			BEGIN						
				-- Get column name in table
				SELECT @ColumnName = Name FROM @tblColumns WHERE Id = @min;
				-- Set the insert values
				IF(@ColumnName != @pkeyName)
				BEGIN				
					SELECT @value = CAST(DataType AS NVARCHAR) FROM @tblColumns WHERE Name = @ColumnName;
					IF(@value <> 'timestamp')
						BEGIN
						SET @sql = @sql + '@' + @ColumnName 					
						IF(@min < @max)
						BEGIN
							SET @sql = @sql + ', ' 
						END
					END
				END		
				SET @min = @min + 1;
			END
			-- Return the current identity
			SET @sql = @sql + ')' + CHAR(10) + CHAR(10) + + CHAR(9) + 'SELECT [' + @pkeyName +'] ' + CHAR(10) + CHAR(9) 
				     + 'FROM [' + @schema + '].['+ @tblName + ']' + CHAR(10) + CHAR(9) + 'WHERE [' + @pkeyName + '] = SCOPE_IDENTITY()'
					 + CHAR(10) + CHAR(10) + CHAR(9) + 'COMMIT;' + CHAR(10) --+ 'GO'			
			exec sp_executesql @sql;
			
			
			-- Create Update procedure
			PRINT 'Creating Procedure: [' + @schema + '].[' + @prefix +@tblName+'Update]'
			-- Drop the procedure if it currently exists
			SET @sql = 'IF OBJECT_ID(''[' + @schema + '].[' + @prefix +@tblName+'Update]'') IS NOT NULL' + CHAR(10)
					 + 'BEGIN' + CHAR(10) + CHAR(9)
					 +	'DROP PROC [' + @schema + '].[' + @prefix + @tblName+'Update]' + CHAR(10)
					 + 'END' + CHAR(10)
					 --+ 'GO'
			exec sp_executesql @sql;
			-- Create update procedure
			SET @sql = 'CREATE PROC [' + @schema + '].[' + @prefix +@tblName+'Update]' + CHAR(10) + CHAR(9)		
		    SELECT @min = MIN(Id) FROM @tblColumns;
			-- Get the max Id for table variable
			SELECT @max = MAX(Id) FROM @tblColumns;
			-- Set the parameter list
			WHILE(@min <= @max)
			BEGIN
				-- Get column name in table
				SELECT @ColumnName = Name FROM @tblColumns WHERE Id = @min;						
				SELECT @value = CAST(DataType AS NVARCHAR) FROM @tblColumns WHERE Name = @ColumnName;
				-- Cannot modify timestamp fields so we skip them
				IF(@value <> 'timestamp')
				BEGIN
					-- Set parameter list and datatypes
					SET @sql = @sql + '@' + @ColumnName + ' ' + @value;
					SELECT @AnsiPadded = IsAnsiPadded FROM @tblColumns WHERE Id = @min;
					IF(@AnsiPadded = 1)
					BEGIN
						SELECT @value = CASE WHEN LEFT(@value, 1) = 'n' THEN (MaxLength / 2) ELSE MaxLength END FROM @tblColumns WHERE Id = @min;
						SET @sql = @sql + CASE WHEN @value > 0 THEN '(' + @value + ')' ELSE '(MAX)' END;
					END
					IF(@min < @max)
					BEGIN
						SET @sql = @sql + ',' + CHAR(10) + CHAR(9)
					END
				END
				SET @min = @min + 1;
			END
			SET @sql = @sql + CHAR(10) + 'AS ' + CHAR(10) + CHAR(9) + 'SET NOCOUNT ON;' + CHAR(10) + CHAR(9) + 'SET XACT_ABORT ON;'
					+ CHAR(10) + CHAR(10) + CHAR(9) + 'BEGIN TRAN' + CHAR(10) + CHAR(10) + CHAR(9)
				    + 'UPDATE [' + @schema + '].['+@tblName+']' + CHAR(10) + CHAR(9) + '   SET' 
			SELECT @min = MIN(Id) FROM @tblColumns;
			-- Get the max Id for table variable
			SELECT @max = MAX(Id) FROM @tblColumns;
			-- Set update conditions
			WHILE(@min <= @max)
			BEGIN						
				-- Get column name in table
				SELECT @ColumnName = Name FROM @tblColumns WHERE Id = @min;
				IF(@ColumnName != @pkeyName)
				BEGIN				
					SELECT @value = CAST(DataType AS NVARCHAR) FROM @tblColumns WHERE Name = @ColumnName;
					IF(@value <> 'timestamp')
					BEGIN
						SET @sql = @sql + ' ['+@ColumnName+'] = @' + @ColumnName 					
						IF(@min < @max)
						BEGIN
							SET @sql = @sql + ',' + CHAR(10) + CHAR(9) + CHAR(9) + '  '
						END
					END
				END
				SET @min = @min + 1;
			END
			-- Set where conditions
			SET @sql = @sql + CHAR(10) + CHAR(9) + ' WHERE [' + @pkeyName + '] = @' + @pkeyName
				      + CHAR(10) + +CHAR(10) + CHAR(9) + ' SELECT @' + @pkeyName + ' AS [' + @pkeyName + ']'
			          + CHAR(10) + CHAR(10) + CHAR(9) + 'COMMIT;' + CHAR(10)
			exec sp_executesql @sql;
			
			PRINT 'Creating Procedure: [' + @schema + '].[' + @prefix  + @tblName + 'Delete]'
			-- Drop Delete procedure if it currently exists
			SET @sql = 'IF OBJECT_ID(''[' + @schema + '].[' + @prefix +@tblName+'Delete]'') IS NOT NULL' + CHAR(10)
					 + 'BEGIN' + CHAR(10) + CHAR(9)
					 + 'DROP PROC [' + @schema + '].[' + @prefix + @tblName+'Delete]' + CHAR(10)
					 + 'END' + CHAR(10)
		exec sp_executesql @sql;
		-- Get delete parameter		
		SELECT @value = CAST(DataType AS NVARCHAR) FROM @tblColumns WHERE Name = @pkeyName		
		-- Set procedure syntax
		SET @sql = 'CREATE PROC [' + @schema + '].[' + @prefix +@tblName+'Delete]' + CHAR(10) + CHAR(9)
				 + '@'+@pkeyName + ' ' + @value + CHAR(10) + 'AS ' + CHAR(10) + CHAR(9) + 'SET NOCOUNT ON;' + CHAR(10) + CHAR(9) + 'SET XACT_ABORT ON;'
				 + CHAR(10) + CHAR(10) + CHAR(9) + 'BEGIN TRAN' + CHAR(10) + CHAR(10) + CHAR(9) + 'DELETE FROM [' + @schema + '].[' 
				 + @tblName + ']' + CHAR(10) + CHAR(9) + 'WHERE [' + @pkeyName + '] = @' + @pkeyName + CHAR(10) + CHAR(10) + CHAR(9) + 'COMMIT;' 
				 + CHAR(10) + CHAR(10) 
		exec sp_executesql @sql;
		END
		ELSE
		BEGIN
			PRINT CHAR(10) + 'Table ' + @tblName + ' does not have a primary key column and therefore CRUD statements could not be generated' + CHAR(10) 
		END		
	END
-- Get the next table to alter
FETCH NEXT FROM DBtables INTO @tblName 
END
-- Close cursor
CLOSE DBtables
DEALLOCATE DBtables
PRINT 'FINISHED'