ALTER TABLE 정리를 위한 쿼리문

declare  @tableName Nvarchar(50)

SET  @tableName = 'OPO_PO' --  TABLE NA

SELECT
'if not exists (select * from sysobjects a, syscolumns b where a.name = '''+ A.TABLE_NAME + ''' and b.name = '''+ COLUMN_NAME+''' and a.id = b.id) begin '

    + 'ALTER TABLE ' + A.TABLE_NAME +
       '   ADD ' + COLUMN_NAME + ' ' + DATA_TYPE
    +  CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 then '(max)'
            WHEN DATA_TYPE in ('text','ntext') then ''
            WHEN DATA_TYPE in ('numeric') then '(' + CONVERT(VARCHAR(10), NUMERIC_PRECISION) +','+  CONVERT(VARCHAR(10), NUMERIC_SCALE) + ')'
            WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' )
            ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ')
       END
    +  CASE WHEN IS_NULLABLE = 'YES' THEN ' NULL'
            WHEN IS_NULLABLE = 'No'  THEN ' NOT NULL'
       END
    +  ' CONSTRAINT [DF_' + + A.TABLE_NAME + '_' + COLUMN_NAME + '] DEFAULT ' +  A.column_default
    +  ' END'
,

       'ALTER TABLE ' + A.TABLE_NAME +
       '   ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE
    +  CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 then '(max)'
            WHEN DATA_TYPE in ('text','ntext') then ''
            WHEN DATA_TYPE in ('numeric') then '(' + CONVERT(VARCHAR(10), NUMERIC_PRECISION) +','+  CONVERT(VARCHAR(10), NUMERIC_SCALE) + ')'
            WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' )
            ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ')
       END
    +  CASE WHEN IS_NULLABLE = 'YES' THEN ' NULL'
            WHEN IS_NULLABLE = 'No'  THEN ' NOT NULL'
       END


FROM INFORMATION_SCHEMA.COLUMNS A INNER JOIN INFORMATION_SCHEMA.TABLES B
ON  A.TABLE_NAME = B.TABLE_NAME
AND A.TABLE_SCHEMA  = B.TABLE_SCHEMA
WHERE TABLE_TYPE = 'BASE TABLE'
 and A.TABLE_NAME = @tableName

덧글

[남성 확대 알약]

훌륭한 게시물입니다. 나는 이것이 지쳤다고 생각한다.