SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE [dbo].[SP_系统自动生成存储过程]
AS
BEGIN
--Get DataBase Name
DECLARE @db_name varchar(40)
SELECT @db_name=db_name()
--Get table list
DECLARE _table Cursor FOR SELECT
TABLE_NAME
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME != 'dtProperties'
DECLARE @table_name nvarchar(4000)
OPEN _table
FETCH NEXT FROM _table INTO @table_name
DECLARE @shifouyouzhujian int
WHILE @@FETCH_STATUS=0
BEGIN
------------------------------------Judge whether has primarykeys information------------
SELECT @shifouyouzhujian=COUNT(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE CONSTRAINT_NAME IN
(
SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='PRIMARY KEY' AND TABLE_NAME=@table_name
)
-------------------------Get Column List Of the table----------------------------------------------------
DECLARE @columncount int--Save count of the table'column
SELECT @columncount=COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = @table_name
DECLARE _column CURSOR FOR
SELECT
INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME,
INFORMATION_SCHEMA.COLUMNS.Data_type,
INFORMATION_SCHEMA.COLUMNS.CHARACTER_MAXIMUM_LENGTH AS COLUMN_LENGTH,
COLUMNPROPERTY(OBJECT_ID(@table_name), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsIdentity') AS IS_IDENTITY
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = @table_name
DECLARE @columnname nvarchar(4000)
DECLARE @columndatatype nvarchar(4000)
DECLARE @columnlength nvarchar(4000)
DECLARE @columnisidentity nvarchar(4000)
OPEN _column
FETCH NEXT FROM _column INTO @columnname,@columndatatype,@columnlength,@columnisidentity
--Define procedure scripts characters
Declare @Str_Insert nvarchar(4000)
Declare @str_Update nvarchar(4000)
Declare @str_Delete nvarchar(4000)
Declare @str_SelectAll nvarchar(4000)
Declare @str_SelectID nvarchar(4000)
--Define procedure name
DECLARE @procedure_insert VARCHAR(200)
DECLARE @procedure_Update VARCHAR(200)
DECLARE @procedure_Delete Varchar(200)
DECLARE @procedure_SelectAll varchar(200)
DECLARE @procedure_Select varchar(200)
DECLARE @insertColu nvarchar(4000)--保存INSERT语句要插入的数据列
SET @insertColu='INSERT INTO '+@table_name+'('+CHAR(13)+CHAR(13)+CHAR(13)
DECLARE @insertValue nvarchar(4000)--保存INsert语句要插入的数据列的值
SET @insertValue='VALUES ('+CHAR(13)+CHAR(13)+CHAR(13)
DECLARE @tiaojianyuju nvarchar(4000)--保存UPDATE语句，Delete语句,以及Select语句等Where子句
SET @tiaojianyuju='WHERE'+CHAR(32)+CHAR(32)
DECLARE @updateColu nvarchar(4000)--保存Update语句要修改的数据列
SET @updateColu='UPDATE '+CHAR(32)+@table_name+CHAR(32)+CHAR(32)+'SET'+CHAR(13)+CHAR(13)++CHAR(13)+CHAR(13)+CHAR(13)+CHAR(13)
DECLARE @paraters nvarchar(4000)--保存Delete语句和SelectBYid语句的参数信息
--------Determine procedure name-----------
SET @procedure_insert='[dbo].['+'SP_'+@table_name+'_INSERT'+']'
SET @procedure_Update='[dbo].['+'SP_'+@table_name+'_UPDATE'+']'
SET @procedure_Delete='[dbo].['+'SP_'+@table_name+'_DELETE'+']'
SET @procedure_SelectAll='[dbo].['+'SP_'+@table_name+'_SELECTALL'+']'
SET @procedure_Select='[dbo].['+'SP_'+@table_name+'_SELECTById'+']'
--Procedure scripts initialization
SET @Str_Insert='GO'+CHAR(13)+CHAR(13)++CHAR(13)+'if exists (select * from dbo.sysobjects where id = object_id(N'
SET @Str_Insert=@Str_Insert+CHAR(39)+@procedure_insert+CHAR(39)+')'
SET @Str_Insert=@Str_Insert+' and OBJECTPROPERTY'+'(id, N'+CHAR(39)+'IsProcedure'+CHAR(39)+')'+'= 1)'+CHAR(13)+CHAR(13)+CHAR(13)+'BEGIN'+CHAR(13)+CHAR(13)+CHAR(13)
SET @Str_Insert=@Str_Insert+'DROP PROCEDURE'+CHAR(32)+CHAR(32)+@procedure_insert+CHAR(13)+CHAR(13)+CHAR(13)+CHAR(13)+CHAR(13)+CHAR(13)+CHAR(13)+'END'+CHAR(13)+CHAR(13)+'GO'+CHAR(13)+CHAR(13)
SET @Str_Insert=@Str_Insert+'CREATE PROCEDURE '+CHAR(32)+CHAR(32)+@procedure_insert+CHAR(32)+CHAR(32)+CHAR(13)+'('+CHAR(13)+CHAR(13)+CHAR(13)+CHAR(13)+CHAR(13)
SET @str_Update=CHAR(13)+CHAR(13)+CHAR(13)+'if exists (select * from dbo.sysobjects where id = object_id(N'
SET @str_Update=@str_Update+CHAR(39)+@procedure_Update+CHAR(39)+')'
SET @str_Update=@str_Update+' and OBJECTPROPERTY'+'(id, N'+CHAR(39)+'IsProcedure'+CHAR(39)+')'+'= 1)'+CHAR(13)+CHAR(13)+CHAR(13)+'BEGIN'+CHAR(13)+CHAR(13)+CHAR(13)
SET @str_Update=@str_Update+'DROP PROCEDURE '+CHAR(32)+CHAR(32)+@procedure_Update+CHAR(13)+CHAR(13)+CHAR(13)+CHAR(13)+CHAR(13)+CHAR(13)+CHAR(13)+'END'+CHAR(13)+CHAR(13)+'GO'+CHAR(13)+CHAR(13)
SET @str_Update=@str_Update+'CREATE PROCEDURE '+CHAR(32)+CHAR(32)+@procedure_Update+CHAR(32)+CHAR(32)+CHAR(13)+'('+CHAR(13)+CHAR(13)+CHAR(13)+CHAR(13)+CHAR(13)
SET @str_Delete=CHAR(13)+CHAR(13)++CHAR(13)+'if exists (select * from dbo.sysobjects where id = object_id(N'
SET @str_Delete=@str_Delete+CHAR(39)+@procedure_Delete+CHAR(39)+')'+CHAR(13)
SET @str_Delete=@str_Delete+' and OBJECTPROPERTY'+'(id, N'+CHAR(39)+'IsProcedure'+CHAR(39)+')'+'= 1)'+CHAR(13)+CHAR(13)+CHAR(13)+'BEGIN'+CHAR(13)+CHAR(13)+CHAR(13)
SET @str_Delete=@str_Delete+'DROP PROCEDURE '+CHAR(32)+CHAR(32)+@procedure_Delete+CHAR(13)+CHAR(13)+CHAR(13)+CHAR(13)+CHAR(13)+CHAR(13)+CHAR(13)+'END'+CHAR(13)+CHAR(13)+'GO'+CHAR(13)+CHAR(13)
SET @str_Delete=@str_Delete+'CREATE PROCEDURE '+CHAR(32)+CHAR(32)+@procedure_Delete+CHAR(32)+CHAR(32)+'('+CHAR(13)+CHAR(13)+CHAR(13)+CHAR(13)+CHAR(13)+CHAR(13)
SET @str_SelectAll=CHAR(13)+CHAR(13)++CHAR(13)+'if exists (select * from dbo.sysobjects where id = object_id(N'
SET @str_SelectAll=@str_SelectAll+CHAR(39)+@procedure_SelectAll+CHAR(39)+')'+CHAR(13)
SET @str_SelectAll=@str_SelectAll+' and OBJECTPROPERTY'+'(id, N'+CHAR(39)+'IsProcedure'+CHAR(39)+')'+'= 1)'+CHAR(13)+CHAR(13)+CHAR(13)+'BEGIN'+CHAR(13)+CHAR(13)+CHAR(13)
SET @str_SelectAll=@str_SelectAll+'DROP PROCEDURE '+CHAR(32)+CHAR(32)+@procedure_SelectAll+CHAR(13)+CHAR(13)+CHAR(13)+CHAR(13)+CHAR(13)+CHAR(13)+CHAR(13)+'END'+CHAR(13)+CHAR(13)+'GO'+CHAR(13)+CHAR(13)
SET @str_SelectAll=@str_SelectAll+'CREATE PROCEDURE '+CHAR(32)+CHAR(32)+@procedure_SelectAll+CHAR(32)+CHAR(32)+CHAR(13)+CHAR(13)+CHAR(13)+CHAR(13)+CHAR(13)
SET @str_SelectID=CHAR(13)+CHAR(13)++CHAR(13)+'if exists (select * from dbo.sysobjects where id = object_id(N'
SET @str_SelectID=@str_SelectID+CHAR(39)+@procedure_Select+CHAR(39)+')'+CHAR(13)
SET @str_SelectID=@str_SelectID+' and OBJECTPROPERTY'+'(id, N'+CHAR(39)+'IsProcedure'+CHAR(39)+')'+'= 1)'+CHAR(13)+CHAR(13)+CHAR(13)+'BEGIN'+CHAR(13)+CHAR(13)+CHAR(13)
SET @str_SelectID=@str_SelectID+'DROP PROCEDURE '+CHAR(32)+CHAR(32)+@procedure_Select+CHAR(13)+CHAR(13)+CHAR(13)+CHAR(13)+CHAR(13)+CHAR(13)+CHAR(13)+'END'+CHAR(13)+CHAR(13)+'GO'+CHAR(13)+CHAR(13)
SET @str_SelectID=@str_SelectID+'CREATE PROCEDURE '+CHAR(32)+CHAR(32)+@procedure_Select+CHAR(32)+CHAR(32)+'('+CHAR(13)+CHAR(13)+CHAR(13)+CHAR(13)+CHAR(13)+CHAR(13)
DECLARE @index_column int
SET @index_column=1
WHILE @@FETCH_STATUS=0
BEGIN
-----------------Generate Insert Procedure scripts
IF @columnisidentity<>1----Identity Column not execute insert
BEGIN
IF
