注册 登录
> SqlServer

根据表名自动生成I/S/U/D的SQL语句,用于MSSQL20

系统 2135 0

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

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


更多文章、技术交流、商务合作、联系博主

微信扫码或搜索：z360901061

微信扫一扫加我为好友

QQ号联系： 360901061

您的支持是博主写作最大的动力，如果您喜欢我的文章，感觉我的文章对您有帮助，请用微信扫描下面二维码支持博主2元、5元、10元、20元等您想捐的金额吧，狠狠点击下面给点支持吧，站长非常感激您！手机微信长按不能支付解决办法：请将微信支付二维码保存到相册，切换到微信，然后点击微信右上角扫一扫功能，选择支付二维码完成支付。

【本文对您有帮助就好】元

您的支持是博主写作最大的动力，如果您喜欢我的文章，感觉我的文章对您有帮助，请用微信扫描上面二维码支持博主2元、5元、10元、自定义金额等您想捐的金额吧，站长会非常 感谢您的哦！！！

发表我的评论
最新评论 总共0条评论