如何在SSMS中为表自动生成CRUD存储过程？
如何在SQL Server Management Studio中从表格生成CRUD存储过程
20
- The Internet
1
我不相信它有这个功能。 - Joe
8个回答
25
SSMS没有生成CRUD存储过程的功能。您可以通过右键单击、Script Table As来生成INSERT、UPDATE语句等，但我认为您使用Mladen Prajdic的SSMS Tools Pack会更好。
- Aaron Bertrand
3
2@DavidJohnson 很高兴能帮到你。如果你认为CRUD很有用，那么等待SSMS崩溃并且无法恢复你的文件时，Mladen会提供非常灵活和强大的自动保存选项来保护你。 - Aaron Bertrand
是的，那让我有点想哭。没有万能药。 - The Internet
刚刚偶然看到这篇文章，想说谢谢：）。这个插件/工具真是太棒了。点赞！ - Tay
22
- Steve Wellens
3
这看起来也非常有帮助。不过早起的鸟儿有虫吃。 - The Internet
8是的，但是夹在第二个老鼠才会得到奶酪。 - Steve Wellens
非常好的回答，这是链接：http://stackoverflow.com/questions/3728641/generating-simple-crud-stored-procs - jimjim
17
我有一段简单的TSQL脚本，用来进行数据挖掘。这个脚本很基础，但可以轻松修改以适应您的需要。它会根据您指定的表格和视图生成一个Upsert（更新或插入）、Select和Delete存储过程所需的TSQL语句。
/*
This is a simple, single-table CRUD Generator. It does not have a bunch of
bells and whistles, and is easy to follow and modify. I wrote this to make
my job easier, and I am sharing it with you to do with it as you wish.
The Basics:
The TSQL below will create 3 procedures:
1. An Upsert Procedure: Suffix _ups
2. A Select Procedure: Suffix _sel
3. A Delete Procedure: Suffix _del
A Little More Detail:
Things you should know:
All 3 procedures have a parameter called @MyID which is used to set
the Context, so that my audit procedures get the validated user. If you
Have no use for it, you'll need to remove the piece of generator code
that adds it as a parameter to each of the 3 procedures. You will also
need to remove the PRINT statement for each procedure that looks like:
PRINT N' SET CONTEXT_INFO @MyID;' + CHAR(13) + CHAR(10)
This generator expects to perform inserts, updates, and deletes on a
table, and selects from a view. If you want to perform selects directly
from the table, simply use the table name in both @TableName and
@ViewName.
The Upsert Procedure:
If ID (Primary Key) is supplied it will perform an Update. Otherwise it
will perform an Insert. This generator is hard-coded to avoid inserting
or updating these particular fields:
Created
CreatedBy
Modified
ModifiedBy
RowVersion
<The Primary Key Field>
That's because in my databases I use those field names for audit, and they
are never modified except internally within the database. You can modify
the part of this procedure that performs this function to suit your needs.
This generator always uses the Parameter name @ID to represent the Primary
key defined for the table. This is my preference but you can modify to suit.
The Select Procedure:
If ID (Primary Key) is supplied it will select a single row from the View
(Table) whose name you provide. Otherwise it will select all rows. If the
@ISACTIVE_SEL variable is set to 1 (True), then the Select Procedure expects
your View (Table) to have a bit-type field named 'IsActive'. My tables are
standardized to this. If @ISACTIVE_SEL = 1 the Select Procedure will have an
additional parameter called @IsActive (bit). When @ID is not supplied, and
@IsActive is not supplied, the procedure selects all rows. When @ID is not
supplied, and @IsActive is supplied, the procedure selects all rows where
the field IsActive matches the parameter @IsActive
The Delete Procedure:
The Delete Pocedure requires that the Key value and the RowVersion value
be supplied. I use an Int type RowVersion, so if you use TimeStamp (varbinary(128))
then you will need to tweak the generator.
--Casey W Little
--Kaciree Software Solutions, LLC
Version 1.00
*/
--Type Your Database Name in this Use statement:
USE [<Your Database>]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*MODIFY THE VALUES BELOW TO SUIT YOUR NEEDS*/
DECLARE @DBName nvarchar(100)=N'<Your Database>';
DECLARE @ProcName nvarchar(100)=N'<Your Proc Name>';
DECLARE @DBRoleName nvarchar(100)=N'<Role that should have exec Rights>';
DECLARE @TableName nvarchar(100)=N'<Your Table Name>';
DECLARE @ViewName nvarchar(100)=N'<Your View Name>';
DECLARE @OrderBy nvarchar(100)=N'<Your Field Name>';
DECLARE @OrderByDir nvarchar(4)=N'ASC';
DECLARE @AUTHOR nvarchar(50) ='<Your Name & Company>';
DECLARE @DESC nvarchar(100) ='<Proc Information>'; -- Ex. 'User Data' will return 'Description : Upsert User Data'
DECLARE @ISACTIVE_SEL bit =0; --Set to 1 if your table has a Bit field named IsActive
/*DO NOT MODIFY BELOW THIS LINE!!!*/
DECLARE @NNND char(23) ='NOT_NULLABLE_NO_DEFAULT';
DECLARE @NNWD char(22) ='NOT_NULLABLE_W_DEFAULT';
DECLARE @NBLE char(8) ='NULLABLE';
DECLARE @LEGEND nvarchar(max);
DECLARE @PRIMARY_KEY nvarchar(100);
--Set up Legend
SET @LEGEND = N'USE [' + @DBName + N'];' + CHAR(13) + CHAR(10)
SET @LEGEND = @LEGEND + N'GO' + CHAR(13) + CHAR(10)
SET @LEGEND = @LEGEND + CHAR(13) + CHAR(10)
SET @LEGEND = @LEGEND + N'SET ANSI_NULLS ON' + CHAR(13) + CHAR(10)
SET @LEGEND = @LEGEND + N'GO' + CHAR(13) + CHAR(10)
SET @LEGEND = @LEGEND + CHAR(13) + CHAR(10)
SET @LEGEND = @LEGEND + N'SET QUOTED_IDENTIFIER ON' + CHAR(13) + CHAR(10)
SET @LEGEND = @LEGEND + N'GO' + CHAR(13) + CHAR(10)
SET @LEGEND = @LEGEND + CHAR(13) + CHAR(10)
SET @LEGEND = @LEGEND + N'-- ===================================================================' + CHAR(13) + CHAR(10)
SET @LEGEND = @LEGEND + N'-- Author : ' + @AUTHOR + CHAR(13) + CHAR(10)
SET @LEGEND = @LEGEND + N'-- Create date : ' + CONVERT(nvarchar(30),GETDATE(),101) + CHAR(13) + CHAR(10)
SET @LEGEND = @LEGEND + N'-- Revised date: ' + CHAR(13) + CHAR(10)
--Get Primary Key Field
SELECT TOP 1 @PRIMARY_KEY = COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1 AND TABLE_NAME = @TableName AND TABLE_CATALOG = @DBName;
DECLARE TableCol Cursor FOR
SELECT c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH
, IIF(c.COLUMN_NAME='RowVersion',@NBLE,IIF(c.COLUMN_NAME=@PRIMARY_KEY,@NBLE,IIF(c.IS_NULLABLE = 'NO' AND c.COLUMN_DEFAULT IS NULL,@NNND,IIF(c.IS_NULLABLE = 'NO' AND c.COLUMN_DEFAULT IS NOT NULL,@NNWD,@NBLE)))) AS [NULLABLE_TYPE]
FROM INFORMATION_SCHEMA.Columns c INNER JOIN
INFORMATION_SCHEMA.Tables t ON c.TABLE_NAME = t.TABLE_NAME
WHERE t.Table_Catalog = @DBName
AND t.TABLE_TYPE = 'BASE TABLE'
AND t.TABLE_NAME = @TableName
ORDER BY [NULLABLE_TYPE], c.ORDINAL_POSITION;
DECLARE @TableSchema varchar(100), @cTableName varchar(100), @ColumnName varchar(100);
DECLARE @DataType varchar(30), @CharLength int, @NullableType varchar(30);
DECLARE @PARAMETERS nvarchar(max);
DECLARE @INSERT_FIELDS nvarchar(max),@INSERT_VALUES nvarchar(max);
DECLARE @UPDATE_VALUES nvarchar(max);
SET @PARAMETERS ='@MyID int,';
SET @INSERT_FIELDS ='';
SET @INSERT_VALUES ='';
SET @UPDATE_VALUES ='';
-- open the cursor
OPEN TableCol
-- get the first row of cursor into variables
FETCH NEXT FROM TableCol INTO @TableSchema, @cTableName, @ColumnName, @DataType, @CharLength, @NullableType
WHILE @@FETCH_STATUS = 0
BEGIN
IF @ColumnName NOT IN('Created','CreatedBy','Modified','ModifiedBy')
BEGIN
SET @PARAMETERS=@PARAMETERS + '@' + IIF(@ColumnName=@PRIMARY_KEY,'ID',@ColumnName) + ' ' + iif(@CharLength IS NULL,@DataType,@DataType + '(' +
CAST(@CharLength AS nvarchar(10)) + ')') + IIF(@NullableType=@NNND OR @NullableType=@NNWD,',','=NULL,');
IF @ColumnName <> @PRIMARY_KEY AND @ColumnName <> N'RowVersion'
BEGIN
SET @INSERT_FIELDS=@INSERT_FIELDS + '[' + @ColumnName + '],';
SET @INSERT_VALUES=@INSERT_VALUES + '@' + IIF(@ColumnName=@PRIMARY_KEY,'ID',@ColumnName) + ',';
SET @UPDATE_VALUES=@UPDATE_VALUES + '[' + @ColumnName + ']=@' + IIF(@ColumnName=@PRIMARY_KEY,'ID',@ColumnName) + ',';
END
END
FETCH NEXT FROM TableCol INTO @TableSchema, @cTableName, @ColumnName, @DataType, @CharLength, @NullableType
END;
SET @PARAMETERS=LEFT(@PARAMETERS,LEN(@PARAMETERS)-1)
SET @INSERT_FIELDS=LEFT(@INSERT_FIELDS,LEN(@INSERT_FIELDS)-1)
SET @INSERT_VALUES=LEFT(@INSERT_VALUES,LEN(@INSERT_VALUES)-1)
SET @UPDATE_VALUES=LEFT(@UPDATE_VALUES,LEN(@UPDATE_VALUES)-1)
-- ----------------
-- clean up cursor
-- ----------------
CLOSE TableCol;
DEALLOCATE TableCol;
--Print Upsert Statement
PRINT N'/****** Object: StoredProcedure [dbo].[' + @ProcName + '_ups] Script Date: ' + CAST(GETDATE() AS nvarchar(30)) + ' ******/' + CHAR(13) + CHAR(10)
PRINT @LEGEND;
PRINT N'-- Description : Upsert ' + @DESC + CHAR(13) + CHAR(10)
PRINT N'-- ===================================================================' + CHAR(13) + CHAR(10)
PRINT CHAR(13) + CHAR(10)
PRINT N'CREATE PROCEDURE [dbo].[' + @ProcName + '_ups]' + CHAR(13) + CHAR(10);
PRINT N' (' + @PARAMETERS + N')' + CHAR(13) + CHAR(10);
PRINT N'AS' + CHAR(13) + CHAR(10)
PRINT N'BEGIN' + CHAR(13) + CHAR(10)
PRINT N' SET CONTEXT_INFO @MyID;' + CHAR(13) + CHAR(10)
PRINT N' IF @ID IS NULL OR @ID = 0' + CHAR(13) + CHAR(10)
PRINT N' BEGIN' + CHAR(13) + CHAR(10)
PRINT N' INSERT INTO [dbo].[' + @TableName + ']' + CHAR(13) + CHAR(10)
PRINT N' (' + @INSERT_FIELDS + N')' + CHAR(13) + CHAR(10)
PRINT N' VALUES' + CHAR(13) + CHAR(10)
PRINT N' (' + @INSERT_VALUES + N');' + CHAR(13) + CHAR(10)
PRINT N' SELECT * FROM [dbo].[' + @ViewName + '] WHERE [ID] = SCOPE_IDENTITY();' + CHAR(13) + CHAR(10)
PRINT N' END' + CHAR(13) + CHAR(10)
PRINT N' ELSE' + CHAR(13) + CHAR(10)
PRINT N' BEGIN' + CHAR(13) + CHAR(10)
PRINT N' UPDATE [dbo].[' + @TableName + ']' + CHAR(13) + CHAR(10)
PRINT N' SET ' + @UPDATE_VALUES + CHAR(13) + CHAR(10)
PRINT N' WHERE ([' + @PRIMARY_KEY + '] = @ID) AND ([RowVersion] = @RowVersion);' + CHAR(13) + CHAR(10)
PRINT N' SELECT * FROM [dbo].[' + @ViewName + '] WHERE [ID] = @ID;' + CHAR(13) + CHAR(10)
PRINT N' END' + CHAR(13) + CHAR(10)
PRINT N'END' + CHAR(13) + CHAR(10)
PRINT N'GO' + CHAR(13) + CHAR(10)
PRINT CHAR(13) + CHAR(10)
----Now add GRANT and DENY permissions to the Role
PRINT N'GRANT EXECUTE ON [dbo].[' + @ProcName + '_ups] TO [' + @DBRoleName + ']' + CHAR(13) + CHAR(10)
PRINT N'GO' + CHAR(13) + CHAR(10)
PRINT N'DENY VIEW DEFINITION ON [dbo].[' + @ProcName + '_ups] TO [' + @DBRoleName + ']' + CHAR(13) + CHAR(10)
PRINT N'GO' + CHAR(13) + CHAR(10)
PRINT CHAR(13) + CHAR(10)
PRINT CHAR(13) + CHAR(10)
--Print Select Statement
PRINT N'/****** Object: StoredProcedure [dbo].[' + @ProcName + '_sel] Script Date: ' + CAST(GETDATE() AS nvarchar(30)) + ' ******/' + CHAR(13) + CHAR(10)
PRINT @LEGEND;
PRINT N'-- Description : Select ' + @DESC + CHAR(13) + CHAR(10)
PRINT N'-- ===================================================================' + CHAR(13) + CHAR(10)
PRINT CHAR(13) + CHAR(10)
PRINT N'CREATE PROCEDURE [dbo].[' + @ProcName + '_sel]' + CHAR(13) + CHAR(10);
PRINT N' (@MyID int, @ID int=NULL' + IIF(@ISACTIVE_SEL = 1,', @IsActive bit=NULL','') + ')' + CHAR(13) + CHAR(10);
PRINT N'AS' + CHAR(13) + CHAR(10)
PRINT N'BEGIN' + CHAR(13) + CHAR(10)
PRINT N' SET CONTEXT_INFO @MyID;' + CHAR(13) + CHAR(10)
PRINT N' IF @ID IS NULL OR @ID = 0' + CHAR(13) + CHAR(10)
IF @ISACTIVE_SEL = 1
BEGIN
PRINT N' BEGIN' + CHAR(13) + CHAR(10)
PRINT N' IF @IsActive IS NULL' + CHAR(13) + CHAR(10)
PRINT N' SELECT * FROM [dbo].[' + @ViewName + '] ORDER BY [' + @OrderBy + '] ' + @OrderByDir + ';' + CHAR(13) + CHAR(10)
PRINT N' ELSE' + CHAR(13) + CHAR(10)
PRINT N' SELECT * FROM [dbo].[' + @ViewName + '] WHERE [isActive] = @IsActive ORDER BY [' + @OrderBy + '] ' + @OrderByDir + ';' + CHAR(13) + CHAR(10)
PRINT N' END' + CHAR(13) + CHAR(10)
END
ELSE
PRINT N' SELECT * FROM [dbo].[' + @ViewName + '] ORDER BY [' + @OrderBy + '] ' + @OrderByDir + ';' + CHAR(13) + CHAR(10)
PRINT N' ELSE' + CHAR(13) + CHAR(10)
PRINT N' SELECT * FROM [dbo].[' + @ViewName + '] WHERE [ID] = @ID;' + CHAR(13) + CHAR(10)
PRINT N'END' + CHAR(13) + CHAR(10)
PRINT N'GO' + CHAR(13) + CHAR(10)
PRINT CHAR(13) + CHAR(10)
----Now add GRANT and DENY permissions to the Role
PRINT N'GRANT EXECUTE ON [dbo].[' + @ProcName + '_sel] TO [' + @DBRoleName + ']' + CHAR(13) + CHAR(10)
PRINT N'GO' + CHAR(13) + CHAR(10)
PRINT N'DENY VIEW DEFINITION ON [dbo].[' + @ProcName +'_sel] TO [' + @DBRoleName + ']' + CHAR(13) + CHAR(10)
PRINT N'GO' + CHAR(13) + CHAR(10)
PRINT CHAR(13) + CHAR(10)
PRINT CHAR(13) + CHAR(10)
--Print Delete Statement
PRINT N'/****** Object: StoredProcedure [dbo].[' + @ProcName + '_del] Script Date: ' + CAST(GETDATE() AS nvarchar(30)) + ' ******/' + CHAR(13) + CHAR(10)
PRINT @LEGEND;
PRINT N'-- Description : Delete ' + @DESC + CHAR(13) + CHAR(10)
PRINT N'-- ===================================================================' + CHAR(13) + CHAR(10)
PRINT CHAR(13) + CHAR(10)
PRINT N'CREATE PROCEDURE [dbo].[' + @ProcName + '_del]' + CHAR(13) + CHAR(10);
PRINT N' (@MyID int, @ID int, @RowVersion int)' + CHAR(13) + CHAR(10);
PRINT N'AS' + CHAR(13) + CHAR(10)
PRINT N'BEGIN' + CHAR(13) + CHAR(10)
PRINT N' SET CONTEXT_INFO @MyID;' + CHAR(13) + CHAR(10)
PRINT N' SET NOCOUNT ON;' + CHAR(13) + CHAR(10)
PRINT N' DELETE FROM [dbo].[' + @TableName + '] WHERE [' + @PRIMARY_KEY + ']=@ID AND [RowVersion]=@RowVersion;' + CHAR(13) + CHAR(10)
PRINT N' SELECT @@ROWCOUNT as [Rows Affected];' + CHAR(13) + CHAR(10)
PRINT N'END' + CHAR(13) + CHAR(10)
PRINT N'GO' + CHAR(13) + CHAR(10)
PRINT CHAR(13) + CHAR(10)
----Now add GRANT and DENY permissions to the Role
PRINT N'GRANT EXECUTE ON [dbo].[' + @ProcName + '_del] TO [' + @DBRoleName + ']' + CHAR(13) + CHAR(10)
PRINT N'GO' + CHAR(13) + CHAR(10)
PRINT N'DENY VIEW DEFINITION ON [dbo].[' + @ProcName +'_del] TO [' + @DBRoleName + ']' + CHAR(13) + CHAR(10)
PRINT N'GO' + CHAR(13) + CHAR(10)
- KacireeSoftware
2
3太好了，谢谢。对于那些在非 DBO 模式表上使用此代码的人，除了使用模式变量之外，还需要修改获取主键代码以考虑模式。OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + CONSTRAINT_NAME)) - jbd
以下是有关编程的内容，需要从英语翻译成中文。请仅返回已翻译的文本：这个应该放在新文档中，类似于“如何为mssql生成CRUD”。 - Yordan Georgiev
14
除了之前提到的工具，还有另一个免费工具可以帮助您在几个点击内完成任务。
要使用此工具，您需要在ApexSQL Complete选项窗口中输入前缀和后缀，并选择每个CRUD过程模板（Select、Insert、Update、Delete）的其中一个子选项卡。完成后，在对象资源管理器窗口中右键单击数据库或表，下拉菜单中将出现CRUD过程功能。
这里有一篇文章详细介绍了此功能（虽然文章有点旧，但该功能已添加到当前版本中）。
要使用此工具，您需要在ApexSQL Complete选项窗口中输入前缀和后缀，并选择每个CRUD过程模板（Select、Insert、Update、Delete）的其中一个子选项卡。完成后，在对象资源管理器窗口中右键单击数据库或表，下拉菜单中将出现CRUD过程功能。
这里有一篇文章详细介绍了此功能（虽然文章有点旧，但该功能已添加到当前版本中）。
- V.Jokinen
-1
CREATE PROCEDURE SP_USUARIO
@usuario varchar(30)=null,
@clave varchar(500)=null,
@idPersona int=null,
@idRol int=null,
@idUsuario int=null,
@TIPO TINYINT=1
AS
BEGIN
IF @TIPO=1 -- LISTAR PERSONAS
BEGIN
SELECT idPersona,Nombre,ApePaterno,ApeMaterno,DNI,Direccion,celular,email FROM Persona
END
IF @TIPO=2 -- LISTAR ROLES
BEGIN
SELECT idRol,Nombre,Descripcion FROM ROL
END
IF @TIPO=3 -- LISTAR USUARIOS
BEGIN
SELECT idUsuario,usuario,pass,idPersona,idRol from Usuario
END
IF @TIPO=3 -- USUARIOS
BEGIN
SELECT idUsuario,usuario,pass,idPersona,idRol from Usuario where idUsuario=@idUsuario
END
IF @TIPO=4 -- AGREGAR USUARIO
BEGIN
INSERT Usuario(usuario,pass,idPersona,idRol) values(@usuario,@clave,@idPersona,@idRol)
END
IF @TIPO=5 -- EDITAR USUARIO
BEGIN
UPDATE Usuario SET usuario=@usuario,pass=@clave,@idPersona=@idPersona,idRol=@idRol where idUsuario=@idUsuario
END
IF @TIPO=6 -- ELIMINAR USUARIO
BEGIN
DELETE Usuario WHERE idUsuario=@idUsuario
END
END
go
- user9817675
-1
-- Here end end of the procedure (started on another post)
If (LEN(@ColumnParametersInsert)>0)
Begin
Set @ColumnParametersInsert = LEFT(@ColumnParametersInsert,LEN(@ColumnParametersInsert)-3) ;
SET @LastPosOfComma = LEN(@ColumnParametersInsertForExec) - CHARINDEX(' ,',REVERSE(@ColumnParametersInsertForExec))
SET @ColumnParametersInsertForExec = LEFT(@ColumnParametersInsertForExec,@LastPosOfComma+3) + SUBSTRING(@ColumnParametersInsertForExec,@LastPosOfComma+5,40000);
Set @ColumnParametersDelete = LEFT(@ColumnParametersDelete,LEN(@ColumnParametersDelete)-4) ;
SET @LastPosOfComma = LEN(@ColumnParametersDeleteForExec) - CHARINDEX(' ,',REVERSE(@ColumnParametersDeleteForExec))
SET @ColumnParametersDeleteForExec = LEFT(@ColumnParametersDeleteForExec,@LastPosOfComma+3) + SUBSTRING(@ColumnParametersDeleteForExec,@LastPosOfComma+5,40000);
SET @ColumnParametersList = LEFT(@ColumnParametersList,LEN(@ColumnParametersList)-3) ;
SET @LastPosOfComma = LEN(@ColumnParametersListForExec) - CHARINDEX(' ,',REVERSE(@ColumnParametersListForExec))
SET @ColumnParametersListForExec = LEFT(@ColumnParametersListForExec,@LastPosOfComma+3) + SUBSTRING(@ColumnParametersListForExec,@LastPosOfComma+5,40000);
IF LEN(@ColumnInValueForInsert)>0
Set @ColumnInValueForInsert = LEFT(@ColumnInValueForInsert,LEN(@ColumnInValueForInsert)-3) ;
IF LEN(@ColumnDefForInsert)>0
Set @ColumnDefForInsert = LEFT(@ColumnDefForInsert,LEN(@ColumnDefForInsert)-3) ;
IF LEN(@tableCols)>0
Set @tableCols = LEFT(@tableCols,LEN(@tableCols)-1) ;
IF LEN(@updCols)>0
Set @updCols = LEFT(@updCols,LEN(@updCols)-3) ;
SET @tableColumnForWhereInListVariables = LEFT(@tableColumnForWhereInListVariables,LEN(@tableColumnForWhereInListVariables)-3)
SET @tableColumnForWhereInListAffectVariables = LEFT(@tableColumnForWhereInListAffectVariables,LEN(@tableColumnForWhereInListAffectVariables)-3) ;
END
If (LEN(@whereCols)>0)
Set @whereCols = 'WHERE ' + LEFT(@whereCols,LEN(@whereCols)-4) ;
ELSE
Set @whereCols = 'WHERE 1=0 --Too dangerous to do update or delete on all the table'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- ============================================='
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- Author : ' + SYSTEM_USER
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- Create date : ' + Convert(varchar(20),Getdate())
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- Description : Insert Procedure for ' + @CurrentTableName
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- ============================================='
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'IF OBJECT_ID(''' + REPLACE(@insertSPName,'''','''''') + ''',''P'') IS NOT NULL'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + ' DROP PROCEDURE ' + @insertSPName
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'GO'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + ''
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + ''
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'CREATE PROCEDURE ' + @insertSPName
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + '' + @ColumnParametersInsert
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @strBegin
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @spaceForTrans + 'INSERT INTO ' + @CurrentFullTableName + '('
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @spaceForTrans + ' ' + '' + @ColumnDefForInsert + ')'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @spaceForTrans + 'VALUES ('
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @spaceForTrans + ' ' + '' + @ColumnInValueForInsert + ')'
IF @NbPrimaryKey =1 --No return if 2 or 0 primarykeys
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @spaceForTrans + 'SELECT SCOPE_IDENTITY() AS ' + @LastPrimaryKey
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @strEnd
Set @strSPText = @strSPText + @SetVariablesForExec
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'EXEC ' + @insertSPName + ' ' + @ColumnParametersInsertForExec
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'GO'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'SELECT * FROM ' + @CurrentFullTableName + ' ORDER BY 1 DESC'
IF @UnCommentExecForDebug = 0 Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '*/'
INSERT INTO @StatementList (FullTableName,StatementType,Statement) VALUES (@CurrentFullTableName,'Insert',@strSPText)
Set @strSPText = ''
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- ============================================='
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- Author : ' + SYSTEM_USER
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- Create date : ' + Convert(varchar(20),Getdate())
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- Description : Update Procedure for ' + @CurrentTableName
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- ============================================='
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'IF OBJECT_ID(''' + REPLACE(@updateSPName,'''','''''') + ''',''P'') IS NOT NULL'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + ' DROP PROCEDURE ' + @updateSPName
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'GO'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + ''
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + ''
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'CREATE PROCEDURE ' + @updateSPName
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + '' + @ColumnParametersUpdate
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @strBegin
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @spaceForTrans+ 'UPDATE ' + @CurrentFullTableName
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @spaceForTrans+ ' SET ' + @updCols
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @spaceForTrans+ ' ' + @whereCols
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @strEnd
Set @strSPText = @strSPText + @SetVariablesForExec + @SetVariablesForExecUpdate
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'EXEC ' + @updateSPName + ' ' + @ColumnParametersUpdateForExec
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'SELECT * FROM ' + @CurrentFullTableName + ' '
IF @UnCommentExecForDebug = 0 Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '*/'
INSERT INTO @StatementList (FullTableName,StatementType,Statement) VALUES (@CurrentFullTableName,'Update',@strSPText)
Set @strSPText = ''
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- ============================================='
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- Author : ' + SYSTEM_USER
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- Create date : ' + Convert(varchar(20),Getdate())
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- Description : Delete Procedure for ' + @CurrentTableName
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- ============================================='
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'IF OBJECT_ID(''' + REPLACE(@deleteSPName,'''','''''') + ''',''P'') IS NOT NULL'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + ' DROP PROCEDURE ' + @deleteSPName
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'GO'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + ''
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + ''
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'CREATE PROCEDURE ' + @deleteSPName
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + '' + @ColumnParametersDelete
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @strBegin
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + 'DELETE FROM ' + @CurrentFullTableName
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + ' ' + @whereCols
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @strEnd
Set @strSPText = @strSPText + @SetVariablesForExecDelete
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'EXEC ' + @deleteSPName + ' ' + @ColumnParametersDeleteForExec
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'GO'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'SELECT * FROM ' + @CurrentFullTableName + ' ORDER BY 1 DESC'
IF @UnCommentExecForDebug = 0 Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '*/'
INSERT INTO @StatementList (FullTableName,StatementType,Statement) VALUES (@CurrentFullTableName,'Delete',@strSPText)
Set @strSPText = ''
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- ============================================='
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- Author : ' + SYSTEM_USER
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- Create date : ' + Convert(varchar(20),Getdate())
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- Description : List Procedure for ' + @CurrentFullTableName
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- ============================================='
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'IF OBJECT_ID(''' + REPLACE(@listSPName,'''','''''') + ''',''P'') IS NOT NULL'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + ' DROP PROCEDURE ' + @listSPName
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'GO'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + ''
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'CREATE PROCEDURE ' + @listSPName
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + '' + @ColumnParametersList
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @strBegin
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space +' DECLARE @Separator nvarchar(20) =''
WHERE '''
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space +' DECLARE @SeparatorAnd nvarchar(20) =''
AND '''
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space +' DECLARE @Statement nvarchar(max) =''SELECT *
FROM ' + REPLACE(@CurrentFullTableName,'''','''''') + '''' + @tableColumnForWhereInList
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + ' --PRINT @Statement'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + ' BEGIN TRY'
IF @GenerateDebugScriptForList=1
BEGIN
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + ' IF 1=0--DEBUG --TODO: verify if not set for final version'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + ' BEGIN'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + ' DECLARE @FullQueryForDebug nvarchar(max)=' + @DebugVariablesForList
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + ' CHAR(13) +CHAR(10) + @Statement'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + ' '
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + ' --PRINT @FullQueryForDebug'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + ' --EXEC [K2FranceDebugDB].dbo.K2FranceDebug ''@FullQueryForDebug DIRECT'', @FullQueryForDebug'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + ' --EXEC loopbackServerForDebug.[K2FranceDebugDB].dbo.K2FranceDebug ''@FullQueryForDebug loopback'', @FullQueryForDebug'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + ' END'
END
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + ' exec sp_executesql @Statement ,N''' + @tableColumnForWhereInListVariables + ''',' + @tableColumnForWhereInListAffectVariables
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + ' END TRY'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + ' BEGIN CATCH'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + ' DECLARE @ErrorToDisplay nvarchar(max)= ''Error trying to execute Query Error number:'' + CAST(ERROR_NUMBER() AS nvarchar(max)) +
--'' Error severity:'' + ISNULL(CAST(ERROR_SEVERITY() AS nvarchar(max)),'''') +
--'' Error state:'' + ISNULL(CAST(ERROR_STATE() AS nvarchar(max)),'''') +
--'' Error procedure:'' + ISNULL(CAST(ERROR_PROCEDURE() AS nvarchar(max)),'''') +
--'' Error line:'' + ISNULL(CAST(ERROR_LINE() AS nvarchar(max)),'''') +
'' Error message:'' + ISNULL(CAST(ERROR_MESSAGE() AS nvarchar(max)),'''')
'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + ' RAISERROR(@ErrorToDisplay, 16, 1);'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + ' END CATCH'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @strEnd
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'EXEC ' + @listSPName + ' ' + @ColumnParametersListForExec
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'GO'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'SELECT * FROM ' + @CurrentFullTableName + ' ORDER BY 1'
IF @UnCommentExecForDebug = 0 Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '*/'
INSERT INTO @StatementList (FullTableName,StatementType,Statement) VALUES (@CurrentFullTableName,'List',@strSPText)
Drop table #tmp_Structure
Fetch next from Tables_cursor INTO @CurrentSchemaName,@CurrentTableName
END
CLOSE Tables_cursor
DEALLOCATE Tables_cursor
SET @DropStatement = '
------------------------------------------- TO CLEAN COMPLETELY THE APPLICATION ---------------------------------------
/*' + @DropStatement +'
*/'
INSERT INTO @StatementList (FullTableName,StatementType,Statement) VALUES ('Common','Drop statement to put at the end of final script',@DropStatement)
SELECT * FROM @StatementList
ORDER BY 1
END
GO
--For all tables of schema dbo of database "OlivierDb":
EXEC dbo.GenerateDynamicallyProceduresForTables 'OlivierDB','dbo'
--With all possible parameters:
EXEC dbo.GenerateDynamicallyProceduresForTables @DatabaseName = 'OlivierDB',
@SchemaName = 'dbo',
@TableName = 'Table5',
@NoCount = 1,
@ManageTransaction = 1,
@GenerateDebugScriptForList = 1,
@ParameterForUser = '@UserInP',
@ParameterForCulture = '@CultureInP',
@FirstParametersAreMandatory= 1,
@ProcedureTemplateName = '[{SchemaName}].[{TableName}_Proc_{ActionType}]',
@ColumnNameLimitation = '', --(syscolumns.name LIKE ''%Creation%'' OR syscolumns.name IN (''SomeInt'',''Somebit'') )
@CreationUserMatch = 'syscolumns.name LIKE ''%CreationUser%'' OR syscolumns.name LIKE ''%CreationBy%''',
@CreationDateMatch = 'syscolumns.name LIKE ''%CreationDate%'' OR syscolumns.name LIKE ''%CreatedDate%''',
@ModificationUserMatch = 'syscolumns.name LIKE ''%ModificationUser%'' OR syscolumns.name LIKE ''%ModifiedBy%'' OR syscolumns.name LIKE ''%ModifiedUser%''',
@ModificationDateMatch = 'syscolumns.name LIKE ''%ModificationDate%'' OR syscolumns.name LIKE ''%ModifiedDate%'''
- Olivier Chatagnon
-1
感谢原始脚本。
我在以下方面进行了改进：
- 允许在另一个数据库上执行
- 对于选择，根据参数动态生成查询
- 管理CreatedBy/Date和ModificationBy/Date列
- 即使在模式/表/列中发现特殊字符，也可以正常工作
- 允许系统地添加用户和文化。
- 存储过程名称的模板
以及很多选项。
注意：代码分为两个答案发送，因为限制了30000个字符。
IF OBJECT_ID('dbo.GenerateDynamicallyProceduresForTables','P') IS NOT NULL
DROP PROCEDURE dbo.GenerateDynamicallyProceduresForTables
GO
CREATE PROCEDURE dbo.GenerateDynamicallyProceduresForTables @DatabaseName nvarchar(200)=NULL,
@SchemaName nvarchar(200) = NULL,
@TableName nvarchar(200) = NULL,
@NoCount bit=1,
@ManageTransaction bit=1,
@GenerateDebugScriptForList bit = 1,
@ParameterForUser nvarchar(20) = '@UserInP',
@ParameterForCulture nvarchar(20) = '@CultureInP',
@FirstParametersAreMandatory bit=1,
@ProcedureTemplateName nvarchar(100) = '[{SchemaName}].[{TableName}_Proc_{ActionType}]',
@ColumnNameLimitation nvarchar(500)= '', --(syscolumns.name LIKE ''%Creation%'' OR syscolumns.name IN (''SomeInt'',''Somebit'') )
@CreationUserMatch nvarchar(500) = 'syscolumns.name LIKE ''%CreationUser%'' OR syscolumns.name LIKE ''%CreationBy%''',
@CreationDateMatch nvarchar(500) = 'syscolumns.name LIKE ''%CreationDate%'' OR syscolumns.name LIKE ''%CreatedDate%''',
@ModificationUserMatch nvarchar(500) = 'syscolumns.name LIKE ''%ModificationUser%'' OR syscolumns.name LIKE ''%ModifiedBy%'' OR syscolumns.name LIKE ''%ModifiedUser%''',
@ModificationDateMatch nvarchar(500) = 'syscolumns.name LIKE ''%ModificationDate%'' OR syscolumns.name LIKE ''%ModifiedDate%'''
AS
BEGIN
DECLARE @UnCommentExecForDebug bit=0 --To set at 0 for final
DECLARE @StatementList TABLE(id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,FullTableName nvarchar(1000),StatementType nvarchar(100),Statement nvarchar(max))
DECLARE @FirstParameters nvarchar(400)='',@FirstParametersForExec nvarchar(400)=''
IF LEN(@ParameterForUser)>1
BEGIN
SET @FirstParameters = @FirstParameters + @ParameterForUser +' nvarchar(500)' + CASE WHEN @FirstParametersAreMandatory =0 THEN ' = NULL' ELSE '' END + ',
'
SET @FirstParametersForExec = @FirstParametersForExec + @ParameterForUser + CASE WHEN @FirstParametersAreMandatory =0 THEN ' = NULL' ELSE ' =''K2:Denallix\Administrator''' END + ',
'
END
IF LEN(@ParameterForCulture)>1
BEGIN
SET @FirstParameters = @FirstParameters + @ParameterForCulture + ' nvarchar(10)' + CASE WHEN @FirstParametersAreMandatory =0 THEN ' = NULL' ELSE '' END + ',
'
SET @FirstParametersForExec = @FirstParametersForExec + @ParameterForCulture + CASE WHEN @FirstParametersAreMandatory =0 THEN ' = NULL' ELSE '=''en-gb''' END + ',
'
END
IF NOT(LEN(@DatabaseName)>0)
SET @DatabaseName=DB_NAME()
IF LEN(@SchemaName)=0
SET @SchemaName=NULL
IF LEN(@TableName)=0
SET @TableName=NULL
IF NOT(LEN(@ColumnNameLimitation)>0)
SET @ColumnNameLimitation = '1=1'
IF NOT(LEN(@CreationUserMatch)>0)
SET @CreationUserMatch = 'syscolumns.name = ''BIDON12345678917071979'''
IF NOT(LEN(@CreationDateMatch)>0)
SET @CreationDateMatch = 'syscolumns.name = ''BIDON12345678917071979'''
IF NOT(LEN(@ModificationUserMatch)>0)
SET @ModificationUserMatch = 'syscolumns.name = ''BIDON12345678917071979'''
IF NOT(LEN(@ModificationDateMatch)>0)
SET @ModificationDateMatch = 'syscolumns.name = ''BIDON12345678917071979'''
DECLARE @strSpText nVarchar(max) ='USE [' + @DatabaseName + ']'
IF @DatabaseName!=DB_NAME()
INSERT INTO @StatementList (FullTableName,StatementType,Statement) VALUES ('Common','Set current database',@strSPText)
DECLARE @sqlstatementForTables nvarchar(max) = -- Not test with USE [' + @DatabaseName + '] ISSUE ON Table iDENTITY.Identity: 'Could not complete cursor operation because the set options have changed since the cursor was declared
N'
DECLARE Tables_cursor CURSOR FOR
SELECT TABLE_SCHEMA,TABLE_NAME
FROM [' + @DatabaseName + '].INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE=''BASE TABLE''
AND (TABLE_SCHEMA=@pSchemaName OR @pSchemaName IS NULL)
AND (Table_Name=@pTableName OR @pTableName IS NULL)'
--EXEC loopbackServerForDebug.[K2FranceDebugDB].dbo.K2FranceDebug '@sqlstatementForColumns',@sqlstatementForColumns
exec sp_executesql @sqlstatementForTables, N'@pSchemaName nvarchar(200),@pTableName nvarchar(200)', @pSchemaName=@SchemaName, @pTableName=@TableName;
OPEN Tables_cursor
DECLARE @CurrentSchemaName nvarchar(100),@CurrentFullTableName nvarchar(1000),@CurrentTableName nVarchar(1000),
@DropStatement nvarchar(max)=''
Fetch next
from Tables_cursor
INTO @CurrentSchemaName,@CurrentTableName
WHILE @@FETCH_STATUS=0
BEGIN
SET @CurrentFullTableName='['+@CurrentSchemaName+'].['+@CurrentTableName+']';
--PRINT @CurrentFullTableName
Declare @dbName nVarchar(50)
Declare @insertSPName nVarchar(4000), @updateSPName nVarchar(4000), @deleteSPName nVarchar(4000), @listSPName nVarchar(4000)--, @ReadSPName nVarchar(50) ;
Declare @ColumnParametersInsert nVarchar(max), @ColumnDefForInsert nVarchar(max),@ColumnInValueForInsert nVarchar(max),
@ColumnParametersList nVarchar(max),@ColumnParametersListForExec nVarchar(max),
@tableColumnForWhereInList nvarchar(max),
@tableColumnForWhereInListVariables nVarchar(max), @tableColumnForWhereInListAffectVariables nVarchar(max),@DebugVariablesForList nvarchar(max)='',
@ColumnParametersInsertForExec nvarchar(max)
Declare @tableCols nVarchar(max), @ColumnParametersUpdate nVarchar(max),@ColumnParametersUpdateForExec nVarchar(max);
Declare @space nVarchar(50) = REPLICATE(' ', 4) ;
Declare @colName nVarchar(max) ;
Declare @DataType nvarchar(200),@colVariable nVarchar(200),@colVariableProc nVarchar(200);
Declare @colParameter nVarchar(max) ;
Declare @colAllowNull nvarchar(15), @colIsPrimaryKey INT,@ColIsIdentityAutoIncrement INT,@ColLength INT,@ColIsComputed INT,@ColMatchCreationUser INT,@ColMatchCreationDate INT,@ColMatchModificationUser INT,@ColMatchModificationDate INT;
Declare @updCols nVarchar(max);
Declare @ColumnParametersDelete nVarchar(max),@ColumnParametersDeleteForExec nVarchar(max),
@LastPrimaryKey nvarchar(max),@NbPrimaryKey INT=0,@ColNumber int=0
Declare @whereCols nVarchar(2000);
DECLARE @SetVariablesForExec nvarchar(max)='',@SetVariablesForExecUpdate nvarchar(max)='', @SetVariablesForExecDelete nvarchar(max)=''
DECLARE @strBegin nvarchar(1000)=' AS' + CHAR(13) + CHAR(10) + 'BEGIN',@spaceForTrans nvarchar(10)=''
IF @NoCount=1
Set @strBegin = @strBegin + CHAR(13) + CHAR(10) + @space + 'SET NOCOUNT ON '
IF @ManageTransaction = 1
BEGIN
Set @strBegin = @strBegin + CHAR(13) + CHAR(10) + @space + 'SET XACT_ABORT ON -- if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.'
Set @strBegin = @strBegin + CHAR(13) + CHAR(10) + ''
Set @strBegin = @strBegin + CHAR(13) + CHAR(10) + @space + 'BEGIN TRAN '
SET @spaceForTrans= @space;
END
DECLARE @strEnd nvarchar(1000)=''
IF @ManageTransaction = 1
Set @strEnd = @strEnd + CHAR(13) + CHAR(10) + @space + 'COMMIT TRAN '
SET @strEnd = @strEnd + CHAR(13) + CHAR(10) + 'END'
Set @strEnd = @strEnd + CHAR(13) + CHAR(10) + 'GO'
Set @strEnd = @strEnd + CHAR(13) + CHAR(10) + ''
IF @UnCommentExecForDebug = 0 Set @strEnd = @strEnd + CHAR(13) + CHAR(10) + '/*'
IF @ProcedureTemplateName IS NULL
BEGIN
Set @insertSPName = '['+@CurrentSchemaName+'].[sp_' + @CurrentTableName +'_insert]' ;
Set @updateSPName = '['+@CurrentSchemaName+'].[sp_' + @CurrentTableName +'_update]' ;
Set @deleteSPName = '['+@CurrentSchemaName+'].[sp_' + @CurrentTableName +'_delete]' ;
set @listSPName = '['+@CurrentSchemaName+'].[sp_' + @CurrentTableName +'_list]' ;
END
ELSE
BEGIN
DECLARE @ProcedureName nvarchar(200)=REPLACE(REPLACE(@ProcedureTemplateName,'{SchemaName}',ISNULL(@SchemaName,@CurrentSchemaName)),'{TableName}',ISNULL(@TableName,@CurrentTableName));
Set @insertSPName = REPLACE(@ProcedureName,'{ActionType}','Insert')
Set @updateSPName = REPLACE(@ProcedureName,'{ActionType}','Update')
Set @deleteSPName = REPLACE(@ProcedureName,'{ActionType}','Delete')
Set @listSPName = REPLACE(@ProcedureName,'{ActionType}','List')
END
SET @DropStatement = @DropStatement+ '
DROP PROCEDURE ' + @insertSPName + '
DROP PROCEDURE ' + @updateSPName + '
DROP PROCEDURE ' + @deleteSPName +'
DROP PROCEDURE ' + @listSPName
Set @ColumnParametersInsert = @FirstParameters ;
SET @ColumnParametersInsertForExec = @FirstParametersForExec
Set @ColumnParametersUpdate=@FirstParameters
SET @ColumnParametersUpdateForExec=@FirstParametersForExec
Set @ColumnParametersDelete = @FirstParameters ;
SET @ColumnParametersDeleteForExec = @FirstParametersForExec ;
SET @ColumnParametersList = @FirstParameters;
SET @ColumnParametersListForExec = @FirstParametersForExec
SET @tableColumnForWhereInList= ''
SET @tableColumnForWhereInListVariables =''
SET @tableColumnForWhereInListAffectVariables =''
SET @DebugVariablesForList ='';
Set @ColumnDefForInsert = '' ;
Set @ColumnInValueForInsert = '' ;
Set @strSPText = '' ;
Set @tableCols = '' ;
Set @updCols = '' ;
Set @whereCols = '' ;
SET NOCOUNT ON
CREATE TABLE #tmp_Structure (colid int,ColumnName nvarchar(max),
ColumnVariable nvarchar(max),
DataType nvarchar(max),
ColumnParameter nvarchar(max),
AllowNull int,
IsPrimaryKey int,
IsIdentityAutoIncrement int,
ColLength int,
IsIsComputedColumn int,
ColMatchCreationUser int,ColMatchCreationDate int,
ColMatchModificationUser INT,ColMatchModificationDate INT)
DECLARE @sqlstatementForColumns nvarchar(max) =
N'USE [' + @DatabaseName + ']
SELECT distinct
--sysobjects.name as ''Table'',
syscolumns.colid ,
''['' + syscolumns.name + '']'' as ''ColumnName'',
''@''+syscolumns.name as ''ColumnVariable'',
systypes.name +
Case When systypes.xusertype in (165,167,175,231,239 ) Then ''('' + Convert(varchar(10),Case When syscolumns.length=-1 Then ''max'' else CAST(syscolumns.length AS nvarchar(10)) end) +'')'' Else '''' end as ''DataType'' ,
systypes.name + Case When systypes.xusertype in (165,167,175,231,239 ) Then ''('' + Convert(varchar(10),Case When syscolumns.length=-1 Then ''max'' else CAST(syscolumns.length AS nvarchar(10)) end) +'')'' Else '''' end as ''ColumnParameter'',
COLUMNPROPERTY(OBJECT_ID(@pFullTableName),syscolumns.name,''AllowsNull'') AS AllowNull,
(SELECT COUNT(*)
FROM [' + @DatabaseName + '].INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab,
[' + @DatabaseName + '].INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col
WHERE Col.Constraint_Name = Tab.Constraint_Name
AND Col.Table_Name = Tab.Table_Name
AND Constraint_Type = ''PRIMARY KEY''
AND Col.Table_Name = @pTableName
AND Tab.TABLE_SCHEMA=@pSchemaName
AND Col.Column_Name = syscolumns.name
) AS IsPrimaryKey,
SC.is_identity AS IsIdentityAutoIncrement,
syscolumns.length,
(SELECT COUNT(*)
FROM sys.computed_columns
WHERE computed_columns.object_id=sysobjects.id
AND computed_columns.Name=syscolumns.name) AS IsComputedColumn,
CASE WHEN ' + @CreationUserMatch +' THEN 1 ELSE 0 END AS ColMatchCreationUser,
CASE WHEN ' + @CreationDateMatch +' THEN 1 ELSE 0 END AS ColMatchCreationDate,
CASE WHEN ' + @ModificationUserMatch +' THEN 1 ELSE 0 END AS ColMatchModificationUser,
CASE WHEN ' + @ModificationDateMatch +' THEN 1 ELSE 0 END AS ColMatchModificationDate
FROM sysobjects
LEFT JOIN syscolumns ON syscolumns.id=sysobjects.id
LEFT JOIN systypes ON systypes.xusertype=syscolumns.xusertype
LEFT JOIN sys.columns SC ON SC.object_id = sysobjects.id
AND SC.name=syscolumns.name
Where sysobjects.xtype = ''u''
and sysobjects.id = OBJECT_ID(@pFullTableName)
AND (' + @ColumnNameLimitation + ')
Order by syscolumns.colid'
--PRINT @sqlstatementForColumns
--EXEC loopbackServerForDebug.[K2FranceDebugDB].dbo.K2FranceDebug '@sqlstatementForColumns',@sqlstatementForColumns
INSERT INTO #tmp_Structure
exec sp_executesql @sqlstatementForColumns, N'@pSchemaName nvarchar(200),@pTableName nvarchar(200),@pFullTableName nvarchar(1000)', @pSchemaName=@CurrentSchemaName, @pTableName=@CurrentTableName,@pFullTableName=@CurrentFullTableName;
--SELECT * FROM #tmp_Structure
/* Read the table structure and populate variables*/
DECLARE SpText_Cursor CURSOR FOR
SELECT ColumnName, ColumnVariable, DataType, ColumnParameter, AllowNull, IsPrimaryKey, IsIdentityAutoIncrement,ColLength, IsIsComputedColumn,ColMatchCreationUser,ColMatchCreationDate,ColMatchModificationUser,ColMatchModificationDate
FROM #tmp_Structure
OPEN SpText_Cursor
FETCH NEXT FROM SpText_Cursor INTO @colName, @colVariable, @DataType, @colParameter, @colAllowNull,@colIsPrimaryKey, @ColIsIdentityAutoIncrement,@ColLength, @ColIsComputed,@ColMatchCreationUser,@ColMatchCreationDate,@ColMatchModificationUser,@ColMatchModificationDate
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ColNumber=@ColNumber+1
SET @SetVariablesForExec = @SetVariablesForExec + CASE WHEN @colAllowNull =1 THEN ''
ELSE CASE WHEN @DataType IN ('datetime','datetime2','smalldatetime','date') AND @SetVariablesForExec NOT LIKE '%@Date%' THEN CHAR(13) +CHAR(10) + 'DECLARE @Date datetime =GetDate()'
WHEN @DataType IN ('uniqueidentifier') AND @SetVariablesForExec NOT LIKE '%@GuidTest%' THEN CHAR(13) +CHAR(10) + 'DECLARE @TheGuid uniqueidentifier =NEWID()'
ELSE ''
END
END
--RegEx to keep only alphanumeric characters:
DECLARE @MatchExpression nvarchar(20) = '%[^a-z0-9]%',@DateTypeWithoutSpecialCharacters nvarchar(100)=@DataType;
WHILE PatIndex(@MatchExpression, @DateTypeWithoutSpecialCharacters) > 0
SET @DateTypeWithoutSpecialCharacters = Stuff(@DateTypeWithoutSpecialCharacters, PatIndex(@MatchExpression, @DateTypeWithoutSpecialCharacters), 1, '')
--Remove Special characters (like space...) for variable name
WHILE PatIndex(@MatchExpression, @colVariable) > 0
SET @colVariable = Stuff(@colVariable, PatIndex(@MatchExpression, @colVariable), 1, '')
SET @colVariableProc = '@p'+ @colVariable
SET @colVariable = '@'+ @colVariable
SET @colParameter = @colVariable + ' ' + @colParameter
DECLARE @AffectationForExec nvarchar(max)=@colVariable + CASE WHEN @colAllowNull =1 THEN ' = NULL'
ELSE ' = ' + CASE WHEN @DataType IN ('Text','sysname') OR @DataType LIKE '%char%' THEN '''' + SUBSTRING ( CAST(ABS(@ColLength) AS nvarchar(10)) + 'TEST' + @DateTypeWithoutSpecialCharacters,0,CASE WHEN @ColLength < 0 THEN 1000 WHEN @DataType LIKE 'nchar%' THEN @ColLength/2+1 ELSE @ColLength END) + ''''
WHEN @DataType IN ('int','numeric','bigint','tinyint') THEN CAST(@ColNumber AS nvarchar(10))
WHEN @DataType IN ('bit') THEN '0'
WHEN @DataType IN ('float') THEN CAST(@ColNumber AS nvarchar(10)) + '.' + CAST(@ColNumber+1 AS nvarchar(10))
WHEN @DataType IN ('datetime','datetime2','smalldatetime','date') THEN '@Date'
WHEN @DataType IN ('uniqueidentifier') THEN '@TheGuid'
WHEN @DataType IN ('xml') THEN '''<testXML><value name="test">' + CAST(@ColNumber AS nvarchar(10)) + '</value></testXML>'''
ELSE '''1''--Currently Not managed'
END
END + ', --Type ' + @DataType + CHAR(13) + CHAR(10) + @space
IF @ColIsIdentityAutoIncrement = 0 AND @ColIsComputed = 0
BEGIN
IF @ColMatchModificationUser = 0 AND @ColMatchModificationDate = 0
Set @ColumnDefForInsert = @ColumnDefForInsert + @colName+ ',' + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans ;
IF @ColMatchCreationUser= 0 AND @ColMatchCreationDate = 0 AND @ColMatchModificationUser = 0 AND @ColMatchModificationDate = 0
BEGIN
Set @ColumnParametersInsert = @ColumnParametersInsert + @colParameter + CASE WHEN @colAllowNull =1 THEN ' = NULL' ELSE '' END + ',' + CHAR(13) + CHAR(10) + @space ;
SET @ColumnParametersInsertForExec = @ColumnParametersInsertForExec + @AffectationForExec
END
IF @ColMatchCreationUser= 1
BEGIN
IF LEN(@ParameterForUser)>1
Set @ColumnInValueForInsert = @ColumnInValueForInsert + 'ISNULL(' + @ParameterForUser + ',SYSTEM_USER)'
ELSE
Set @ColumnInValueForInsert = @ColumnInValueForInsert + 'SYSTEM_USER'
END
ELSE
BEGIN
IF @ColMatchCreationDate= 1
Set @ColumnInValueForInsert = @ColumnInValueForInsert + 'GETDATE()'
ELSE
IF @ColMatchModificationUser = 0 AND @ColMatchModificationDate = 0
Set @ColumnInValueForInsert = @ColumnInValueForInsert + @colVariable
END
IF @ColMatchCreationUser= 1 OR @ColMatchCreationDate= 1 OR @ColMatchModificationUser = 0 AND @ColMatchModificationDate = 0
SET @ColumnInValueForInsert =@ColumnInValueForInsert + ',' + CHAR(13) + CHAR(10) + @space + @space+ @spaceForTrans
Set @tableCols = @tableCols + @colName + ',' ;
IF @ColMatchModificationUser = 1
BEGIN
IF LEN(@ParameterForUser)>1
Set @updCols = @updCols + @colName + ' = ISNULL(' + @ParameterForUser + ',SYSTEM_USER)';
ELSE
Set @updCols = @updCols + @colName + ' = SYSTEM_USER';
END
ELSE
BEGIN
IF @ColMatchModificationDate = 1
Set @updCols = @updCols + @colName + ' = GETDATE()';
ELSE
IF @ColMatchCreationUser=0 AND @ColMatchCreationDate=0
Set @updCols = @updCols + @colName + ' = ' + @colVariable;
END
IF @ColMatchModificationUser = 1 OR @ColMatchModificationDate = 1 OR @ColMatchCreationUser=0 AND @ColMatchCreationDate=0
SET @updCols =@updCols + ',' + CHAR(13) + CHAR(10) + @space + @space+ ' ' + @spaceForTrans
END
SET @ColumnParametersList = @ColumnParametersList + @colParameter + ' = NULL' + ',' + CHAR(13) + CHAR(10) + @space ;
SET @ColumnParametersListForExec = @ColumnParametersListForExec+ @colVariable + ' = NULL, --Type ' + @DataType + CHAR(13) + CHAR(10) + @space
IF @ColIsIdentityAutoIncrement = 1 AND @DataType='int'
BEGIN
SET @SetVariablesForExecUpdate = CHAR(13)+CHAR(10)+'DECLARE @PrimaryKeyValue INT= (SELECT MIN(' + @colName + ') FROM ' + @CurrentFullTableName + ')'
SET @AffectationForExec = @colVariable + '= @PrimaryKeyValue, --Type ' + @DataType + CHAR(13) + CHAR(10) + @space
END
IF @ColIsComputed = 0 AND @ColMatchCreationUser=0 AND @ColMatchCreationDate=0 AND @ColMatchModificationUser=0 AND @ColMatchModificationDate=0
BEGIN
Set @ColumnParametersUpdate = @ColumnParametersUpdate + @colParameter + ',' + CHAR(13) + CHAR(10) + @space ;
SET @ColumnParametersUpdateForExec = @ColumnParametersUpdateForExec + @AffectationForExec
END
IF @DataType NOT IN ('text')
BEGIN
IF @DataType NOT IN ('Xml')
BEGIN
SET @tableColumnForWhereInList = @tableColumnForWhereInList + '
IF ' + @colVariable + ' IS NOT NULL
BEGIN
SET @Statement= @Statement+ @Separator + ''' + REPLACE(@colName,'''','''''') + '= '+ @colVariableProc +'''
SET @Separator = @SeparatorAnd
END'
SET @tableColumnForWhereInListVariables = @tableColumnForWhereInListVariables + @space + @space + @space + @spaceForTrans + @colVariableProc + ' ' + @DataType +',
'
SET @tableColumnForWhereInListAffectVariables = @tableColumnForWhereInListAffectVariables + @space + @space + @space + @spaceForTrans + @colVariableProc + '=' + @colVariable + ',
'
END
SET @DebugVariablesForList = @DebugVariablesForList+ CHAR(13) +CHAR(10) + @space + @space + @space + @space +@space+ @spaceForTrans
IF @DataType IN ('Xml')
SET @DebugVariablesForList = @DebugVariablesForList+ 'ISNULL(''DECLARE '+ @colVariableProc + ' ' + @DataType+' = CAST('''''' + REPLACE(CAST(' +@colVariable + ' as nvarchar(max)),'''''''','''''''''''') + ''''''AS XML);''+CHAR(13)+CHAR(10) ,'''') + '
ELSE
SET @DebugVariablesForList = @DebugVariablesForList+ 'ISNULL(''DECLARE '+ @colVariableProc + ' ' + @DataType+' = '''''' + REPLACE(' +@colVariable + ','''''''','''''''''''') + '''''';''+CHAR(13)+CHAR(10) ,'''') + '
END
IF @colIsPrimaryKey= 1
BEGIN
IF @ColIsIdentityAutoIncrement = 1 AND @DataType='int'
BEGIN
SET @SetVariablesForExecDelete = CHAR(13)+CHAR(10)+'DECLARE @PrimaryKeyValue INT= (SELECT MAX(' + @colName + ') FROM ' + @CurrentFullTableName + ')'
END
SET @ColumnParametersDelete = @ColumnParametersDelete + @colParameter +', ' + CHAR(13) + CHAR(10) + @space ;
SET @ColumnParametersDeleteForExec = @ColumnParametersDeleteForExec + @AffectationForExec
SET @whereCols = @whereCols + @colName + ' = ' + @colVariable + ' AND ' ;
SET @NbPrimaryKey = @NbPrimaryKey +1
SET @LastPrimaryKey = @colName
END
FETCH NEXT FROM SpText_Cursor INTO @colName, @colVariable, @DataType,@colParameter, @colAllowNull,@colIsPrimaryKey,@ColIsIdentityAutoIncrement,@ColLength,@ColIsComputed,@ColMatchCreationUser,@ColMatchCreationDate,@ColMatchModificationUser,@ColMatchModificationDate
END
CLOSE SpText_Cursor
DEALLOCATE SpText_Cursor
IF @ColumnDefForInsert IS NULL
RAISERROR('@ColumnDefForInsert IS NULL',16,1)
IF @ColumnParametersInsert IS NULL
RAISERROR('@ColumnParametersInsert IS NULL',16,1)
IF @ColumnParametersInsertForExec IS NULL
RAISERROR('@ColumnParametersInsertForExec IS NULL',16,1)
IF @ColumnInValueForInsert IS NULL
RAISERROR('@ColumnInValueForInsert IS NULL',16,1)
IF @tableCols IS NULL
RAISERROR('@tableCols IS NULL',16,1)
IF @updCols IS NULL
RAISERROR('@updCols IS NULL',16,1)
IF @ColumnParametersDelete IS NULL
RAISERROR('@ColumnParametersDelete IS NULL',16,1)
IF @whereCols IS NULL
RAISERROR('@whereCols IS NULL',16,1)
DECLARE @LastPosOfComma INT
If (LEN(@ColumnParametersUpdate)>0)
BEGIN
Set @ColumnParametersUpdate = LEFT(@ColumnParametersUpdate,LEN(@ColumnParametersUpdate)-3) ;
SET @LastPosOfComma = LEN(@ColumnParametersUpdateForExec) - CHARINDEX(' ,',REVERSE(@ColumnParametersUpdateForExec))
SET @ColumnParametersUpdateForExec = LEFT(@ColumnParametersUpdateForExec,@LastPosOfComma+3) + SUBSTRING(@ColumnParametersUpdateForExec,@LastPosOfComma+5,40000);
END
--See next post for the end of procedure
- Olivier Chatagnon
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接
- 相关问题
- 4 在SQL Server Management Studio中多次打开存储过程文件
- 3 控制 SQL Server Management Studio 脚本生成
- 4 SQL Server Management Studio问题：创建存储过程脚本的问题
- 12 刷新SQL Server Management Studio中的表格
- 24 如何在SQL Server Management Studio中将UDTT传递给存储过程
- 9 在Microsoft Sql Server Management Studio中测试存储过程
- 4 SQL Server Management Studio中存储过程的依赖关系
- 114 如何在SQL Server Management Studio 2008中自动化“生成脚本”任务？
- 41 在SQL Server Management Studio中生成脚本
- 44 在SQL Server Management Studio中调试存储过程