#title SP부하 테스트 스크립트만들기
[[TableOfContents]]

==== 만드는 중 ====
{{{
--1부터 10까지 만들기..
SELECT * 
INTO #data
FROM (
         SELECT 0 ID UNION ALL
         SELECT 1 ID UNION ALL
         SELECT 2 ID UNION ALL
         SELECT 3 ID UNION ALL
         SELECT 4 ID UNION ALL
         SELECT 5 ID UNION ALL
         SELECT 6 ID UNION ALL
         SELECT 7 ID UNION ALL
         SELECT 8 ID UNION ALL
         SELECT 9 ID ) T

--데이터 만들기 : 백만 건
--DROP TABLE #dumy
SELECT 
	D_INT
,	D_INT%256 D_TINYINT
,	D_INT%32767 D_SMALLINT
,	CAST(D_INT AS VARCHAR) D_INT_CHAR
,	CAST(D_INT%256 AS VARCHAR) D_TINYINT_CHAR
,	CAST(D_INT%32767 AS VARCHAR) D_SMALLINT_CHAR
,	RIGHT(NEWID(), 12) D_CHAR12
,	DATEADD(mi, D_INT, DATEADD (ms , D_INT, '20050101' )) D_DATE
INTO #dumy
FROM (
	SELECT 
		CAST(
			CAST(F.ID AS VARCHAR) + 
			CAST(E.ID AS VARCHAR) + 
			CAST(D.ID AS VARCHAR) + 
			CAST(C.ID AS VARCHAR) +
			CAST(B.ID AS VARCHAR) +
			CAST(A.ID AS VARCHAR)
		AS INT) D_INT
	FROM #data A CROSS JOIN #data B CROSS JOIN #data C CROSS JOIN #data D CROSS JOIN #data E CROSS JOIN #data F) T

--프로시저 정보
--drop table #temp
CREATE TABLE #temp (
	DBName varchar(255)
,	Owner varchar(255)
,	ProcName varchar(255)
,	ColumnName varchar(255)
,	ColumnType int
,	DataType int
,	TypeName varchar(255)
,	Precisions int
,	Length int
,	Scale int
,	Radix int
,	Nullable int
, 	Remarks varchar(255)
,	Column_Def varchar(255)
,	SqlDataType int
,	SqlDatetimeSub int
,	CharOctetLength int
,	Ordinal_Position int
,	Is_Nullalbe varchar(255)
,	ssDataType int
)
INSERT #temp EXEC sp_sproc_columns

SELECT
	DBName
,	Owner
,	CASE WHEN Ordinal_Position = 1 THEN ProcName ELSE '' END ProcName
,	ParamName
,	TypeName
,	Length
,	Ordinal_Position
FROM (
	SELECT TOP 100 PERCENT
		DBName
	,	Owner
	,	SUBSTRING(ProcName, 1, PATINDEX('%;%', ProcName) - 1) ProcName
	,	ColumnName ParamName
	,	TypeName
	,	Length
	,	Ordinal_Position
	FROM #temp
	WHERE Ordinal_Position > 0 
	ORDER BY ProcName, Ordinal_Position) T 
WHERE 1=1
--AND ProcName IN (SELECT name FROM sysobjects WHERE xtype = 'P' AND status > 0)

SELECT
	REPLACE(
		REPLACE(
			REPLACE(
				REPLACE(
					REPLACE(
						REPLACE(
							REPLACE(p_name, 'smallint', D_SMALLINT_CHAR), 
							'tinyint', D_TINYINT_CHAR), 
						'int', D_INT_CHAR), 
					'char', '''' + D_CHAR12 + ''''), 'var', ''), 
				'datetime', '''' + CONVERT(VARCHAR, D_DATE, 121) + ''''), 'small', '')
FROM (
	SELECT
		MIN(
			CASE WHEN TypeName LIKE '%char%' OR TypeName LIKE '%binary%' THEN  
				CASE WHEN ColumnType = 5 THEN 'DECLARE @a ' + TypeName + '(' + CAST(Length AS VARCHAR) + ') OUTPUT ' ELSE '' END
			ELSE CASE WHEN ColumnType = 5 THEN 'DECLARE @a ' + TypeName ELSE '' END
			END
		) + 
		MIN('EXEC ' + DBName + '.' + Owner + '.' + ProcName + ' ') + 
		ISNULL(MIN(CASE WHEN Ordinal_Position = 1 THEN ParamName + '=' + TypeName + ' ' END), '') +  
		ISNULL(MIN(CASE WHEN Ordinal_Position = 2 THEN ', ' + ParamName + '=' + TypeName + ' ' END), '') + 
		ISNULL(MIN(CASE WHEN Ordinal_Position = 3 THEN ', ' + ParamName + '=' + TypeName + ' ' END), '') + 
		ISNULL(MIN(CASE WHEN Ordinal_Position = 4 THEN ', ' + ParamName + '=' + TypeName + ' ' END), '') + 
		ISNULL(MIN(CASE WHEN Ordinal_Position = 5 THEN ', ' + ParamName + '=' + TypeName + ' ' END), '') + 
		ISNULL(MIN(CASE WHEN Ordinal_Position = 6 THEN ', ' + ParamName + '=' + TypeName + ' ' END), '') + 
		ISNULL(MIN(CASE WHEN Ordinal_Position = 7 THEN ', ' + ParamName + '=' + TypeName + ' ' END), '') + 
		ISNULL(MIN(CASE WHEN Ordinal_Position = 8 THEN ', ' + ParamName + '=' + TypeName + ' ' END), '') + 
		ISNULL(MIN(CASE WHEN Ordinal_Position = 9 THEN ', ' + ParamName + '=' + TypeName + ' ' END), '') + 
		ISNULL(MIN(CASE WHEN Ordinal_Position = 10 THEN ', ' + ParamName + '=' + TypeName + ' ' END), '') + 
		ISNULL(MIN(CASE WHEN Ordinal_Position = 11 THEN ', ' + ParamName + '=' + TypeName + ' ' END), '') p_name
	FROM (
		SELECT TOP 100 PERCENT
			DBName
		,	Owner
		,	SUBSTRING(ProcName, 1, PATINDEX('%;%', ProcName) - 1) ProcName
		,	ColumnName ParamName
		,	TypeName
		,	ColumnType
		,	Length
		,	Ordinal_Position
		FROM #temp
		WHERE Ordinal_Position > 0 
		AND Owner = 'PubSales'
		ORDER BY ProcName, Ordinal_Position) T 
	WHERE 1=1
	--AND ProcName IN (SELECT name FROM sys.sysobjects WHERE xtype = 'P' AND status > 0)
	GROUP BY ProcName) A CROSS JOIN (select top 100 * from #dumy) B

--DROP TABLE #temp
--DROP TABLE #data
--DROP TABLE #dumy
}}}