MSSQL 단축키 전용 SP 만들기

-- 프로시저 파라미터 만들기

USE [TEST]
GO
/****** Object:  StoredProcedure [dbo].[USP_SOURCE_GEN]    Script Date: 2024-07-04 오후 3:15:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		전광식
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- EXEC [USP_SOURCE_GEN]  'PM_MONTHLY_PRODUCT'
-- =============================================
ALTER PROCEDURE [dbo].[USP_SOURCE_GEN]
(
	@TABALE_NAME Nvarchar(100)
)
AS
BEGIN

	DECLARE @columns NVARCHAR(MAX) = '';
    DECLARE @sql NVARCHAR(MAX) = '';
    
    -- Get column names and data types
    SELECT @columns = @columns + ', @' + LEFT(COLUMN_NAME + REPLICATE(' ', 30), 30)  + ' ' + UPPER(DATA_TYPE) +
                      CASE 
                        WHEN DATA_TYPE IN (N'varchar', N'char', N'nvarchar', N'nchar') THEN '(' + 
                            CASE 
                              WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX'
                              ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS NVARCHAR(10)) 
                            END + ')'
                        WHEN DATA_TYPE IN (N'decimal', N'numeric') THEN '(' + 
                            CAST(NUMERIC_PRECISION AS NVARCHAR(10)) + ', ' + 
                            CAST(NUMERIC_SCALE AS NVARCHAR(10)) + ')'
                        ELSE ''
                      END + CHAR(13) + CHAR(10)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @TABALE_NAME;
    
    -- Remove the first comma and space
    SET @columns = STUFF(@columns, 1, 2, '');
    
    -- Generate the CREATE PROCEDURE statement
    SET @sql = N'CREATE PROCEDURE [프로시저명] ' + CHAR(13) + CHAR(10) + @columns  + CHAR(13) + CHAR(10) + N'AS
BEGIN

	SET NOCOUNT ON;

	DECLARE @ERR	INT
	DECLARE @V_DATE DATETIME2 = GETDATE()
	DECLARE @V_MSG	NVARCHAR(MAX)




	--IF @@ROWCOUNT <> 1
	--BEGIN 
	--	SET @V_MSG =  N''저장중 오류가 발생하였습니다.'' + CONVERT(NVARCHAR(20),'''' + @YYYYMM + '' '' + CONVERT(NVARCHAR, @@ROWCOUNT)) 
	--	RAISERROR(@V_MSG , 16,1)	  
	--	RETURN -1;
	--END	
	
	--SET @ERR = @@Error
	--RETURN @ERR
END'
    
    -- Print the result
    PRINT @sql;

END

 

-- 선택된 테이블 조회

-- =============================================
-- Author:		<  >
-- Create date: <Create Date,,>
-- Description:	<SELECT 쿼리 >
-- EXEC [USP_SELECT_TABLE]  'TEST'
-- =============================================
CREATE PROCEDURE [dbo].[USP_SELECT_TABLE]
(
	@TABALE_NAME nvarchar(MAX)
)
AS
BEGIN

	DECLARE @tableCatalog NVARCHAR(MAX);
	DECLARE @tableSchema NVARCHAR(MAX);	
	DECLARE @tableName NVARCHAR(MAX);
	
	DECLARE @columns	NVARCHAR(MAX) = '';
	DECLARE @query		NVARCHAR(MAX);
	DECLARE @lastDateTimeColumn NVARCHAR(128); --마지막 컬럼 DATETIME 찾기

	-- Get the selected table name
	SELECT @tableName = TABLE_NAME
		 , @tableSchema = TABLE_SCHEMA
		 , @tableCatalog = TABLE_CATALOG
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_TYPE = 'BASE TABLE'
	  AND TABLE_NAME = @TABALE_NAME; -- Replace with your specific table name or logic to get the selected table

	-- Get column names
	SELECT @columns = STRING_AGG(QUOTENAME(COLUMN_NAME), ', ')
	FROM INFORMATION_SCHEMA.COLUMNS 
	WHERE TABLE_NAME = @tableName;


	SELECT @lastDateTimeColumn = COLUMN_NAME
	FROM (
		SELECT COLUMN_NAME,
			   ROW_NUMBER() OVER (PARTITION BY TABLE_NAME ORDER BY ORDINAL_POSITION DESC) AS rn
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = @tableName
		  AND DATA_TYPE IN ('datetime', 'datetime2', 'smalldatetime', 'date', 'time', 'datetimeoffset')
	) AS dt
	WHERE rn = 1;

	-- Remove trailing comma and space 
	IF @lastDateTimeColumn IS NOT NULL
	BEGIN
		SET @query = 'SELECT TOP 500 ' + @columns   + ' FROM '  + @tableCatalog + '.' + @tableSchema +'.' + @tableName + ' ORDER BY ' + @lastDateTimeColumn +' DESC';
	END
	ELSE
	BEGIN
	-- Generate SELECT query
		SET @query = 'SELECT TOP 500 ' + @columns + ' FROM ' + @tableCatalog + '.' + @tableSchema +'.' + @tableName  ;
	END

	EXEC sp_executesql  @query;
 

END

sample.sql

0.00MB