이 sp를 실행하면 테이블 구조의 내용을 script로 뽑아준다.
맨날 mssm의 수정 부분들어가서 script생성 어쩌구 눌러서 만들려면 귀찮다.
결과물은 아래 그림을 참조.
--◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆
--◆ (c) Copyright Brainnet Co..Ltd. 2005
--◆ All rights reserved
--◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆
--◆ Procedure Name : SP_Z_GET_TABLE_CREATE
--◆ Program Code : SP_Z_GET_TABLE_CREATE
--◆ Description : table create 명령줄 뽑아내기.
--◆ Author : 홍길동
--◆ Date : 2016. 06. 29.
--◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆
--◆ Modification Log
--◆
--◆ Date In Charge Description
--◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆
--◆ 2014. 12. 16. softone Initial Release
--◆
--◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆
CREATE PROCEDURE SP_Z_GET_TABLE_CREATE
(
@P_TABLE_NAME nVarchar(100)
)
AS
BEGIN
DECLARE @SQL TABLE(S VARCHAR(4000), ID INT IDENTITY)
declare #ZSY_SYS_cur01 cursor for
SELECT upper(name) o_name
FROM sysobjects
WHERE xtype = 'U'
and name = @P_TABLE_NAME
order by upper(name)
OPEN #ZSY_SYS_cur01
fetch next from #ZSY_SYS_cur01 into
@P_TABLE_NAME
WHILE (@@fetch_status <> -1)
BEGIN
INSERT INTO @SQL(S)
SELECT 'CREATE TABLE ' + @P_TABLE_NAME + ' ('
;WITH AAA AS (
SELECT OBJECT_NAME(o.parent_object_id) AS TABLE_NAME
, o.Name AS CONSTRAINT_NAME
, cost_name.name COLUMN_NAME
FROM sys.objects o
JOIN sys.schemas s
ON o.schema_id = s.schema_id
JOIN sys.all_columns cost_name
ON o.object_id = cost_name.default_object_id
AND o.parent_object_id = cost_name.object_id
WHERE 1=1
AND o.type = 'D'
AND OBJECT_NAME(o.parent_object_id) = @P_TABLE_NAME
)
INSERT INTO @SQL(S)
SELECT CASE WHEN ORDINAL_POSITION = 1 THEN ' ' ELSE ' , ' END
+ A.COLUMN_NAME
+ N' ' + DATA_TYPE +
CASE WHEN CHARACTER_MAXIMUM_LENGTH > 0 THEN '(' + CONVERT(VARCHAR(MAX), CHARACTER_MAXIMUM_LENGTH) + ')'
WHEN NUMERIC_PRECISION > 0 THEN '(' + CONVERT(VARCHAR(MAX), NUMERIC_PRECISION)+ ',' + CONVERT(VARCHAR(MAX), NUMERIC_SCALE) + ')'
WHEN DATETIME_PRECISION > 0 THEN ''
END
+ N' ' + ( CASE WHEN IS_NULLABLE = 'NO' THEN 'NOT ' ELSE '' END ) + 'NULL '
+ N' CONSTRAINT ' + CASE WHEN CONSTRAINT_NAME IS NULL THEN '' ELSE CONSTRAINT_NAME END
+ N' ' + CASE WHEN COLUMN_DEFAULT IS NULL THEN '' ELSE COALESCE('DEFAULT '+COLUMN_DEFAULT,'') END
FROM INFORMATION_SCHEMA.COLUMNS A
LEFT
JOIN AAA B
ON A.TABLE_NAME = B.TABLE_NAME
AND A.COLUMN_NAME = B.COLUMN_NAME
WHERE A.TABLE_NAME = @P_TABLE_NAME
ORDER
BY A.ORDINAL_POSITION
;
INSERT INTO @SQL(S)
SELECT ' )'
INSERT INTO @SQL(S)
SELECT ' ALTER TABLE ' + @P_TABLE_NAME + ' ADD CONSTRAINT ' + ' PK_' + @P_TABLE_NAME + N' PRIMARY KEY '
;WITH AAA AS (
SELECT OBJECT_NAME(o.parent_object_id) AS TABLE_NAME
, o.Name AS CONSTRAINT_NAME
, cost_name.name COLUMN_NAME
FROM sys.objects o
JOIN sys.schemas s
ON o.schema_id = s.schema_id
JOIN sys.all_columns cost_name
ON o.object_id = cost_name.default_object_id
AND o.parent_object_id = cost_name.object_id
WHERE 1=1
AND o.type = 'D'
AND OBJECT_NAME(o.parent_object_id) = @P_TABLE_NAME
)
INSERT INTO @SQL(S)
SELECT CASE WHEN A.column_id = 1 THEN ' ( ' ELSE ' , ' END + INDEX_COL(@P_TABLE_NAME, A.INDEX_ID, A.INDEX_COLUMN_ID ) COL_NM
FROM sys.index_columns A
JOIN SYSINDEXES B
ON A.object_id = B.ID
AND B.INDID = A.INDEX_ID
WHERE A.object_id = OBJECT_ID( @P_TABLE_NAME )
INSERT INTO @SQL(S)
SELECT ' )'
fetch next from #ZSY_SYS_cur01 into
@P_TABLE_NAME
END
CLOSE #ZSY_SYS_cur01
DEALLOCATE #ZSY_SYS_cur01
SELECT * FROM @SQL
END
최근 덧글