網上流傳的基本SQL生成語句(Inset/Selec/Update/Delete)方法多了，這裡介紹一種，可以用於MSSQL 2000/2005/2008。
第一步：生成測試數據庫
/* ************自動生成存儲過程演示 **************** */
/* ************主要生成以下四個腳本 Delete/Select/Insert/Update **************** */
/* ************說明：僅用於MSSQL 2000/2005/2008 **************** */
/* ************tony 2009.06.06 Update **************** */
/* ************MSN:[email protected] **************** */
/* ************自動生成存儲過程演示 **************** */
/* ***********************創建測試數據庫[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
第二步：生成主要存儲過程
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
/* 生成一個Delete記錄的存儲過程************************* tony 2009.06.06 Update MSN:[email protected] @sTableName 表名 @bExecute 是否執行 默認0不執行 */
CREATE PROC CPP__SYS_MakeDeleteRecordProc
@sTableName varchar ( 128 ),
@bExecute bit = 0
AS
IF dbo.fnTableHasPrimaryKey( @sTableName ) = 0
BEGIN
RAISERROR ( ' Procedure cannot be created on a table with no primary key. ' , 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 + ' IF EXISTS(SELECT * FROM sysobjects WHERE name = '' AutoGenerateSys__ ' + @sTableName + ' _Delete '' ) ' + @sCRLF
SET @sProcText = @sProcText + @sTAB + ' DROP PROC AutoGenerateSys__ ' + @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 + ' -- Delete a single record from ' + @sTableName + @sCRLF
SET @sProcText = @sProcText + ' ---------------------------------------------------------------------------- ' + @sCRLF
SET @sProcText = @sProcText + ' CREATE PROC AutoGenerateSys__ ' + @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 ) -- character and binary
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記錄的存儲過程*************************
tony 2009.06.06 Update MSN:[email protected] @sTableName
表名 @bExecute 是否執行 默認0不執行 */
CREATE PROC CPP__SYS_MakeInsertRecordProc
@sTableName varchar ( 128 ),
@bExecute bit = 0
AS
IF dbo.fnTableHasPrimaryKey( @sTableName ) = 0
BEGIN
RAISERROR ( ' Procedure cannot be created on a table with no primary key. ' , 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 + ' IF EXISTS(SELECT * FROM sysobjects WHERE name = '' AutoGenerateSys__ ' + @sTableName + ' _Insert '' ) ' + @sCRLF
SET @sProcText = @sProcText + @sTAB + ' DROP PROC AutoGenerateSys__ ' + @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 + ' -- Insert a single record into ' + @sTableName + @sCRLF
SET @sProcText = @sProcText + ' ---------------------------------------------------------------------------- ' + @sCRLF
SET @sProcText = @sProcText + ' CREATE PROC AutoGenerateSys__ ' + @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 ) -- character and binary
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 + ' RETURN SCOPE_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記錄的存儲過程*************************
tony 2009.06.06 Update MSN:[email protected] @sTableName
表名 @bExecute 是否執行 默認0不執行 */
CREATE PROC CPP__SYS_MakeSelectRecordProc
@sTableName varchar ( 128 ),
@bExecute bit = 0
AS
IF dbo.fnTableHasPrimaryKey( @sTableName ) = 0
BEGIN
RAISERROR ( ' Procedure cannot be created on a table with no primary key. ' , 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 + ' IF EXISTS(SELECT * FROM sysobjects WHERE name = '' AutoGenerateSys__ ' + @sTableName + ' _Select '' ) ' + @sCRLF
SET @sProcText = @sProcText + @sTAB + ' DROP PROC AutoGenerateSys__ ' + @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 + ' -- Select a single record from ' + @sTableName + @sCRLF
SET @sProcText = @sProcText + ' ---------------------------------------------------------------------------- ' + @sCRLF
SET @sProcText = @sProcText + ' CREATE PROC AutoGenerateSys__ ' + @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 ) -- character and binary
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記錄的存儲過程*************************
tony 2009.06.06 Update MSN:[email protected] @sTableName
表名 @bExecute 是否執行 默認0不執行 */
CREATE PROC CPP__SYS_MakeUpdateRecordProc
@sTableName varchar ( 128 ),
@bExecute bit = 0
AS
IF dbo.fnTableHasPrimaryKey( @sTableName ) = 0
BEGIN
RAISERROR ( ' Procedure cannot be created on a table with no primary key. ' , 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 + ' IF EXISTS(SELECT * FROM sysobjects WHERE name = '' AutoGenerateSys__ ' + @sTableName + ' _Update '' ) ' + @sCRLF
SET @sProcText = @sProcText + @sTAB + ' DROP PROC AutoGenerateSys__ ' + @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 + ' -- Update a single record in ' + @sTableName + @sCRLF
SET @sProcText = @sProcText + ' ---------------------------------------------------------------------------- ' + @sCRLF
SET @sProcText = @sProcText + ' CREATE PROC AutoGenerateSys__ ' + @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 ) -- character and binary
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
/* 生成一些通用的Function *************************
tony 2009.06.06 Update MSN:[email protected] @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 sysindexkeys sik INNER JOIN syscolumns sc
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 syscolumns c INNER JOIN systypes t
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
第四步：生成測試數據表，並執行
/* ***********************創建測試數據表 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 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ CategoryID1 ] [ int ] NULL , [ CategoryID2 ] [ int ] NULL , [ CategoryID3 ] [ int ] NULL ,
[ P_SingleIntro ] [ nvarchar ] ( 1000 ) COLLATE Chinese_PRC_CI_AS NULL , [ P_Intro ] [ ntext ] COLLATE Chinese_PRC_CI_AS NULL ,
[ P_Order ] [ float ] NULL , [ P_TopTime ] [ smalldatetime ] NULL , [ P_BigImage ] [ nvarchar ] ( 150 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ P_SmallImage ] [ nvarchar ] ( 150 ) COLLATE Chinese_PRC_CI_AS NULL , [ CurState ] [ smallint ] NOT NULL ,
[ RecState ] [ smallint ] NOT NULL , [ P_CheckInfo ] [ nvarchar ] ( 80 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ P_L_ID ] [ int ] NOT NULL , [ P_NewKey1 ] [ nvarchar ] ( 300 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ AddTime ] [ datetime ] NOT NULL , [ AddUser ] [ nvarchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ ModTime ] [ datetime ] NOT NULL , [ ModUser ] [ nvarchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ F1 ] [ int ] NOT NULL , [ F3 ] [ nvarchar ] ( 50 ) COLLATE Chinese_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
