-------------------------------------------------------------------------------------------------------------------------------
使用方式
-------------------------------------------------------------------------------------------------------------------------------
/*
PL_setTempTable 'XX'
DROP TABLE #TEMP_XX
*/
-------------------------------------------------------------------------------------------------------------------------------
以下為預存程序
-------------------------------------------------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[PL_setTempTable]
@TABLE_NAME NVARCHAR(MAX)
AS
BEGIN
DECLARE @TX NVARCHAR(MAX) = '##TEMP_' + @TABLE_NAME;
DECLARE
@T NVARCHAR(MAX) = 'CREATE TABLE ' + @TX + ' ( ',
@COLUMN_NAME NVARCHAR(MAX) = '',
@DATA_TYPE NVARCHAR(MAX) = '',
@LENGTH NVARCHAR(MAX) = '',
@COUNT INT = 0;
IF OBJECT_ID('TEMPDB..' + @TX) IS NOT NULL BEGIN
EXEC ('DROP TABLE ' + @TX)
END
DECLARE MYCURSOR CURSOR FOR
SELECT
A.COLUMN_NAME,
UPPER(DATA_TYPE) DATA_TYPE,
ISNULL(ISNULL(A.CHARACTER_MAXIMUM_LENGTH,A.NUMERIC_PRECISION),0) [LENGTH]
FROM INFORMATION_SCHEMA.COLUMNS A
LEFT JOIN
(
SELECT
Col.COLUMN_NAME,Col.Table_Name
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab,
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col
WHERE Col.Constraint_Name = Tab.Constraint_Name
AND Col.Table_Name = Tab.Table_Name
AND Constraint_Type = 'PRIMARY KEY'
) B ON A.COLUMN_NAME = B.COLUMN_NAME AND A.Table_Name = B.Table_Name
WHERE A.TABLE_NAME = @TABLE_NAME
-- 開啟游標
OPEN MYCURSOR
FETCH NEXT FROM MYCURSOR INTO @COLUMN_NAME,@DATA_TYPE,@LENGTH
WHILE @@FETCH_STATUS = 0
BEGIN
SET @COUNT = @COUNT + 1
SET @T = @T + '[' + @COLUMN_NAME+ '] ' + @DATA_TYPE + (CASE WHEN @DATA_TYPE IN ('NVARCHAR','NCHAR') THEN '(MAX)' ELSE '' END);
IF @COUNT <> @@CURSOR_ROWS BEGIN
SET @T = @T + ' , '
END ELSE BEGIN
SET @T = @T + ' ) '
END
-- 抓下一筆
FETCH NEXT FROM MYCURSOR INTO @COLUMN_NAME,@DATA_TYPE,@LENGTH
END
CLOSE MYCURSOR
DEALLOCATE MYCURSOR
EXEC (@T)
END
留言列表