SQL:StoredProcedure:MyTable2SQL

出自ProgWiki

用途

• 產生與指定的Table相關的SQL字串。

程式碼

--物件:  StoredProcedure [dbo].[MyTable2SQL]
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE [dbo].[MyTable2SQL](
	@strTableName AS NVARCHAR(100) = null
)
AS
BEGIN
	DECLARE @ColName AS NVARCHAR(255)
	DECLARE @DataType1 AS NVARCHAR(255)
	DECLARE @DataType2 AS NVARCHAR(255)
	DECLARE @IsKey AS NVARCHAR(1)
	DECLARE @IsAbleNull AS NVARCHAR(1)
	DECLARE @IsIdentity AS NVARCHAR(1)
	DECLARE @DefaultVaule AS NVARCHAR(MAX)
 
	DECLARE @sql_cols AS NVARCHAR(MAX)
	DECLARE @sql_vals AS NVARCHAR(MAX)
	DECLARE @sql_cols_and_vals AS NVARCHAR(MAX)
	DECLARE @sql_keycols_and_vals AS NVARCHAR(MAX)
	DECLARE @sql_cols_and_type AS NVARCHAR(MAX)
 
	DECLARE @sql_select_count AS NVARCHAR(MAX)
	DECLARE @sql_select AS NVARCHAR(MAX)
	DECLARE @sql_insert AS NVARCHAR(MAX)
	DECLARE @sql_update AS NVARCHAR(MAX)
	DECLARE @sql_delete AS NVARCHAR(MAX)
	DECLARE @sql_edit AS NVARCHAR(MAX)
 
	SET @sql_cols = ''
	SET @sql_vals = ''
	SET @sql_cols_and_vals = ''
	SET @sql_keycols_and_vals =''
	SET @sql_cols_and_type = ''
 
	DECLARE db_cur CURSOR FOR
 
	SELECT
 		ColName=a.name
 		,DataType1=b.name
		,DataType2=CASE
			WHEN (b.name in ('nchar', 'nvarchar')) THEN
				'(' + CASE WHEN (a.length = -1) THEN 'max' ELSE LTRIM(STR(a.length / 2)) END + ')'
			WHEN (b.name in ('binary','char', 'varchar')) THEN
				'(' + CASE WHEN (a.length = -1) THEN 'max' ELSE LTRIM(STR(a.length)) END + ')'
			WHEN (b.name in ('decimal','numeric')) THEN
				'('+CONVERT(VARCHAR(10),a.xprec)+','+CONVERT(VARCHAR(10),a.xscale)+')'
			ELSE 
				''
			END
 
 		,IsKey=	CASE WHEN a.name IN (
			SELECT 機碼=d.name 
				FROM Syscolumns d
					INNER JOIN sysobjects e ON d.Id=e.Id AND e.Xtype='U' AND e.Name<>'Dtproperties'
				WHERE EXISTS(
					SELECT 1 FROM sysobjects WHERE Xtype='Pk' AND name IN (
						SELECT name FROM sysindexes WHERE Indid IN (
							SELECT Indid FROM sysindexkeys WHERE Id = d.Id AND Colid=d.Colid
						)
					)
				)
				AND e.name=c.name
			)
			THEN 'Y' ELSE '' END
 
		,IsAbleNull=
			CASE WHEN a.isnullable IN ( 1 )
				THEN 'Y'
				ELSE 'N'
			END
 
		--判斷欄位是否自動增值
		,IsIdentity =
			CASE WHEN a.colstat IN ( 1 )
				THEN 'Y'
				ELSE 'N'
			END
 
		,DefaultVaule=
			CASE WHEN (a.cdefault <> 0) THEN 
				(SELECT d.TEXT FROM [syscomments] d WHERE d.id = a.cdefault)
			ELSE
				''
			END
 
	FROM
		[syscolumns] a,
		[systypes] b,
		[sysobjects] c 
	WHERE
		a.id=c.id
		AND a.xtype=b.xtype 
		AND c.type='U'
		AND b.name<>'sysname'
		AND c.name=@strTableName
	ORDER BY a.colid       
 
	-- 
 
	--欄位處理回圈
	OPEN db_cur
	FETCH NEXT FROM db_cur INTO @ColName,@DataType1,@DataType2,@IsKey,@IsAbleNull,@IsIdentity,@DefaultVaule
 
	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF (@sql_cols = '')
			SET @sql_cols = '['+@ColName+']'
		ELSE
			SET @sql_cols = @sql_cols + CHAR(13)+',['+@ColName+']'
 
 		IF (@sql_vals = '')
			SET @sql_vals = '@'+@ColName
		ELSE
			SET @sql_vals = @sql_vals + CHAR(13)+',@'+@ColName
 
		IF (@IsKey = 'Y')
			BEGIN
				IF (@sql_keycols_and_vals = '')
					SET @sql_keycols_and_vals = '['+@ColName+']=@'+@ColName
				ELSE
					SET @sql_keycols_and_vals = @sql_keycols_and_vals + CHAR(13) +'and ['+@ColName+']=@'+@ColName
			END
 
		ELSE
			BEGIN
				IF (@sql_cols_and_vals = '')
					SET @sql_cols_and_vals = '['+@ColName+']=@'+@ColName
				ELSE
					SET @sql_cols_and_vals = @sql_cols_and_vals + CHAR(13) +',['+@ColName+']=@'+@ColName
			END
 
		IF (@sql_cols_and_type ='')
			SET @sql_cols_and_type = '@' + @ColName + ' as ' + @DataType1 + @DataType2
		ELSE
			SET @sql_cols_and_type = @sql_cols_and_type + CHAR(13) + ',@' + @ColName + ' as ' + @DataType1 + @DataType2
 
		FETCH NEXT FROM db_cur INTO @ColName,@DataType1,@DataType2,@IsKey,@IsAbleNull,@IsIdentity,@DefaultVaule
	END
	CLOSE db_cur
	DEALLOCATE db_cur
 
	--基本4型(select, insert, update, delete)
	SET @sql_select = 'SELECT '+ @sql_cols + CHAR(13)
					 + 'FROM [' + @strTableName + ']'+CHAR(13) + 'WHERE '+@sql_keycols_and_vals 
 
	SET @sql_insert = 'INSERT INTO ['+@strTableName+'] (' + CHAR(13)
					+ @sql_cols + CHAR(13) + ') VALUES (' +CHAR(13) + @sql_vals + CHAR(13)+ ')'
 
	SET @sql_update = 'UPDATE ['+@strTableName+'] SET '+CHAR(13)+@sql_cols_and_vals +CHAR(13)
					 + 'WHERE ' +@sql_keycols_and_vals 
 
	SET @sql_delete = 'DELETE FROM ['+@strTableName+']'+CHAR(13)
					 + 'WHERE ' +@sql_keycols_and_vals 
 
 
	--進階處理(預存程序相關)
 
	SET @sql_select_count = 'SELECT @nCount=count(*) FROM ' +@strTableName +' where '+@sql_keycols_and_vals 
	SET @sql_edit = 'SET ANSI_NULLS ON'+CHAR(13)+'GO'+CHAR(13)+'SET QUOTED_IDENTIFIER ON'+CHAR(13)+'GO'+CHAR(13)
					+'CREATE PROC [dbo].['+@strTableName+'_edit]('+CHAR(13)
					+@sql_cols_and_type+CHAR(13)+')'+CHAR(13)+'AS'+CHAR(13)
					+'BEGIN'+CHAR(13)
					+'declare @nCount AS int;'+CHAR(13)+@sql_select_count+CHAR(13)
					+'IF (@nCount=0)'+CHAR(13)+'BEGIN'+CHAR(13)+@sql_insert+CHAR(13)+'END'+CHAR(13)
					+'ELSE'+CHAR(13)
					+'BEGIN'+CHAR(13)+@sql_update+CHAR(13)+'END'+CHAR(13)
					+'END'
 
--print '@sql_cols='+char(13)+ @sql_cols + char(13) + char(13)
--print '@sql_vals='+char(13)+ @sql_vals + char(13) + char(13)
--print '@sql_cols_and_vals='+char(13)+ @sql_cols_and_vals + char(13) + char(13)
--print '@sql_keycols_and_vals='+char(13)+ @sql_keycols_and_vals + char(13) + char(13)
--print '@sql_cols_and_type='+char(13)+@sql_cols_and_type + char(13) + char(13)
 
DECLARE @outLine AS NVARCHAR(MAX)
SET @outLine = CHAR(13)+'--------------------------------------------'+CHAR(13)
 
PRINT '* Select用'+@outLine+@sql_select + CHAR(13) + CHAR(13)
PRINT '* Insert用'+@outLine+@sql_insert + CHAR(13) + CHAR(13)
PRINT '* Update用'+@outLine+@sql_update + CHAR(13) + CHAR(13)
PRINT '* Delete用'+@outLine+@sql_delete + CHAR(13) + CHAR(13)
 
PRINT '* SP-Edit用'+@outLine+@sql_edit + CHAR(13) + CHAR(13)
 
END