- 浏览: 676395 次
- 性别:
- 来自: 上海
最新评论
-
TheUniqueGirl:
Tomcat系统架构与设计模式:http://www.doci ...
Tomcat -
aykjy:
...
UML常见工具之NetBeans(downmoon) -
不老肖邦:
谢谢提供的简单示例
JAVA toString()
根据表名自动生成I/S/U/D的SQL语句,用于MSSQL2000/2005/2008(downmoon)
网上流传的基本SQL生成语句(Inset/Selec/Update/Delete)方法多了,这里介绍一种,可以用于MSSQL 2000/2005/2008。
第一步:生成测试数据库
SQL
<!-- <br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />-->/**/ /* ************自动生成存储过程演示**************** */
/**/ /* ************主要生成以下四个脚本 Delete/Select/Insert/Update**************** */
/**/ /* ************说明:仅用于MSSQL2000/2005/2008**************** */
/**/ /* ************tony2009.06.06Update**************** */
/**/ /* ************MSN:3w@live.cn**************** */
/**/ /* ************自动生成存储过程演示**************** */
/**/ /* ***********************创建测试数据库[TestProcedure]****************************** */
use master
go
IF EXISTS ( SELECT name FROM master.dbo.sysdatabases WHERE name = N ' TestProcedure ' )
DROP DATABASE [ TestProcedure ]
GO
create database [ TestProcedure ]
go
use [ TestProcedure ]
go
<!-- <br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />-->/**/ /* ************自动生成存储过程演示**************** */
/**/ /* ************主要生成以下四个脚本 Delete/Select/Insert/Update**************** */
/**/ /* ************说明:仅用于MSSQL2000/2005/2008**************** */
/**/ /* ************tony2009.06.06Update**************** */
/**/ /* ************MSN:3w@live.cn**************** */
/**/ /* ************自动生成存储过程演示**************** */
/**/ /* ***********************创建测试数据库[TestProcedure]****************************** */
use master
go
IF EXISTS ( SELECT name FROM master.dbo.sysdatabases WHERE name = N ' TestProcedure ' )
DROP DATABASE [ TestProcedure ]
GO
create database [ TestProcedure ]
go
use [ TestProcedure ]
go
第二步:生成主要存储过程
Code
<!-- <br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />-->SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/**/ /* 生成一个Delete记录的存储过程*************************
tony2009.06.06Update
MSN:3w@live.cn
@sTableName 表名
@bExecute是否执行 默认0不执行
*/
CREATE PROC CPP__SYS_MakeDeleteRecordProc
@sTableName varchar ( 128 ),
@bExecute bit = 0
AS
IF dbo.fnTableHasPrimaryKey( @sTableName ) = 0
BEGIN
RAISERROR ( ' Procedurecannotbecreatedonatablewithnoprimarykey. ' , 10 , 1 )
RETURN
END
DECLARE @sProcText varchar ( 8000 ),
@sKeyFields varchar ( 2000 ),
@sWhereClause varchar ( 2000 ),
@sColumnName varchar ( 128 ),
@nColumnID smallint ,
@bPrimaryKeyColumn bit ,
@nAlternateType int ,
@nColumnLength int ,
@nColumnPrecision int ,
@nColumnScale int ,
@IsNullable bit ,
@IsIdentity int ,
@sTypeName varchar ( 128 ),
@sDefaultValue varchar ( 4000 ),
@sCRLF char ( 2 ),
@sTAB char ( 1 )
SET @sTAB = char ( 9 )
SET @sCRLF = char ( 13 ) + char ( 10 )
SET @sProcText = ''
SET @sKeyFields = ''
SET @sWhereClause = ''
SET @sProcText = @sProcText + ' IFEXISTS(SELECT*FROMsysobjectsWHEREname= '' AutoGenerateSys__ ' + @sTableName + ' _Delete '' ) ' + @sCRLF
SET @sProcText = @sProcText + @sTAB + ' DROPPROCAutoGenerateSys__ ' + @sTableName + ' _Delete ' + @sCRLF
IF @bExecute = 0
SET @sProcText = @sProcText + ' GO ' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
PRINT @sProcText
IF @bExecute = 1
EXEC ( @sProcText )
SET @sProcText = ''
SET @sProcText = @sProcText + ' ---------------------------------------------------------------------------- ' + @sCRLF
SET @sProcText = @sProcText + ' --Deleteasinglerecordfrom ' + @sTableName + @sCRLF
SET @sProcText = @sProcText + ' ---------------------------------------------------------------------------- ' + @sCRLF
SET @sProcText = @sProcText + ' CREATEPROCAutoGenerateSys__ ' + @sTableName + ' _Delete ' + @sCRLF
DECLARE crKeyFields cursor for
SELECT *
FROM dbo.fnTableColumnInfo( @sTableName )
ORDER BY 2
OPEN crKeyFields
FETCH NEXT
FROM crKeyFields
INTO @sColumnName , @nColumnID , @bPrimaryKeyColumn , @nAlternateType ,
@nColumnLength , @nColumnPrecision , @nColumnScale , @IsNullable ,
@IsIdentity , @sTypeName , @sDefaultValue
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
IF ( @bPrimaryKeyColumn = 1 )
BEGIN
IF ( @sKeyFields <> '' )
SET @sKeyFields = @sKeyFields + ' , ' + @sCRLF
SET @sKeyFields = @sKeyFields + @sTAB + ' @ ' + @sColumnName + ' ' + @sTypeName
IF ( @nAlternateType = 2 ) -- decimal,numeric
SET @sKeyFields = @sKeyFields + ' ( ' + CAST ( @nColumnPrecision AS varchar ( 3 )) + ' , '
+ CAST ( @nColumnScale AS varchar ( 3 )) + ' ) '
ELSE IF ( @nAlternateType = 1 ) -- characterandbinary
SET @sKeyFields = @sKeyFields + ' ( ' + CAST ( @nColumnLength AS varchar ( 4 )) + ' ) '
IF ( @sWhereClause = '' )
SET @sWhereClause = @sWhereClause + ' WHERE '
ELSE
SET @sWhereClause = @sWhereClause + ' AND '
SET @sWhereClause = @sWhereClause + @sTAB + @sColumnName + ' =@ ' + @sColumnName + @sCRLF
END
FETCH NEXT
FROM crKeyFields
INTO @sColumnName , @nColumnID , @bPrimaryKeyColumn , @nAlternateType ,
@nColumnLength , @nColumnPrecision , @nColumnScale , @IsNullable ,
@IsIdentity , @sTypeName , @sDefaultValue
END
CLOSE crKeyFields
DEALLOCATE crKeyFields
SET @sProcText = @sProcText + @sKeyFields + @sCRLF
SET @sProcText = @sProcText + ' AS ' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
SET @sProcText = @sProcText + ' DELETE ' + @sTableName + @sCRLF
SET @sProcText = @sProcText + @sWhereClause
SET @sProcText = @sProcText + @sCRLF
IF @bExecute = 0
SET @sProcText = @sProcText + ' GO ' + @sCRLF
PRINT @sProcText
IF @bExecute = 1
EXEC ( @sProcText )
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/**/ /* 生成一个Insert记录的存储过程*************************
tony2009.06.06Update
MSN:3w@live.cn
@sTableName 表名
@bExecute是否执行 默认0不执行
*/
CREATE PROC CPP__SYS_MakeInsertRecordProc
@sTableName varchar ( 128 ),
@bExecute bit = 0
AS
IF dbo.fnTableHasPrimaryKey( @sTableName ) = 0
BEGIN
RAISERROR ( ' Procedurecannotbecreatedonatablewithnoprimarykey. ' , 10 , 1 )
RETURN
END
DECLARE @sProcText varchar ( 8000 ),
@sKeyFields varchar ( 2000 ),
@sAllFields varchar ( 2000 ),
@sAllParams varchar ( 2000 ),
@sWhereClause varchar ( 2000 ),
@sColumnName varchar ( 128 ),
@nColumnID smallint ,
@bPrimaryKeyColumn bit ,
@nAlternateType int ,
@nColumnLength int ,
@nColumnPrecision int ,
@nColumnScale int ,
@IsNullable bit ,
@IsIdentity int ,
@HasIdentity int ,
@sTypeName varchar ( 128 ),
@sDefaultValue varchar ( 4000 ),
@sCRLF char ( 2 ),
@sTAB char ( 1 )
SET @HasIdentity = 0
SET @sTAB = char ( 9 )
SET @sCRLF = char ( 13 ) + char ( 10 )
SET @sProcText = ''
SET @sKeyFields = ''
SET @sAllFields = ''
SET @sWhereClause = ''
SET @sAllParams = ''
SET @sProcText = @sProcText + ' IFEXISTS(SELECT*FROMsysobjectsWHEREname= '' AutoGenerateSys__ ' + @sTableName + ' _Insert '' ) ' + @sCRLF
SET @sProcText = @sProcText + @sTAB + ' DROPPROCAutoGenerateSys__ ' + @sTableName + ' _Insert ' + @sCRLF
IF @bExecute = 0
SET @sProcText = @sProcText + ' GO ' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
PRINT @sProcText
IF @bExecute = 1
EXEC ( @sProcText )
SET @sProcText = ''
SET @sProcText = @sProcText + ' ---------------------------------------------------------------------------- ' + @sCRLF
SET @sProcText = @sProcText + ' --Insertasinglerecordinto ' + @sTableName + @sCRLF
SET @sProcText = @sProcText + ' ---------------------------------------------------------------------------- ' + @sCRLF
SET @sProcText = @sProcText + ' CREATEPROCAutoGenerateSys__ ' + @sTableName + ' _Insert ' + @sCRLF
DECLARE crKeyFields cursor for
SELECT *
FROM dbo.fnTableColumnInfo( @sTableName )
ORDER BY 2
OPEN crKeyFields
FETCH NEXT
FROM crKeyFields
INTO @sColumnName , @nColumnID , @bPrimaryKeyColumn , @nAlternateType ,
@nColumnLength , @nColumnPrecision , @nColumnScale , @IsNullable ,
@IsIdentity , @sTypeName , @sDefaultValue
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
IF ( @IsIdentity = 0 )
BEGIN
IF ( @sKeyFields <> '' )
SET @sKeyFields = @sKeyFields + ' , ' + @sCRLF
SET @sKeyFields = @sKeyFields + @sTAB + ' @ ' + @sColumnName + ' ' + @sTypeName
IF ( @sAllFields <> '' )
BEGIN
SET @sAllParams = @sAllParams + ' , '
SET @sAllFields = @sAllFields + ' , '
END
IF ( @sTypeName = ' timestamp ' )
SET @sAllParams = @sAllParams + ' NULL '
ELSE IF ( @sDefaultValue IS NOT NULL )
SET @sAllParams = @sAllParams + ' COALESCE(@ ' + @sColumnName + ' , ' + @sDefaultValue + ' ) '
ELSE
SET @sAllParams = @sAllParams + ' @ ' + @sColumnName
SET @sAllFields = @sAllFields + @sColumnName
END
ELSE
BEGIN
SET @HasIdentity = 1
END
IF ( @nAlternateType = 2 ) -- decimal,numeric
SET @sKeyFields = @sKeyFields + ' ( ' + CAST ( @nColumnPrecision AS varchar ( 3 )) + ' , '
+ CAST ( @nColumnScale AS varchar ( 3 )) + ' ) '
ELSE IF ( @nAlternateType = 1 ) -- characterandbinary
SET @sKeyFields = @sKeyFields + ' ( ' + CAST ( @nColumnLength AS varchar ( 4 )) + ' ) '
IF ( @IsIdentity = 0 )
BEGIN
IF ( @sDefaultValue IS NOT NULL ) OR ( @IsNullable = 1 ) OR ( @sTypeName = ' timestamp ' )
SET @sKeyFields = @sKeyFields + ' =NULL '
END
FETCH NEXT
FROM crKeyFields
INTO @sColumnName , @nColumnID , @bPrimaryKeyColumn , @nAlternateType ,
@nColumnLength , @nColumnPrecision , @nColumnScale , @IsNullable ,
@IsIdentity , @sTypeName , @sDefaultValue
END
CLOSE crKeyFields
DEALLOCATE crKeyFields
SET @sProcText = @sProcText + @sKeyFields + @sCRLF
SET @sProcText = @sProcText + ' AS ' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
SET @sProcText = @sProcText + ' INSERT ' + @sTableName + ' ( ' + @sAllFields + ' ) ' + @sCRLF
SET @sProcText = @sProcText + ' VALUES( ' + @sAllParams + ' ) ' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
IF ( @HasIdentity = 1 )
BEGIN
SET @sProcText = @sProcText + ' RETURNSCOPE_IDENTITY() ' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
END
IF @bExecute = 0
SET @sProcText = @sProcText + ' GO ' + @sCRLF
PRINT @sProcText
IF @bExecute = 1
EXEC ( @sProcText )
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/**/ /* 生成一个Select记录的存储过程*************************
tony2009.06.06Update
MSN:3w@live.cn
@sTableName 表名
@bExecute是否执行 默认0不执行
*/
CREATE PROC CPP__SYS_MakeSelectRecordProc
@sTableName varchar ( 128 ),
@bExecute bit = 0
AS
IF dbo.fnTableHasPrimaryKey( @sTableName ) = 0
BEGIN
RAISERROR ( ' Procedurecannotbecreatedonatablewithnoprimarykey. ' , 10 , 1 )
RETURN
END
DECLARE @sProcText varchar ( 8000 ),
@sKeyFields varchar ( 2000 ),
@sSelectClause varchar ( 2000 ),
@sWhereClause varchar ( 2000 ),
@sColumnName varchar ( 128 ),
@nColumnID smallint ,
@bPrimaryKeyColumn bit ,
@nAlternateType int ,
@nColumnLength int ,
@nColumnPrecision int ,
@nColumnScale int ,
@IsNullable bit ,
@IsIdentity int ,
@sTypeName varchar ( 128 ),
@sDefaultValue varchar ( 4000 ),
@sCRLF char ( 2 ),
@sTAB char ( 1 )
SET @sTAB = char ( 9 )
SET @sCRLF = char ( 13 ) + char ( 10 )
SET @sProcText = ''
SET @sKeyFields = ''
SET @sSelectClause = ''
SET @sWhereClause = ''
SET @sProcText = @sProcText + ' IFEXISTS(SELECT*FROMsysobjectsWHEREname= '' AutoGenerateSys__ ' + @sTableName + ' _Select '' ) ' + @sCRLF
SET @sProcText = @sProcText + @sTAB + ' DROPPROCAutoGenerateSys__ ' + @sTableName + ' _Select ' + @sCRLF
IF @bExecute = 0
SET @sProcText = @sProcText + ' GO ' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
PRINT @sProcText
IF @bExecute = 1
EXEC ( @sProcText )
SET @sProcText = ''
SET @sProcText = @sProcText + ' ---------------------------------------------------------------------------- ' + @sCRLF
SET @sProcText = @sProcText + ' --Selectasinglerecordfrom ' + @sTableName + @sCRLF
SET @sProcText = @sProcText + ' ---------------------------------------------------------------------------- ' + @sCRLF
SET @sProcText = @sProcText + ' CREATEPROCAutoGenerateSys__ ' + @sTableName + ' _Select ' + @sCRLF
DECLARE crKeyFields cursor for
SELECT *
FROM dbo.fnTableColumnInfo( @sTableName )
ORDER BY 2
OPEN crKeyFields
FETCH NEXT
FROM crKeyFields
INTO @sColumnName , @nColumnID , @bPrimaryKeyColumn , @nAlternateType ,
@nColumnLength , @nColumnPrecision , @nColumnScale , @IsNullable ,
@IsIdentity , @sTypeName , @sDefaultValue
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
IF ( @bPrimaryKeyColumn = 1 )
BEGIN
IF ( @sKeyFields <> '' )
SET @sKeyFields = @sKeyFields + ' , ' + @sCRLF
SET @sKeyFields = @sKeyFields + @sTAB + ' @ ' + @sColumnName + ' ' + @sTypeName
IF ( @nAlternateType = 2 ) -- decimal,numeric
SET @sKeyFields = @sKeyFields + ' ( ' + CAST ( @nColumnPrecision AS varchar ( 3 )) + ' , '
+ CAST ( @nColumnScale AS varchar ( 3 )) + ' ) '
ELSE IF ( @nAlternateType = 1 ) -- characterandbinary
SET @sKeyFields = @sKeyFields + ' ( ' + CAST ( @nColumnLength AS varchar ( 4 )) + ' ) '
IF ( @sWhereClause = '' )
SET @sWhereClause = @sWhereClause + ' WHERE '
ELSE
SET @sWhereClause = @sWhereClause + ' AND '
SET @sWhereClause = @sWhereClause + @sTAB + @sColumnName + ' =@ ' + @sColumnName + @sCRLF
END
IF ( @sSelectClause = '' )
SET @sSelectClause = @sSelectClause + ' SELECT '
ELSE
SET @sSelectClause = @sSelectClause + ' , ' + @sCRLF
SET @sSelectClause = @sSelectClause + @sTAB + @sColumnName
FETCH NEXT
FROM crKeyFields
INTO @sColumnName , @nColumnID , @bPrimaryKeyColumn , @nAlternateType ,
@nColumnLength , @nColumnPrecision , @nColumnScale , @IsNullable ,
@IsIdentity , @sTypeName , @sDefaultValue
END
CLOSE crKeyFields
DEALLOCATE crKeyFields
SET @sSelectClause = @sSelectClause + @sCRLF
SET @sProcText = @sProcText + @sKeyFields + @sCRLF
SET @sProcText = @sProcText + ' AS ' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
SET @sProcText = @sProcText + @sSelectClause
SET @sProcText = @sProcText + ' FROM ' + @sTableName + @sCRLF
SET @sProcText = @sProcText + @sWhereClause
SET @sProcText = @sProcText + @sCRLF
IF @bExecute = 0
SET @sProcText = @sProcText + ' GO ' + @sCRLF
PRINT @sProcText
IF @bExecute = 1
EXEC ( @sProcText )
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/**/ /* 生成一个Update记录的存储过程*************************
tony2009.06.06Update
MSN:3w@live.cn
@sTableName 表名
@bExecute是否执行 默认0不执行
*/
CREATE PROC CPP__SYS_MakeUpdateRecordProc
@sTableName varchar ( 128 ),
@bExecute bit = 0
AS
IF dbo.fnTableHasPrimaryKey( @sTableName ) = 0
BEGIN
RAISERROR ( ' Procedurecannotbecreatedonatablewithnoprimarykey. ' , 10 , 1 )
RETURN
END
DECLARE @sProcText varchar ( 8000 ),
@sKeyFields varchar ( 2000 ),
@sSetClause varchar ( 2000 ),
@sWhereClause varchar ( 2000 ),
@sColumnName varchar ( 128 ),
@nColumnID smallint ,
@bPrimaryKeyColumn bit ,
@nAlternateType int ,
@nColumnLength int ,
@nColumnPrecision int ,
@nColumnScale int ,
@IsNullable bit ,
@IsIdentity int ,
@sTypeName varchar ( 128 ),
@sDefaultValue varchar ( 4000 ),
@sCRLF char ( 2 ),
@sTAB char ( 1 )
SET @sTAB = char ( 9 )
SET @sCRLF = char ( 13 ) + char ( 10 )
SET @sProcText = ''
SET @sKeyFields = ''
SET @sSetClause = ''
SET @sWhereClause = ''
SET @sProcText = @sProcText + ' IFEXISTS(SELECT*FROMsysobjectsWHEREname= '' AutoGenerateSys__ ' + @sTableName + ' _Update '' ) ' + @sCRLF
SET @sProcText = @sProcText + @sTAB + ' DROPPROCAutoGenerateSys__ ' + @sTableName + ' _Update ' + @sCRLF
IF @bExecute = 0
SET @sProcText = @sProcText + ' GO ' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
PRINT @sProcText
IF @bExecute = 1
EXEC ( @sProcText )
SET @sProcText = ''
SET @sProcText = @sProcText + ' ---------------------------------------------------------------------------- ' + @sCRLF
SET @sProcText = @sProcText + ' --Updateasinglerecordin ' + @sTableName + @sCRLF
SET @sProcText = @sProcText + ' ---------------------------------------------------------------------------- ' + @sCRLF
SET @sProcText = @sProcText + ' CREATEPROCAutoGenerateSys__ ' + @sTableName + ' _Update ' + @sCRLF
DECLARE crKeyFields cursor for
SELECT *
FROM dbo.fnTableColumnInfo( @sTableName )
ORDER BY 2
OPEN crKeyFields
FETCH NEXT
FROM crKeyFields
INTO @sColumnName , @nColumnID , @bPrimaryKeyColumn , @nAlternateType ,
@nColumnLength , @nColumnPrecision , @nColumnScale , @IsNullable ,
@IsIdentity , @sTypeName , @sDefaultValue
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
IF ( @sKeyFields <> '' )
SET @sKeyFields = @sKeyFields + ' , ' + @sCRLF
SET @sKeyFields = @sKeyFields + @sTAB + ' @ ' + @sColumnName + ' ' + @sTypeName
IF ( @nAlternateType = 2 ) -- decimal,numeric
SET @sKeyFields = @sKeyFields + ' ( ' + CAST ( @nColumnPrecision AS varchar ( 3 )) + ' , '
+ CAST ( @nColumnScale AS varchar ( 3 )) + ' ) '
ELSE IF ( @nAlternateType = 1 ) -- characterandbinary
SET @sKeyFields = @sKeyFields + ' ( ' + CAST ( @nColumnLength AS varchar ( 4 )) + ' ) '
IF ( @bPrimaryKeyColumn = 1 )
BEGIN
IF ( @sWhereClause = '' )
SET @sWhereClause = @sWhereClause + ' WHERE '
ELSE
SET @sWhereClause = @sWhereClause + ' AND '
SET @sWhereClause = @sWhereClause + @sTAB + @sColumnName + ' =@ ' + @sColumnName + @sCRLF
END
ELSE
IF ( @IsIdentity = 0 )
BEGIN
IF ( @sSetClause = '' )
SET @sSetClause = @sSetClause + ' SET '
ELSE
SET @sSetClause = @sSetClause + ' , ' + @sCRLF
SET @sSetClause = @sSetClause + @sTAB + @sColumnName + ' = '
IF ( @sTypeName = ' timestamp ' )
SET @sSetClause = @sSetClause + ' NULL '
ELSE IF ( @sDefaultValue IS NOT NULL )
SET @sSetClause = @sSetClause + ' COALESCE(@ ' + @sColumnName + ' , ' + @sDefaultValue + ' ) '
ELSE
SET @sSetClause = @sSetClause + ' @ ' + @sColumnName
END
IF ( @IsIdentity = 0 )
BEGIN
IF ( @IsNullable = 1 ) OR ( @sTypeName = ' timestamp ' )
SET @sKeyFields = @sKeyFields + ' =NULL '
END
FETCH NEXT
FROM crKeyFields
INTO @sColumnName , @nColumnID , @bPrimaryKeyColumn , @nAlternateType ,
@nColumnLength , @nColumnPrecision , @nColumnScale , @IsNullable ,
@IsIdentity , @sTypeName , @sDefaultValue
END
CLOSE crKeyFields
DEALLOCATE crKeyFields
SET @sSetClause = @sSetClause + @sCRLF
SET @sProcText = @sProcText + @sKeyFields + @sCRLF
SET @sProcText = @sProcText + ' AS ' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
SET @sProcText = @sProcText + ' UPDATE ' + @sTableName + @sCRLF
SET @sProcText = @sProcText + @sSetClause
SET @sProcText = @sProcText + @sWhereClause
SET @sProcText = @sProcText + @sCRLF
IF @bExecute = 0
SET @sProcText = @sProcText + ' GO ' + @sCRLF
PRINT @sProcText
IF @bExecute = 1
EXEC ( @sProcText )
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
<!-- <br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />-->SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/**/ /* 生成一个Delete记录的存储过程*************************
tony2009.06.06Update
MSN:3w@live.cn
@sTableName 表名
@bExecute是否执行 默认0不执行
*/
CREATE PROC CPP__SYS_MakeDeleteRecordProc
@sTableName varchar ( 128 ),
@bExecute bit = 0
AS
IF dbo.fnTableHasPrimaryKey( @sTableName ) = 0
BEGIN
RAISERROR ( ' Procedurecannotbecreatedonatablewithnoprimarykey. ' , 10 , 1 )
RETURN
END
DECLARE @sProcText varchar ( 8000 ),
@sKeyFields varchar ( 2000 ),
@sWhereClause varchar ( 2000 ),
@sColumnName varchar ( 128 ),
@nColumnID smallint ,
@bPrimaryKeyColumn bit ,
@nAlternateType int ,
@nColumnLength int ,
@nColumnPrecision int ,
@nColumnScale int ,
@IsNullable bit ,
@IsIdentity int ,
@sTypeName varchar ( 128 ),
@sDefaultValue varchar ( 4000 ),
@sCRLF char ( 2 ),
@sTAB char ( 1 )
SET @sTAB = char ( 9 )
SET @sCRLF = char ( 13 ) + char ( 10 )
SET @sProcText = ''
SET @sKeyFields = ''
SET @sWhereClause = ''
SET @sProcText = @sProcText + ' IFEXISTS(SELECT*FROMsysobjectsWHEREname= '' AutoGenerateSys__ ' + @sTableName + ' _Delete '' ) ' + @sCRLF
SET @sProcText = @sProcText + @sTAB + ' DROPPROCAutoGenerateSys__ ' + @sTableName + ' _Delete ' + @sCRLF
IF @bExecute = 0
SET @sProcText = @sProcText + ' GO ' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
PRINT @sProcText
IF @bExecute = 1
EXEC ( @sProcText )
SET @sProcText = ''
SET @sProcText = @sProcText + ' ---------------------------------------------------------------------------- ' + @sCRLF
SET @sProcText = @sProcText + ' --Deleteasinglerecordfrom ' + @sTableName + @sCRLF
SET @sProcText = @sProcText + ' ---------------------------------------------------------------------------- ' + @sCRLF
SET @sProcText = @sProcText + ' CREATEPROCAutoGenerateSys__ ' + @sTableName + ' _Delete ' + @sCRLF
DECLARE crKeyFields cursor for
SELECT *
FROM dbo.fnTableColumnInfo( @sTableName )
ORDER BY 2
OPEN crKeyFields
FETCH NEXT
FROM crKeyFields
INTO @sColumnName , @nColumnID , @bPrimaryKeyColumn , @nAlternateType ,
@nColumnLength , @nColumnPrecision , @nColumnScale , @IsNullable ,
@IsIdentity , @sTypeName , @sDefaultValue
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
IF ( @bPrimaryKeyColumn = 1 )
BEGIN
IF ( @sKeyFields <> '' )
SET @sKeyFields = @sKeyFields + ' , ' + @sCRLF
SET @sKeyFields = @sKeyFields + @sTAB + ' @ ' + @sColumnName + ' ' + @sTypeName
IF ( @nAlternateType = 2 ) -- decimal,numeric
SET @sKeyFields = @sKeyFields + ' ( ' + CAST ( @nColumnPrecision AS varchar ( 3 )) + ' , '
+ CAST ( @nColumnScale AS varchar ( 3 )) + ' ) '
ELSE IF ( @nAlternateType = 1 ) -- characterandbinary
SET @sKeyFields = @sKeyFields + ' ( ' + CAST ( @nColumnLength AS varchar ( 4 )) + ' ) '
IF ( @sWhereClause = '' )
SET @sWhereClause = @sWhereClause + ' WHERE '
ELSE
SET @sWhereClause = @sWhereClause + ' AND '
SET @sWhereClause = @sWhereClause + @sTAB + @sColumnName + ' =@ ' + @sColumnName + @sCRLF
END
FETCH NEXT
FROM crKeyFields
INTO @sColumnName , @nColumnID , @bPrimaryKeyColumn , @nAlternateType ,
@nColumnLength , @nColumnPrecision , @nColumnScale , @IsNullable ,
@IsIdentity , @sTypeName , @sDefaultValue
END
CLOSE crKeyFields
DEALLOCATE crKeyFields
SET @sProcText = @sProcText + @sKeyFields + @sCRLF
SET @sProcText = @sProcText + ' AS ' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
SET @sProcText = @sProcText + ' DELETE ' + @sTableName + @sCRLF
SET @sProcText = @sProcText + @sWhereClause
SET @sProcText = @sProcText + @sCRLF
IF @bExecute = 0
SET @sProcText = @sProcText + ' GO ' + @sCRLF
PRINT @sProcText
IF @bExecute = 1
EXEC ( @sProcText )
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/**/ /* 生成一个Insert记录的存储过程*************************
tony2009.06.06Update
MSN:3w@live.cn
@sTableName 表名
@bExecute是否执行 默认0不执行
*/
CREATE PROC CPP__SYS_MakeInsertRecordProc
@sTableName varchar ( 128 ),
@bExecute bit = 0
AS
IF dbo.fnTableHasPrimaryKey( @sTableName ) = 0
BEGIN
RAISERROR ( ' Procedurecannotbecreatedonatablewithnoprimarykey. ' , 10 , 1 )
RETURN
END
DECLARE @sProcText varchar ( 8000 ),
@sKeyFields varchar ( 2000 ),
@sAllFields varchar ( 2000 ),
@sAllParams varchar ( 2000 ),
@sWhereClause varchar ( 2000 ),
@sColumnName varchar ( 128 ),
@nColumnID smallint ,
@bPrimaryKeyColumn bit ,
@nAlternateType int ,
@nColumnLength int ,
@nColumnPrecision int ,
@nColumnScale int ,
@IsNullable bit ,
@IsIdentity int ,
@HasIdentity int ,
@sTypeName varchar ( 128 ),
@sDefaultValue varchar ( 4000 ),
@sCRLF char ( 2 ),
@sTAB char ( 1 )
SET @HasIdentity = 0
SET @sTAB = char ( 9 )
SET @sCRLF = char ( 13 ) + char ( 10 )
SET @sProcText = ''
SET @sKeyFields = ''
SET @sAllFields = ''
SET @sWhereClause = ''
SET @sAllParams = ''
SET @sProcText = @sProcText + ' IFEXISTS(SELECT*FROMsysobjectsWHEREname= '' AutoGenerateSys__ ' + @sTableName + ' _Insert '' ) ' + @sCRLF
SET @sProcText = @sProcText + @sTAB + ' DROPPROCAutoGenerateSys__ ' + @sTableName + ' _Insert ' + @sCRLF
IF @bExecute = 0
SET @sProcText = @sProcText + ' GO ' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
PRINT @sProcText
IF @bExecute = 1
EXEC ( @sProcText )
SET @sProcText = ''
SET @sProcText = @sProcText + ' ---------------------------------------------------------------------------- ' + @sCRLF
SET @sProcText = @sProcText + ' --Insertasinglerecordinto ' + @sTableName + @sCRLF
SET @sProcText = @sProcText + ' ---------------------------------------------------------------------------- ' + @sCRLF
SET @sProcText = @sProcText + ' CREATEPROCAutoGenerateSys__ ' + @sTableName + ' _Insert ' + @sCRLF
DECLARE crKeyFields cursor for
SELECT *
FROM dbo.fnTableColumnInfo( @sTableName )
ORDER BY 2
OPEN crKeyFields
FETCH NEXT
FROM crKeyFields
INTO @sColumnName , @nColumnID , @bPrimaryKeyColumn , @nAlternateType ,
@nColumnLength , @nColumnPrecision , @nColumnScale , @IsNullable ,
@IsIdentity , @sTypeName , @sDefaultValue
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
IF ( @IsIdentity = 0 )
BEGIN
IF ( @sKeyFields <> '' )
SET @sKeyFields = @sKeyFields + ' , ' + @sCRLF
SET @sKeyFields = @sKeyFields + @sTAB + ' @ ' + @sColumnName + ' ' + @sTypeName
IF ( @sAllFields <> '' )
BEGIN
SET @sAllParams = @sAllParams + ' , '
SET @sAllFields = @sAllFields + ' , '
END
IF ( @sTypeName = ' timestamp ' )
SET @sAllParams = @sAllParams + ' NULL '
ELSE IF ( @sDefaultValue IS NOT NULL )
SET @sAllParams = @sAllParams + ' COALESCE(@ ' + @sColumnName + ' , ' + @sDefaultValue + ' ) '
ELSE
SET @sAllParams = @sAllParams + ' @ ' + @sColumnName
SET @sAllFields = @sAllFields + @sColumnName
END
ELSE
BEGIN
SET @HasIdentity = 1
END
IF ( @nAlternateType = 2 ) -- decimal,numeric
SET @sKeyFields = @sKeyFields + ' ( ' + CAST ( @nColumnPrecision AS varchar ( 3 )) + ' , '
+ CAST ( @nColumnScale AS varchar ( 3 )) + ' ) '
ELSE IF ( @nAlternateType = 1 ) -- characterandbinary
SET @sKeyFields = @sKeyFields + ' ( ' + CAST ( @nColumnLength AS varchar ( 4 )) + ' ) '
IF ( @IsIdentity = 0 )
BEGIN
IF ( @sDefaultValue IS NOT NULL ) OR ( @IsNullable = 1 ) OR ( @sTypeName = ' timestamp ' )
SET @sKeyFields = @sKeyFields + ' =NULL '
END
FETCH NEXT
FROM crKeyFields
INTO @sColumnName , @nColumnID , @bPrimaryKeyColumn , @nAlternateType ,
@nColumnLength , @nColumnPrecision , @nColumnScale , @IsNullable ,
@IsIdentity , @sTypeName , @sDefaultValue
END
CLOSE crKeyFields
DEALLOCATE crKeyFields
SET @sProcText = @sProcText + @sKeyFields + @sCRLF
SET @sProcText = @sProcText + ' AS ' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
SET @sProcText = @sProcText + ' INSERT ' + @sTableName + ' ( ' + @sAllFields + ' ) ' + @sCRLF
SET @sProcText = @sProcText + ' VALUES( ' + @sAllParams + ' ) ' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
IF ( @HasIdentity = 1 )
BEGIN
SET @sProcText = @sProcText + ' RETURNSCOPE_IDENTITY() ' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
END
IF @bExecute = 0
SET @sProcText = @sProcText + ' GO ' + @sCRLF
PRINT @sProcText
IF @bExecute = 1
EXEC ( @sProcText )
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/**/ /* 生成一个Select记录的存储过程*************************
tony2009.06.06Update
MSN:3w@live.cn
@sTableName 表名
@bExecute是否执行 默认0不执行
*/
CREATE PROC CPP__SYS_MakeSelectRecordProc
@sTableName varchar ( 128 ),
@bExecute bit = 0
AS
IF dbo.fnTableHasPrimaryKey( @sTableName ) = 0
BEGIN
RAISERROR ( ' Procedurecannotbecreatedonatablewithnoprimarykey. ' , 10 , 1 )
RETURN
END
DECLARE @sProcText varchar ( 8000 ),
@sKeyFields varchar ( 2000 ),
@sSelectClause varchar ( 2000 ),
@sWhereClause varchar ( 2000 ),
@sColumnName varchar ( 128 ),
@nColumnID smallint ,
@bPrimaryKeyColumn bit ,
@nAlternateType int ,
@nColumnLength int ,
@nColumnPrecision int ,
@nColumnScale int ,
@IsNullable bit ,
@IsIdentity int ,
@sTypeName varchar ( 128 ),
@sDefaultValue varchar ( 4000 ),
@sCRLF char ( 2 ),
@sTAB char ( 1 )
SET @sTAB = char ( 9 )
SET @sCRLF = char ( 13 ) + char ( 10 )
SET @sProcText = ''
SET @sKeyFields = ''
SET @sSelectClause = ''
SET @sWhereClause = ''
SET @sProcText = @sProcText + ' IFEXISTS(SELECT*FROMsysobjectsWHEREname= '' AutoGenerateSys__ ' + @sTableName + ' _Select '' ) ' + @sCRLF
SET @sProcText = @sProcText + @sTAB + ' DROPPROCAutoGenerateSys__ ' + @sTableName + ' _Select ' + @sCRLF
IF @bExecute = 0
SET @sProcText = @sProcText + ' GO ' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
PRINT @sProcText
IF @bExecute = 1
EXEC ( @sProcText )
SET @sProcText = ''
SET @sProcText = @sProcText + ' ---------------------------------------------------------------------------- ' + @sCRLF
SET @sProcText = @sProcText + ' --Selectasinglerecordfrom ' + @sTableName + @sCRLF
SET @sProcText = @sProcText + ' ---------------------------------------------------------------------------- ' + @sCRLF
SET @sProcText = @sProcText + ' CREATEPROCAutoGenerateSys__ ' + @sTableName + ' _Select ' + @sCRLF
DECLARE crKeyFields cursor for
SELECT *
FROM dbo.fnTableColumnInfo( @sTableName )
ORDER BY 2
OPEN crKeyFields
FETCH NEXT
FROM crKeyFields
INTO @sColumnName , @nColumnID , @bPrimaryKeyColumn , @nAlternateType ,
@nColumnLength , @nColumnPrecision , @nColumnScale , @IsNullable ,
@IsIdentity , @sTypeName , @sDefaultValue
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
IF ( @bPrimaryKeyColumn = 1 )
BEGIN
IF ( @sKeyFields <> '' )
SET @sKeyFields = @sKeyFields + ' , ' + @sCRLF
SET @sKeyFields = @sKeyFields + @sTAB + ' @ ' + @sColumnName + ' ' + @sTypeName
IF ( @nAlternateType = 2 ) -- decimal,numeric
SET @sKeyFields = @sKeyFields + ' ( ' + CAST ( @nColumnPrecision AS varchar ( 3 )) + ' , '
+ CAST ( @nColumnScale AS varchar ( 3 )) + ' ) '
ELSE IF ( @nAlternateType = 1 ) -- characterandbinary
SET @sKeyFields = @sKeyFields + ' ( ' + CAST ( @nColumnLength AS varchar ( 4 )) + ' ) '
IF ( @sWhereClause = '' )
SET @sWhereClause = @sWhereClause + ' WHERE '
ELSE
SET @sWhereClause = @sWhereClause + ' AND '
SET @sWhereClause = @sWhereClause + @sTAB + @sColumnName + ' =@ ' + @sColumnName + @sCRLF
END
IF ( @sSelectClause = '' )
SET @sSelectClause = @sSelectClause + ' SELECT '
ELSE
SET @sSelectClause = @sSelectClause + ' , ' + @sCRLF
SET @sSelectClause = @sSelectClause + @sTAB + @sColumnName
FETCH NEXT
FROM crKeyFields
INTO @sColumnName , @nColumnID , @bPrimaryKeyColumn , @nAlternateType ,
@nColumnLength , @nColumnPrecision , @nColumnScale , @IsNullable ,
@IsIdentity , @sTypeName , @sDefaultValue
END
CLOSE crKeyFields
DEALLOCATE crKeyFields
SET @sSelectClause = @sSelectClause + @sCRLF
SET @sProcText = @sProcText + @sKeyFields + @sCRLF
SET @sProcText = @sProcText + ' AS ' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
SET @sProcText = @sProcText + @sSelectClause
SET @sProcText = @sProcText + ' FROM ' + @sTableName + @sCRLF
SET @sProcText = @sProcText + @sWhereClause
SET @sProcText = @sProcText + @sCRLF
IF @bExecute = 0
SET @sProcText = @sProcText + ' GO ' + @sCRLF
PRINT @sProcText
IF @bExecute = 1
EXEC ( @sProcText )
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/**/ /* 生成一个Update记录的存储过程*************************
tony2009.06.06Update
MSN:3w@live.cn
@sTableName 表名
@bExecute是否执行 默认0不执行
*/
CREATE PROC CPP__SYS_MakeUpdateRecordProc
@sTableName varchar ( 128 ),
@bExecute bit = 0
AS
IF dbo.fnTableHasPrimaryKey( @sTableName ) = 0
BEGIN
RAISERROR ( ' Procedurecannotbecreatedonatablewithnoprimarykey. ' , 10 , 1 )
RETURN
END
DECLARE @sProcText varchar ( 8000 ),
@sKeyFields varchar ( 2000 ),
@sSetClause varchar ( 2000 ),
@sWhereClause varchar ( 2000 ),
@sColumnName varchar ( 128 ),
@nColumnID smallint ,
@bPrimaryKeyColumn bit ,
@nAlternateType int ,
@nColumnLength int ,
@nColumnPrecision int ,
@nColumnScale int ,
@IsNullable bit ,
@IsIdentity int ,
@sTypeName varchar ( 128 ),
@sDefaultValue varchar ( 4000 ),
@sCRLF char ( 2 ),
@sTAB char ( 1 )
SET @sTAB = char ( 9 )
SET @sCRLF = char ( 13 ) + char ( 10 )
SET @sProcText = ''
SET @sKeyFields = ''
SET @sSetClause = ''
SET @sWhereClause = ''
SET @sProcText = @sProcText + ' IFEXISTS(SELECT*FROMsysobjectsWHEREname= '' AutoGenerateSys__ ' + @sTableName + ' _Update '' ) ' + @sCRLF
SET @sProcText = @sProcText + @sTAB + ' DROPPROCAutoGenerateSys__ ' + @sTableName + ' _Update ' + @sCRLF
IF @bExecute = 0
SET @sProcText = @sProcText + ' GO ' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
PRINT @sProcText
IF @bExecute = 1
EXEC ( @sProcText )
SET @sProcText = ''
SET @sProcText = @sProcText + ' ---------------------------------------------------------------------------- ' + @sCRLF
SET @sProcText = @sProcText + ' --Updateasinglerecordin ' + @sTableName + @sCRLF
SET @sProcText = @sProcText + ' ---------------------------------------------------------------------------- ' + @sCRLF
SET @sProcText = @sProcText + ' CREATEPROCAutoGenerateSys__ ' + @sTableName + ' _Update ' + @sCRLF
DECLARE crKeyFields cursor for
SELECT *
FROM dbo.fnTableColumnInfo( @sTableName )
ORDER BY 2
OPEN crKeyFields
FETCH NEXT
FROM crKeyFields
INTO @sColumnName , @nColumnID , @bPrimaryKeyColumn , @nAlternateType ,
@nColumnLength , @nColumnPrecision , @nColumnScale , @IsNullable ,
@IsIdentity , @sTypeName , @sDefaultValue
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
IF ( @sKeyFields <> '' )
SET @sKeyFields = @sKeyFields + ' , ' + @sCRLF
SET @sKeyFields = @sKeyFields + @sTAB + ' @ ' + @sColumnName + ' ' + @sTypeName
IF ( @nAlternateType = 2 ) -- decimal,numeric
SET @sKeyFields = @sKeyFields + ' ( ' + CAST ( @nColumnPrecision AS varchar ( 3 )) + ' , '
+ CAST ( @nColumnScale AS varchar ( 3 )) + ' ) '
ELSE IF ( @nAlternateType = 1 ) -- characterandbinary
SET @sKeyFields = @sKeyFields + ' ( ' + CAST ( @nColumnLength AS varchar ( 4 )) + ' ) '
IF ( @bPrimaryKeyColumn = 1 )
BEGIN
IF ( @sWhereClause = '' )
SET @sWhereClause = @sWhereClause + ' WHERE '
ELSE
SET @sWhereClause = @sWhereClause + ' AND '
SET @sWhereClause = @sWhereClause + @sTAB + @sColumnName + ' =@ ' + @sColumnName + @sCRLF
END
ELSE
IF ( @IsIdentity = 0 )
BEGIN
IF ( @sSetClause = '' )
SET @sSetClause = @sSetClause + ' SET '
ELSE
SET @sSetClause = @sSetClause + ' , ' + @sCRLF
SET @sSetClause = @sSetClause + @sTAB + @sColumnName + ' = '
IF ( @sTypeName = ' timestamp ' )
SET @sSetClause = @sSetClause + ' NULL '
ELSE IF ( @sDefaultValue IS NOT NULL )
SET @sSetClause = @sSetClause + ' COALESCE(@ ' + @sColumnName + ' , ' + @sDefaultValue + ' ) '
ELSE
SET @sSetClause = @sSetClause + ' @ ' + @sColumnName
END
IF ( @IsIdentity = 0 )
BEGIN
IF ( @IsNullable = 1 ) OR ( @sTypeName = ' timestamp ' )
SET @sKeyFields = @sKeyFields + ' =NULL '
END
FETCH NEXT
FROM crKeyFields
INTO @sColumnName , @nColumnID , @bPrimaryKeyColumn , @nAlternateType ,
@nColumnLength , @nColumnPrecision , @nColumnScale , @IsNullable ,
@IsIdentity , @sTypeName , @sDefaultValue
END
CLOSE crKeyFields
DEALLOCATE crKeyFields
SET @sSetClause = @sSetClause + @sCRLF
SET @sProcText = @sProcText + @sKeyFields + @sCRLF
SET @sProcText = @sProcText + ' AS ' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
SET @sProcText = @sProcText + ' UPDATE ' + @sTableName + @sCRLF
SET @sProcText = @sProcText + @sSetClause
SET @sProcText = @sProcText + @sWhereClause
SET @sProcText = @sProcText + @sCRLF
IF @bExecute = 0
SET @sProcText = @sProcText + ' GO ' + @sCRLF
PRINT @sProcText
IF @bExecute = 1
EXEC ( @sProcText )
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
第三步:生成一些必须的Function
Code
<!-- <br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />-->/**/ /* 生成一些通用的Function*************************
tony2009.06.06Update
MSN:3w@live.cn
@sTableName 表名
@bExecute是否执行 默认0不执行
*/
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.fnCleanDefaultValue( @sDefaultValue varchar ( 4000 ))
RETURNS varchar ( 4000 )
AS
BEGIN
RETURN SubString ( @sDefaultValue , 2 , DataLength ( @sDefaultValue ) - 2 )
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.fnColumnDefault( @sTableName varchar ( 128 ), @sColumnName varchar ( 128 ))
RETURNS varchar ( 4000 )
AS
BEGIN
DECLARE @sDefaultValue varchar ( 4000 )
SELECT @sDefaultValue = dbo.fnCleanDefaultValue(COLUMN_DEFAULT)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @sTableName
AND COLUMN_NAME = @sColumnName
RETURN @sDefaultValue
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.fnIsColumnPrimaryKey( @sTableName varchar ( 128 ), @nColumnName varchar ( 128 ))
RETURNS bit
AS
BEGIN
DECLARE @nTableID int ,
@nIndexID int ,
@i int
SET @nTableID = OBJECT_ID ( @sTableName )
SELECT @nIndexID = indid
FROM sysindexes
WHERE id = @nTableID
AND indid BETWEEN 1 And 254
AND (status & 2048 ) = 2048
IF @nIndexID Is Null
RETURN 0
IF @nColumnName IN
( SELECT sc. [ name ]
FROM sysindexkeyssik
INNER JOIN syscolumnssc ON sik.id = sc.id AND sik.colid = sc.colid
WHERE sik.id = @nTableID
AND sik.indid = @nIndexID )
BEGIN
RETURN 1
END
RETURN 0
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.fnTableColumnInfo( @sTableName varchar ( 128 ))
RETURNS TABLE
AS
RETURN
SELECT c.name AS sColumnName,
c.colid AS nColumnID,
dbo.fnIsColumnPrimaryKey( @sTableName ,c.name) AS bPrimaryKeyColumn,
CASE WHEN t.name IN ( ' char ' , ' varchar ' , ' binary ' , ' varbinary ' , ' nchar ' , ' nvarchar ' ) THEN 1
WHEN t.name IN ( ' decimal ' , ' numeric ' ) THEN 2
ELSE 0
END AS nAlternateType,
c.length AS nColumnLength,
c.prec AS nColumnPrecision,
c.scale AS nColumnScale,
c.IsNullable,
SIGN (c.status & 128 ) AS IsIdentity,
t.name as sTypeName,
dbo.fnColumnDefault( @sTableName ,c.name) AS sDefaultValue
FROM syscolumnsc
INNER JOIN systypest ON c.xtype = t.xtype and c.usertype = t.usertype
WHERE c.id = OBJECT_ID ( @sTableName )
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.fnTableHasPrimaryKey( @sTableName varchar ( 128 ))
RETURNS bit
AS
BEGIN
DECLARE @nTableID int ,
@nIndexID int
SET @nTableID = OBJECT_ID ( @sTableName )
SELECT @nIndexID = indid
FROM sysindexes
WHERE id = @nTableID
AND indid BETWEEN 1 And 254
AND (status & 2048 ) = 2048
IF @nIndexID IS NOT Null
RETURN 1
RETURN 0
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
<!-- <br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />-->/**/ /* 生成一些通用的Function*************************
tony2009.06.06Update
MSN:3w@live.cn
@sTableName 表名
@bExecute是否执行 默认0不执行
*/
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.fnCleanDefaultValue( @sDefaultValue varchar ( 4000 ))
RETURNS varchar ( 4000 )
AS
BEGIN
RETURN SubString ( @sDefaultValue , 2 , DataLength ( @sDefaultValue ) - 2 )
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.fnColumnDefault( @sTableName varchar ( 128 ), @sColumnName varchar ( 128 ))
RETURNS varchar ( 4000 )
AS
BEGIN
DECLARE @sDefaultValue varchar ( 4000 )
SELECT @sDefaultValue = dbo.fnCleanDefaultValue(COLUMN_DEFAULT)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @sTableName
AND COLUMN_NAME = @sColumnName
RETURN @sDefaultValue
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.fnIsColumnPrimaryKey( @sTableName varchar ( 128 ), @nColumnName varchar ( 128 ))
RETURNS bit
AS
BEGIN
DECLARE @nTableID int ,
@nIndexID int ,
@i int
SET @nTableID = OBJECT_ID ( @sTableName )
SELECT @nIndexID = indid
FROM sysindexes
WHERE id = @nTableID
AND indid BETWEEN 1 And 254
AND (status & 2048 ) = 2048
IF @nIndexID Is Null
RETURN 0
IF @nColumnName IN
( SELECT sc. [ name ]
FROM sysindexkeyssik
INNER JOIN syscolumnssc ON sik.id = sc.id AND sik.colid = sc.colid
WHERE sik.id = @nTableID
AND sik.indid = @nIndexID )
BEGIN
RETURN 1
END
RETURN 0
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.fnTableColumnInfo( @sTableName varchar ( 128 ))
RETURNS TABLE
AS
RETURN
SELECT c.name AS sColumnName,
c.colid AS nColumnID,
dbo.fnIsColumnPrimaryKey( @sTableName ,c.name) AS bPrimaryKeyColumn,
CASE WHEN t.name IN ( ' char ' , ' varchar ' , ' binary ' , ' varbinary ' , ' nchar ' , ' nvarchar ' ) THEN 1
WHEN t.name IN ( ' decimal ' , ' numeric ' ) THEN 2
ELSE 0
END AS nAlternateType,
c.length AS nColumnLength,
c.prec AS nColumnPrecision,
c.scale AS nColumnScale,
c.IsNullable,
SIGN (c.status & 128 ) AS IsIdentity,
t.name as sTypeName,
dbo.fnColumnDefault( @sTableName ,c.name) AS sDefaultValue
FROM syscolumnsc
INNER JOIN systypest ON c.xtype = t.xtype and c.usertype = t.usertype
WHERE c.id = OBJECT_ID ( @sTableName )
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.fnTableHasPrimaryKey( @sTableName varchar ( 128 ))
RETURNS bit
AS
BEGIN
DECLARE @nTableID int ,
@nIndexID int
SET @nTableID = OBJECT_ID ( @sTableName )
SELECT @nIndexID = indid
FROM sysindexes
WHERE id = @nTableID
AND indid BETWEEN 1 And 254
AND (status & 2048 ) = 2048
IF @nIndexID IS NOT Null
RETURN 1
RETURN 0
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
第四步:生成测试数据表,并执行
Code
<!-- <br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />-->
/**/ /* ***********************创建测试数据表Product****************************** */
if not exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[Product] ' ) and OBJECTPROPERTY (id,N ' IsUserTable ' ) = 1 )
BEGIN
CREATE TABLE [ Product ] (
[ P_ID ] [ bigint ] NOT NULL ,
[ P_Name ] [ nvarchar ] ( 255 )COLLATEChinese_PRC_CI_AS NULL ,
[ CategoryID1 ] [ int ] NULL ,
[ CategoryID2 ] [ int ] NULL ,
[ CategoryID3 ] [ int ] NULL ,
[ P_SingleIntro ] [ nvarchar ] ( 1000 )COLLATEChinese_PRC_CI_AS NULL ,
[ P_Intro ] [ ntext ] COLLATEChinese_PRC_CI_AS NULL ,
[ P_Order ] [ float ] NULL ,
[ P_TopTime ] [ smalldatetime ] NULL ,
[ P_BigImage ] [ nvarchar ] ( 150 )COLLATEChinese_PRC_CI_AS NULL ,
[ P_SmallImage ] [ nvarchar ] ( 150 )COLLATEChinese_PRC_CI_AS NULL ,
[ CurState ] [ smallint ] NOT NULL ,
[ RecState ] [ smallint ] NOT NULL ,
[ P_CheckInfo ] [ nvarchar ] ( 80 )COLLATEChinese_PRC_CI_AS NULL ,
[ P_L_ID ] [ int ] NOT NULL ,
[ P_NewKey1 ] [ nvarchar ] ( 300 )COLLATEChinese_PRC_CI_AS NULL ,
[ AddTime ] [ datetime ] NOT NULL ,
[ AddUser ] [ nvarchar ] ( 50 )COLLATEChinese_PRC_CI_AS NULL ,
[ ModTime ] [ datetime ] NOT NULL ,
[ ModUser ] [ nvarchar ] ( 50 )COLLATEChinese_PRC_CI_AS NULL ,
[ F1 ] [ int ] NOT NULL ,
[ F3 ] [ nvarchar ] ( 50 )COLLATEChinese_PRC_CI_AS NULL ,
CONSTRAINT [ PK_Product ] PRIMARY KEY CLUSTERED
(
[ P_ID ]
) ON [ PRIMARY ]
) ON [ PRIMARY ] TEXTIMAGE_ON [ PRIMARY ]
END
/**/ /* *******测试生成********** */
-- CPP__SYS_MakeDeleteRecordProc'Product',0
-- go
-- CPP__SYS_MakeInsertRecordProc'Product',0
-- go
-- CPP__SYS_MakeSelectRecordProc'Product',0
-- go
-- CPP__SYS_MakeUpdateRecordProc'Product',0
-- go
/**/ /* *******测试生成********** */
CPP__SYS_MakeDeleteRecordProc ' Product ' , 1
go
CPP__SYS_MakeInsertRecordProc ' Product ' , 1
go
CPP__SYS_MakeSelectRecordProc ' Product ' , 1
go
CPP__SYS_MakeUpdateRecordProc ' Product ' , 1
go
<!-- <br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />-->
/**/ /* ***********************创建测试数据表Product****************************** */
if not exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[Product] ' ) and OBJECTPROPERTY (id,N ' IsUserTable ' ) = 1 )
BEGIN
CREATE TABLE [ Product ] (
[ P_ID ] [ bigint ] NOT NULL ,
[ P_Name ] [ nvarchar ] ( 255 )COLLATEChinese_PRC_CI_AS NULL ,
[ CategoryID1 ] [ int ] NULL ,
[ CategoryID2 ] [ int ] NULL ,
[ CategoryID3 ] [ int ] NULL ,
[ P_SingleIntro ] [ nvarchar ] ( 1000 )COLLATEChinese_PRC_CI_AS NULL ,
[ P_Intro ] [ ntext ] COLLATEChinese_PRC_CI_AS NULL ,
[ P_Order ] [ float ] NULL ,
[ P_TopTime ] [ smalldatetime ] NULL ,
[ P_BigImage ] [ nvarchar ] ( 150 )COLLATEChinese_PRC_CI_AS NULL ,
[ P_SmallImage ] [ nvarchar ] ( 150 )COLLATEChinese_PRC_CI_AS NULL ,
[ CurState ] [ smallint ] NOT NULL ,
[ RecState ] [ smallint ] NOT NULL ,
[ P_CheckInfo ] [ nvarchar ] ( 80 )COLLATEChinese_PRC_CI_AS NULL ,
[ P_L_ID ] [ int ] NOT NULL ,
[ P_NewKey1 ] [ nvarchar ] ( 300 )COLLATEChinese_PRC_CI_AS NULL ,
[ AddTime ] [ datetime ] NOT NULL ,
[ AddUser ] [ nvarchar ] ( 50 )COLLATEChinese_PRC_CI_AS NULL ,
[ ModTime ] [ datetime ] NOT NULL ,
[ ModUser ] [ nvarchar ] ( 50 )COLLATEChinese_PRC_CI_AS NULL ,
[ F1 ] [ int ] NOT NULL ,
[ F3 ] [ nvarchar ] ( 50 )COLLATEChinese_PRC_CI_AS NULL ,
CONSTRAINT [ PK_Product ] PRIMARY KEY CLUSTERED
(
[ P_ID ]
) ON [ PRIMARY ]
) ON [ PRIMARY ] TEXTIMAGE_ON [ PRIMARY ]
END
/**/ /* *******测试生成********** */
-- CPP__SYS_MakeDeleteRecordProc'Product',0
-- go
-- CPP__SYS_MakeInsertRecordProc'Product',0
-- go
-- CPP__SYS_MakeSelectRecordProc'Product',0
-- go
-- CPP__SYS_MakeUpdateRecordProc'Product',0
-- go
/**/ /* *******测试生成********** */
CPP__SYS_MakeDeleteRecordProc ' Product ' , 1
go
CPP__SYS_MakeInsertRecordProc ' Product ' , 1
go
CPP__SYS_MakeSelectRecordProc ' Product ' , 1
go
CPP__SYS_MakeUpdateRecordProc ' Product ' , 1
go
效果如图:
相关推荐
一个相当独立的.通用分页控件c#源码(downmoon收集) 采用工厂模式,提供标准化接口!支持 DataGrid 及 Repeater分页使用方法:新建两个解决方案 一为...http://blog.csdn.net/downmoon/archive/2006/04/12/660279.aspx
由于微软在vs2005 vsts团队开发版中集成了sql 2005 express,而在SQL 2005中又集成了vs2005 Express,,所以当很多用户同时安装vs2005团队开发?:?sql 2005企业版(downmoon)时,就会出现数据库连接失败的case
在某种程度上,有了它,我们再也不用为不能全面及时检测众多浏览器和五花八门的js/css不兼容性而痛不欲生了。...http://blog.csdn.net/downmoon/archive/2010/10/17/5946465.aspx 欢迎联系邀月 3w@live.cn
获取远程网页的内容之一(downmoon原创) 一、本机直接上网时: 获取指定远程网页内容#region 获取指定远程网页内容 代码如下: /**//// /// 获取指定远程网页内容 /// </summary> /// <param u00a0name=...
获取远程网页的内容之一(downmoon原创) 一、本机直接上网时: 获取指定远程网页内容#region 获取指定远程网页内容 代码如下: /**//// /// 获取指定远程网页内容 /// </summary> /// <param u00a0name=”...
此源码有问题,请下载downmoon提供的修正版!非常抱歉! http://download.csdn.net/source/2434932
《Microsoft SQL Server 2008 Analysis Services Step by Step》随书数据库,由邀月(3w@live.cn)整理上传,本数据库只限学习、研究之用,不得用于商业用途,否则后果自负。
Winform下的分页控件。3w@live.cn。详细说明,请看文章:http://blog.csdn.net/downmoon/archive/2011/02/10/6177224.aspx
上周完成了一个报表小项目,使用开源组件NPOI作为主要组件。之所以采用第三方的开源组件而不使用COM或微软提供的API,原因就不多... 在此分享NPOI的一个应用,利用Excel模板生成excel文件。这正是NPOI强于Myxls之处。
sqlserver 存储过程中的top+变量使用分析(downmoon) ,需要的朋友可以参考下。
近来有许多人问起SQL在线管理的问题,遂将以前用过的一个开源SQL 在线管理工具修改了一下,并分享。附源码下载。
获取远程网页的内容之二(downmoon原创)
在IE中调用javascript打开Excel的代码(downmoon原作)
asp.net下获取远程网页的内容之二(downmoon原创)
在使用vs.php+ sourcesafe管理代码时,发现有时候并不能从vs.net IDE中正确获取phpproj文件下的所有文件。但从sourcesafe IDE下却可以。