Automatically generate SUID procs for any table
As a database developer, coding the SELECT, UPDATE, INSERT, and DELETE (SUID) stored procedures for each table in your database can be tedious, boring, and time-consuming. For that reason, I created the procedure usp_GenerateProcsForTable, which automatically generates the basic SUID procedures for any given tablename passed in. Your table must have a primary key in order for this to work properly.
This tool is a huge timesaver for developers and DBAs.
The syntax is [EXEC] usp_GenerateProcsForTable [@table_name=] 'MyTable'.
If you want to go a step further and generate SUID procs for your entire database in one stroke........you can execute this proc via the system-supplied proc sp_MSForEachTable; i.e.,
sp_MSForEachTable 'usp_GenerateProcsForTable ''?'''
ALTER PROCEDURE usp_GenerateProcsForTable
@table_name SYSNAME
/*
Created by Cade Bryant.
Generates the code for creating
SELECT, UPDATE, INSERT, and DELETE
procs for any given table
Example: EXEC usp_GenerateProcsForTable 'MyTable'
*/
AS
SET NOCOUNT ON
DECLARE @cols VARCHAR(8000),
@parms VARCHAR(8000),
@parms_with_types VARCHAR(8000),
@parms_for_where_clause VARCHAR(8000),
@parms_for_update VARCHAR(8000),
@pk_col SYSNAME
--Remove table-owner prefix if it exists (e.g., if proc was executed via sp_MSForEachTable)"
IF CHARINDEX('.', @table_name) > 0
SET @table_name = SUBSTRING(@table_name, CHARINDEX('.', @table_name) + 1, LEN(@table_name))
--remove brackets
SET @table_name = REPLACE(REPLACE(@table_name, '[', ''), ']', '')
--GET PRIMARY KEY FOR TABLE
SELECT @pk_col = ccu.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
ON tc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND tc.TABLE_NAME = @table_name
AND ccu.TABLE_NAME = @table_name
DECLARE @temp TABLE
(
sort INT,
code TEXT
)
SELECT @cols =
ISNULL(@cols + ',' + CHAR(13) + CHAR(10), '') +
CHAR(9) + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
SELECT @parms =
ISNULL(@parms + ',' + CHAR(13) + CHAR(10), '') +
CHAR(9) + '@' + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
SELECT @parms_with_types =
ISNULL(@parms_with_types + ',' + CHAR(13) + CHAR(10), '') +
CHAR(9) + '@' + COLUMN_NAME + ' ' +
UPPER(DATA_TYPE) + CASE
WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
AND DATA_TYPE NOT LIKE '%text%'
THEN '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')'
WHEN NUMERIC_PRECISION IS NOT NULL
AND ISNULL(NUMERIC_SCALE, 0) > 0
AND DATETIME_PRECISION IS NULL
THEN '(' + CAST(NUMERIC_PRECISION AS VARCHAR) + ', ' +
CAST(NUMERIC_SCALE AS VARCHAR) + ')'
ELSE ''
END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
SELECT @parms_for_where_clause =
ISNULL(@parms_for_where_clause + CHAR(13) + 'AND', '') +
CHAR(13) + CHAR(9) + '(' + COLUMN_NAME + '=' +
'@' + COLUMN_NAME + ' OR ' +
'@' + COLUMN_NAME + ' IS NULL) '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
SELECT @parms_for_update =
ISNULL(@parms_for_update + ',' + CHAR(13), '') +
CHAR(9) + COLUMN_NAME + '=' +
'ISNULL(@' + COLUMN_NAME + ',' + COLUMN_NAME + ')'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
--GENERATE SELECT PROC:
INSERT @temp
SELECT 1, 'CREATE PROCEDURE usp_' + @table_name + '_Sel'
UNION ALL
SELECT 2, @parms_with_types
UNION ALL
SELECT 3, ''
UNION ALL
SELECT 4, 'AS'
UNION ALL
SELECT 5, ''
UNION ALL
SELECT 6, 'SELECT'
UNION ALL
SELECT 7, @cols
UNION ALL
SELECT 8, CHAR(13) + 'FROM' + CHAR(13) + CHAR(9) + @table_name + CHAR(13)
UNION ALL
SELECT 9, 'WHERE' + CHAR(9) + @parms_for_where_clause
UNION ALL
SELECT 10, ''
UNION ALL
SELECT 11, 'GO' + CHAR(13) + CHAR(13)
--GENERATE INSERT PROC:
INSERT @temp
SELECT 12, 'CREATE PROCEDURE usp_' + @table_name + '_Ins'
UNION ALL
SELECT 13, @parms_with_types
UNION ALL
SELECT 14, ''
UNION ALL
SELECT 15, 'AS'
UNION ALL
SELECT 16, ''
UNION ALL
SELECT 17, 'INSERT INTO ' + @table_name
UNION ALL
SELECT 18, '(' + CHAR(13) + @cols + CHAR(13) + ')'
UNION ALL
SELECT 19, ''
UNION ALL
SELECT 20, 'SELECT'
UNION ALL
SELECT 21, @parms
UNION ALL
SELECT 22, 'GO' + CHAR(13) + CHAR(13)
--GENERATE UPDATE PROC:
INSERT @temp
SELECT 23, 'CREATE PROCEDURE usp_' + @table_name + '_Upd'
UNION ALL
SELECT 24, @parms_with_types
UNION ALL
SELECT 25, ''
UNION ALL
SELECT 26, 'AS'
UNION ALL
SELECT 27, ''
UNION ALL
SELECT 28, 'UPDATE ' + CHAR(13) + CHAR(9) + @table_name
UNION ALL
SELECT 29, 'SET' + CHAR(13) + @parms_for_update
UNION ALL
SELECT 30, 'WHERE' + CHAR(13) + CHAR(9) +
@pk_col + '=' + '@' + @pk_col
UNION ALL
SELECT 31, 'GO' + CHAR(13) + CHAR(13)
--GENERATE DELETE PROC:
INSERT @temp
SELECT 32, 'CREATE PROCEDURE usp_' + @table_name + '_Del'
UNION ALL
SELECT 33, CHAR(9) + '@' + @pk_col + ' ' +
UPPER(DATA_TYPE) + CASE
WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
AND DATA_TYPE NOT LIKE '%text%'
THEN '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')'
WHEN NUMERIC_PRECISION IS NOT NULL
AND ISNULL(NUMERIC_SCALE, 0) > 0
AND DATETIME_PRECISION IS NULL
THEN '(' + CAST(NUMERIC_PRECISION AS VARCHAR) + ', ' +
CAST(NUMERIC_SCALE AS VARCHAR) + ')'
ELSE ''
END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
AND COLUMN_NAME = @pk_col
UNION ALL
SELECT 34, ''
UNION ALL
SELECT 35, 'AS'
UNION ALL
SELECT 36, ''
UNION ALL
SELECT 37, 'DELETE FROM ' + CHAR(13) + CHAR(9) + @table_name
UNION ALL
SELECT 38, 'WHERE' + CHAR(13) + CHAR(9) +
@pk_col + '=' + '@' + @pk_col
UNION ALL
SELECT 39, 'GO' + CHAR(13) + CHAR(13)
SELECT code
FROM @temp
ORDER BY sort 
