根据表名自动生成I/S/U/D的SQL语句,用于MSSQL2000/2005/2008(downmoon)

最新推荐文章于 2024-04-29 23:47:34 发布
最新推荐文章于 2024-04-29 23:47:34 发布
阅读量76 收藏
点赞数
文章标签： 数据库

网上流传的基本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


第二步：生成主要存储过程


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


第三步：生成一些必须的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


第四步：生成测试数据表，并执行

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


效果如图：



附下载SQL

iteye_20474
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
根据表名自动生成数据的INSERT语句的存储过程.sql
12-05
根据表名自动生成数据的INSERT语句的存储过程.sql
delphi 建表处理工具
Listest的博客
11-12 200
unit UcreateTableTool; interface uses Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, Dialogs, cxControls, cxLookAndFeels, cxLookAndFeelPainters, cxContainer, cxEdit, dxSkinsCore, dxSkinsDefaultPainters, cxTextEdit, cxMe.
SQL server获取建表sql语句 并创建结构相同的数据库表
Zzu_zzx的博客
09-15 1553
【代码】SQL server获取建表sql语句 并创建结构相同的数据库表。
mysql中怎么根据表生成sql语句_通过sql语句生成MySQl数据字典 | 学步园
weixin_29303701的博客
01-20 794
通过一天的学习，下载了好多的数据字典生成工具都用不成，最后只得通过sql语句来实现了，现在分享出来，希望大家多多指点。通过下面sql语句生成的数据字典包含主键、外键、索引。select distinct a.TABLE_SCHEMA as '数据库' ,a.TABLE_NAME as '表名',a.COLUMN_NAME as '字段名',a.COLUMN_TYPE as '类型长度',case ...
SQL-根据数据库表名自动生成表中字段工具
01-11
可以根据输入的表名自动生成表中的字段,并以insert into 和UPDATE 两种方式展现出来.
同时安装vs2005团队开发版和sql 2005企业版的方法(downmoon原作)
09-11
由于微软在vs2005 vsts团队开发版中集成了sql 2005 express，而在SQL 2005中又集成了vs2005 Express,，所以当很多用户同时安装vs2005团队开发?：?sql 2005企业版(downmoon)时，就会出现数据库连接失败的case
asp.net省市三级联动的DropDownList+Ajax的三种框架(aspnet/Jquery/ExtJs)示例
01-02
本文主要列举了省市三级联动的DropDownList+Ajax的三种框架(aspnet/Jquery/ExtJs)示例。前段时间需要作一个的Web前端应用，需要用多个框架，一个典型的应用场景... namespace Downmoon.Framework.Model { #region Popul
sqlserver 存储过程中的top+变量使用分析(downmoon)
01-19
存储过程中的TOP后跟一个变量会如何？ 代码如下: Create proc getWorkPlan2 (@intCounter int ,@lngUserID int) as select Top 5 lngWorkID,strWorkName,strExecHumanName,strBeginDate from worklist where ...
分享一个改进版的仿MSN/QQ弹出窗口(C#)
04-25
右下角弹出窗口,完全能够自定义弹出窗口的样式、内容、链接，该控件支持Firefox, IE 和 Opera ，其他浏览器没有测试。
根据表名自动生成INSERT,UPDATE,DELETE,SELECT的SQL语句
#一起看斜阳#的专栏
01-22 1745
TBPROCCREATE Procedure TbProc@model varchar(2),@pagename varchar(32),@object varchar(32),@autoField varchar(32)=nullasset nocount onselect @model=upper(@model)select @pagename=lower(@pagenam
MS SqlServer 生成 insert 语句
ritaleo的专栏
01-31 410
执行下面的存储过程     create proc proc_insert (@tablename varchar(256))    as  begin   set nocount on     declare @sqlstr varchar(4000)     declare @sqlstr1 varchar(4000)     declare @sqlstr2 varchar(4000...
sql语句中为表创建一个别名
热门推荐
每天进步一点点
11-20 2万+
有时候，在对数据库中的表进行操作的时候，发现表名比较冗长，这时候我们就需要对表创建一个别名，别名的关键字为as(也可以不加) 。    现在有一个student表，结构如下: 现在我认为student太长了  我不想一直打。 sql语句如下  select  a.Sno,a.Sname,a.Sage from student as a (as可以去掉)  结果如下:
【课堂练习】
最新发布
JacksonLeo的博客
04-29 280
虽然现代JVM的垃圾回收机制已经非常高效，但是在性能敏感的场合，仍然需要注意对象的创建频率。例如，在SysLogininforMapper中的deleteLogininforByIds方法，如果处理大量ID时，应考虑使用批处理技术，而不是循环单条处理。例如，在SysMenuMapper中的查询方法，如果涉及到复杂的查询条件或者大量数据的查询，应确保SQL语句的优化和索引的使用。异常处理：代码中应该有更明确的异常处理逻辑，比如使用try-catch块捕获可能的异常，并进行适当的处理，如记录日志、回滚事务等。
探秘MySQL主从复制的多种实现方式
todoitbo的博客
04-26 1031
本文将深入探讨MySQL主从复制的多种实现方式，包括基于语句、基于行和混合模式等。无论您是初学者还是有经验的数据库管理员，都能通过本文全面了解MySQL主从复制技术的多样化选择，从而提高数据库的可用性和性能。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值