Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Auto generate crud sprocs. must grant permissions after running.
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'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.