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

于 2009-06-06 15:34:00 发布
阅读量142 收藏
点赞数
文章标签： 数据库

网上流传的基本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
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值