[MSSQL] CREATE TABLE 쿼리문 일괄로 쉽게 만들기

 

테이블 스키마를 보고 싶을 때 흔히들

 

EXEC SP_HELP '테이블명'  요렇게 많이 할 것이다.

난 요럿게 내 입맛에 맞게 바꿔서 사용한다.

 

그리고 아래 쿼리를 잘 이용하면 특정 DB내의 모든 테이블 CREATE 문을 자동 생성할 수도 있다.

 

CREATE PROC DBO.USP_GET_TABLE_SCHEMA @TBLNAME VARCHAR(100) /************************************************************************     1> 내   용: 내입맛대로테이블스키마뽑기     2> 입력내용: @TBLNAME = 테이블명(ex. DBO.F_SALES_ORDERS)     3> 출력내용: 변경된테이블스키마     4> 작성자명: 민경태     5> 작성일자: 2011-11-25     6> 수정내역:       7> TEST CODE :     EXEC USP_GET_TABLE_SCHEMA 'DBO.TABLE_NAME' *************************************************************************/ AS SET NOCOUNT ON --DECLARE @TBLNAME VARCHAR(100) --SET @TBLNAME = 'DBO.F_SALES_ORDERS' --// 자료형바꿔서출력 SELECT A.NAME AS 컬럼명     , B.NAME AS 자료형     , A.LENGTH AS 크기     , ' →' AS 변경     , A.NAME AS NAME     , CASE WHEN B.NAME='NVARCHAR' THEN 'VARCHAR' ELSE B.NAME END AS DATATYPE     , A.LENGTH AS LENGTH     , A.XPREC     , A.XSCALE     , A.COLORDER AS [ORDER] INTO #TMP FROM SYSCOLUMNS AS A INNER JOIN SYSTYPES AS B ON A.XTYPE=B.XTYPE WHERE ID=OBJECT_ID(@TBLNAME) AND B.NAME <> 'SYSNAME' ORDER BY COLORDER   --SELECT * FROM #TMP   SELECT 'CREATE TABLE '+@TBLNAME+'_MOVE (' AS SCRIPT, 0 AS [ORDER]   UNION ALL   SELECT     CASE DATATYPE         WHEN 'VARCHAR'          THEN '['+UPPER(NAME)+']'+DATATYPE+'('+CONVERT(VARCHAR,[LENGTH])+'),'         WHEN 'CHAR'             THEN '['+UPPER(NAME)+']'+DATATYPE+'('+CONVERT(VARCHAR,[LENGTH])+'),'         WHEN 'TEXT'             THEN '['+UPPER(NAME)+']'+DATATYPE+','         WHEN 'NVARCHAR'         THEN '['+UPPER(NAME)+']'+DATATYPE+'('+CONVERT(VARCHAR,[LENGTH])+'),'         WHEN 'NCHAR'            THEN '['+UPPER(NAME)+']'+DATATYPE+'('+CONVERT(VARCHAR,[LENGTH])+'),'         WHEN 'NTEXT'            THEN '['+UPPER(NAME)+']'+DATATYPE+','         WHEN 'BIT'              THEN '['+UPPER(NAME)+']'+DATATYPE+','         WHEN 'TINYINT'          THEN '['+UPPER(NAME)+']'+DATATYPE+','         WHEN 'SMALLINT'         THEN '['+UPPER(NAME)+']'+DATATYPE+','         WHEN 'BIGINT'           THEN '['+UPPER(NAME)+']'+DATATYPE+','         WHEN 'SMALLMONEY'       THEN '['+UPPER(NAME)+']'+DATATYPE+','         WHEN 'MONEY'            THEN '['+UPPER(NAME)+']'+DATATYPE+','         WHEN 'BIGINT'           THEN '['+UPPER(NAME)+']'+DATATYPE+','         WHEN 'INT'              THEN '['+UPPER(NAME)+']'+DATATYPE+','         WHEN 'FLOAT'            THEN '['+UPPER(NAME)+']'+DATATYPE+','         WHEN 'DATETIME'         THEN '['+UPPER(NAME)+']'+DATATYPE+','         WHEN 'SMALLDATETIME'    THEN '['+UPPER(NAME)+']'+DATATYPE+','         WHEN 'DECIMAL'          THEN '['+UPPER(NAME)+']'+DATATYPE+'('+CONVERT(VARCHAR,[XPREC])+','+CONVERT(VARCHAR,[XSCALE])+'),'         WHEN 'NUMERIC'          THEN '['+UPPER(NAME)+']'+DATATYPE+'('+CONVERT(VARCHAR,[XPREC])+','+CONVERT(VARCHAR,[XSCALE])+'),'         WHEN 'XML'              THEN '['+UPPER(NAME)+']'+DATATYPE+','     END AS SCRIPT     , [ORDER]     FROM #TMP   UNION ALL   SELECT ')' AS SCRIPT, 255 AS [ORDER] ORDER BY [ORDER]   DROP TABLE #TMP