[MS-SQL] 테이블 및 컬럼 설명, 사용자 프로시져(파라미터 만들기)

 

테이블 정보를 보거나 insert, update 등 쿼리를 만들때마다 테이블의 파라미터 정보를 쉽게 만들어 주는 사용자 프로시져 입니다.

 

쿼리 바로가기에 등록후 테이블정보가 필요시마다 사용 할 수 있습니다.

MS SSMS(SQL Server Management Studio) 쿼리 바로가기

 

블럭설정 후 바로가기 키를 누르면 그림과 같이 테이블 정보를 볼수 있습니다.

* column에 대한 설명  가 등록이 되어야 param_declare, parma_insert 등 parameter 구성하는 데이터가 보입니다.

(컬럼 설명 추가(수정) 쿼리 참고)

MS-SQL - 테이블, 컬럼 설명 추가, 수정 하기(SP_ADDEXTENDEDPROPERTY, SP_UPDATEEXTENDEDPROPERTY)

 

 

 

테이블 정보 사용자 프로시져(쿼리)

ALTER procedure [dbo].[USP_TABLE_DESCRIPTION]
(
 @tablename varchar(100)
)
as
begin

    SELECT d.value as table_desc
         , a.name as table_name
         , e.value as column_desc
         , b.name as column_name
         , c.name +   
           CASE WHEN c.name in ('char', 'varchar') THEN '(' + CONVERT(VARCHAR(5), b.max_length) + ')'   
                WHEN c.name in ('nchar', 'nvarchar') THEN '(' + CONVERT(VARCHAR(5), b.max_length / 2) + ')'   
                WHEN c.name in ('decimal', 'numeric') THEN '(' + CONVERT(VARCHAR(5), b.precision) + ',' + CONVERT(VARCHAR(5), b.scale) + ')'   
                ELSE ''   
           END AS data_type
         , CASE WHEN f.column_id IS NOT NULL THEN 'PK' ELSE '' END AS is_primary_key
         , CASE b.is_nullable WHEN 0 THEN 'NN' ELSE '' END AS is_nullable
         , ', ' + b.name + replicate(' ', g.col_len - len(b.name)) + ' -- ' + convert(varchar(1000), e.value) as name_desc
         , ',  -- ' + convert(varchar(1000), e.value) as _desc
         , ', @' + b.name + replicate(' ', g.col_len - len(b.name))  + ' '
         + UPPER(c.name) +   
           CASE WHEN c.name in ('char', 'varchar') THEN '(' + CONVERT(VARCHAR(5), b.max_length) + ')'   
                WHEN c.name in ('nchar', 'nvarchar') THEN '(' + CONVERT(VARCHAR(5), b.max_length / 2) + ')'   
                WHEN c.name in ('decimal', 'numeric') THEN '(' + CONVERT(VARCHAR(5), b.precision) + ',' + CONVERT(VARCHAR(5), b.scale) + ')'   
                ELSE ''   
           END AS params_declare
         , CASE WHEN b.name in ('inp_id', 'mdfy_id') then ', @id'
                when b.name in ('inp_dt', 'mdfy_dt') then ', CONVERT(NVARCHAR(8), GETDATE(), 112)'
                WHEN B.NAME IN ('inp_tm', 'mdfy_tm') then ', REPLACE(CONVERT(NVARCHAR(8), GETDATE(), 108), '':'', '''')'
           ELSE ', @' + b.name + replicate(' ', g.col_len - len(b.name)) + ' -- ' + convert(varchar(1000), e.value) END as param_insert
         , ', ' + b.name  + replicate(' ', g.col_len - len(b.name)) 
         + CASE WHEN b.name in ('inp_id', 'mdfy_id') then ' = @id'
                when b.name in ('inp_dt', 'mdfy_dt') then ' = CONVERT(NVARCHAR(8), GETDATE(), 112)'
                WHEN B.NAME IN ('inp_tm', 'mdfy_tm') then ' = REPLACE(CONVERT(NVARCHAR(8), GETDATE(), 108), '':'', '''')'
           ELSE ' = @' + b.name +  + replicate(' ', g.col_len - len(b.name)) + ' -- ' + convert(varchar(1000), e.value) END as param_update
	  FROM sys.tables AS a   
	 INNER JOIN sys.columns AS b
        ON a.object_id = b.object_id   
	 INNER JOIN sys.types AS c      
        ON b.user_type_id = c.user_type_id   
      LEFT JOIN (select object_id, max(len(name)) as col_len
                   from sys.columns
                  group by object_id) g
        ON a.object_id = g.object_id
	  LEFT OUTER JOIN sys.extended_properties AS d 
        ON a.object_id = d.major_id   
	   AND d.minor_id = 0   
	   AND d.name = 'MS_Description'   
	  LEFT OUTER JOIN sys.extended_properties AS e
        ON b.object_id = e.major_id   
	   AND b.column_id = e.minor_id   
	   AND e.name = 'MS_Description'   
	   AND E.class_desc <> 'INDEX'
	  LEFT OUTER JOIN (SELECT b.object_id, b.column_id   
						 FROM sys.indexes AS a   
						INNER JOIN sys.index_columns AS b
                           ON a.object_id = b.object_id   
						  AND a.index_id = b.index_id   
						  AND a.is_primary_key = 1
                      ) AS f  
		ON b.object_id = f.object_id   
	   AND b.column_id = f.column_id   
	 WHERE a.name like @tablename   
	 ORDER BY d.value, a.name, b.column_id  

end