locked
How to create logical alter script RRS feed

  • Question

  • hi ;

    I am already using the below code for a magic script , But I want to add to script with DEFAULT value ,How can I do .

    SELECT 'ALTER TABLE ' + TABLE_NAME +' '+ 'ADD'+' ' + COLUMN_NAME + ' ' + DATA_TYPE + CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN '(max)' WHEN DATA_TYPE in ('text','ntext','image') THEN '' WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' ) ELSE ISNULL(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ') END +' '+ CASE IS_NULLABLE WHEN 'YES' THEN 'NULL' WHEN 'No' THEN 'NOT NULL' END AS CLMNS FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='PERSONAL_TABLE' AND COLUMN_NAME='SALARY'

    So I want to take a result like below

    ALTER TABLE PERSONAL_COLUMN ADD SALARY INT DEFAULT '0'

    ALTER TABLE PERSONAL_COLUMN ADD SALARY SMALLDATETIME DEFAULT GETDATE

    Thanks in advance.


    • Edited by Aly14 Saturday, July 14, 2018 3:52 PM a
    Saturday, July 14, 2018 3:52 PM

Answers

  • Here's an example that uses the catalog views to extract the full column definition plus default constraint, if one exists. Be aware that this will not include primary key constraints, check constraints, foreign key constraints, indexes, column encryption, etc.

    DECLARE @SourceTableName nvarchar(261) = N'dbo.PERSONAL_TABLE';
    DECLARE @ColumnName sysname = N'SALARY';
    SELECT 
        CASE 
            WHEN c.is_computed = 1 
                THEN QUOTENAME(c.name) + N' AS (' + cc.definition + N')'
            ELSE
                QUOTENAME(c.name) 
                + ' ' + TYPE_NAME(c.user_type_id)
                + CASE 
                    --types without length, precision, or scale specification
                    WHEN TYPE_NAME(c.user_type_id) IN (N'int',N'bigint',N'smallint',N'tinyint',N'money',N'smallmoney',N'real',N'datetime',N'smalldatetime',N'bit',N'image',N'text',N'uniqueidentifier',N'date',N'ntext',N'sql_variant',N'hierarchyid','geography',N'timestamp',N'xml') 
                        THEN N''
                    --types with precision and scale specification
                    WHEN TYPE_NAME(c.user_type_id) in (N'decimal',N'numeric') 
                        THEN N'(' + CAST(c.precision AS varchar(5)) + N',' + CAST(c.scale AS varchar(5)) + N')'
                    --types with scale specification only
                    WHEN TYPE_NAME(c.user_type_id) in (N'time',N'datetime2',N'datetimeoffset') 
                        THEN N'(' + CAST(c.scale AS varchar(5)) + N')'
                    --float default precision is 53 - add precision when column has a different precision value
                    WHEN TYPE_NAME(c.user_type_id) in (N'float')
                        THEN CASE WHEN c.precision = 53 THEN N'' ELSE N'(' + CAST(c.precision AS varchar(5)) + N')' END
                    --types with length specifiecation
                    ELSE N'(' + CASE c.max_length WHEN -1 THEN N'MAX' ELSE CAST(c.max_length AS nvarchar(20)) END + N')'
            END
            + CASE WHEN c.is_filestream = 1 THEN N' FILESTREAM' ELSE '' END
            + COALESCE(N' COLLATE ' + c.collation_name, N'')
            + CASE WHEN c.is_sparse = 1 THEN N' SPARSE' ELSE N'' END
            + CASE WHEN c.is_identity = 1 THEN N' IDENTITY(' + CAST(IDENT_SEED(@SourceTableName) AS varchar(20)) + ',' + CAST(IDENT_INCR(@SourceTableName) AS varchar(20)) + ')' ELSE '' END
            + CASE WHEN c.is_rowguidcol = 1 THEN N' ROWGUIDCOL' ELSE '' END
            + CASE WHEN c.is_nullable = 1 THEN N' NULL' ELSE N' NOT NULL' END
    		+ CASE WHEN d.object_id IS NULL THEN '' ELSE ' CONSTRAINT ' + QUOTENAME(d.name) + ' DEFAULT ' + d.definition END
        END
    FROM sys.columns AS c 
    LEFT JOIN sys.computed_columns AS cc ON
        cc.object_id = c.object_id
        AND cc.column_id = c.column_id
    LEFT JOIN sys.default_constraints AS d ON
    	d.parent_object_id = c.object_id
    	AND d.parent_column_id = c.column_id
    WHERE
        c.object_id = OBJECT_ID(@SourceTableName)
    	AND c.name = @ColumnName;


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com




    • Edited by Dan GuzmanMVP, Editor Saturday, July 14, 2018 8:58 PM
    • Proposed as answer by Xi Jin Monday, July 16, 2018 8:25 AM
    • Marked as answer by Aly14 Monday, July 16, 2018 8:47 AM
    Saturday, July 14, 2018 8:10 PM
    Answerer

All replies

  • use "case when" + "Datatype"

    it looks like:

    ...
    	   + ' DEFAULT ' + 
    	   case data_type 
    			when 'int' then '0'
    			when 'SMALLDATETIME' then 'GETDATE()'
    	   end 
    .....
    full script:

    SELECT  'ALTER TABLE ' + table_name + ' ' + 'ADD' + ' ' 
           + column_name + ' ' + data_type + 
    	   CASE WHEN character_maximum_length = -1 
    		   THEN '(max)' 
    		   WHEN data_type IN ('text', 'ntext', 'image') THEN '' 
    		   WHEN character_maximum_length IS NOT NULL THEN '('+(CONVERT(VARCHAR, 
    		   character_maximum_length)+')' ) 
    		   ELSE Isnull(CONVERT(VARCHAR, 
    		   character_maximum_length), ' ') 
    	   END 
    	   + ' ' + 
    	   CASE is_nullable 
    		   WHEN 'YES' THEN 'NULL' 
    		   WHEN 'No' THEN 'NOT NULL' 
    	   END 
    	   + ' DEFAULT ' + 
    	   case data_type 
    			when 'int' then '0'
    			when 'SMALLDATETIME' then 'GETDATE()'
    	   end 
    	   AS CLMNS 
    FROM   information_schema.columns 
    WHERE  1=1
    	and table_name = 'PERSONAL_TABLE' 
    	AND column_name = 'SALARY' 

    if you want more datatype default's value just add "when datatype then 'default value'"

    • Edited by IT-Wei Saturday, July 14, 2018 4:21 PM
    Saturday, July 14, 2018 4:17 PM
  • The ANSI/ISO standard INFORMATION_SCHEMA views do not expose SQL Server default constraints so you'll need to use catalog views instead. Below is an example that will add the default separately but you could incorporate into your query with a JOIN, if needed.

    SELECT 'ALTER TABLE ' + OBJECT_NAME(c.object_id) + ' ADD CONSTRAINT ' + d.name + ' DEFAULT ' + d.definition + ' FOR ' + c.name
    FROM sys.columns AS c
    JOIN sys.default_constraints AS d ON
    	d.parent_object_id = c.object_id
    	AND d.parent_column_id = c.column_id
    WHERE
    	c.object_id = OBJECT_ID(N'PERSONAL_TABLE')
    	AND c.name='SALARY';

    For a more robust script, consider schema-qualifying objects, using the QUOTENAME function to enclose identifiers, adding precision and scale for decimal/numeric, etc. Personally, I prefer to use the catalog views for this sort of task as those also expose proprietary SQL Server features like IDENTITY et.al. that are part of the column definition.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Saturday, July 14, 2018 4:31 PM
    Answerer
  • this is only add constraint to already column ,

    I want to add column and default value same time such as

    ALTER TABLE PERSONAL_COLUMN ADD SALARY INT DEFAULT '0'

    ALTER TABLE PERSONAL_COLUMN ADD SALARY SMALLDATETIME DEFAULT GETDATE

    Saturday, July 14, 2018 5:22 PM
  • this is like what I need  , is it possible to read column fatures , thus ;

    I have a 2 sql server , I am creatting the manuel column on 1.sql server like below

    ALTER TABLE PERSONAL_COLUMN ADD SALARY INT DEFAULT '0'

    I want to add the new column to 2. sql server reading from 1.sql server

    when 'int' then '0'   -- I dont know this value (0)
      

    Saturday, July 14, 2018 5:31 PM
  • Here's an example that uses the catalog views to extract the full column definition plus default constraint, if one exists. Be aware that this will not include primary key constraints, check constraints, foreign key constraints, indexes, column encryption, etc.

    DECLARE @SourceTableName nvarchar(261) = N'dbo.PERSONAL_TABLE';
    DECLARE @ColumnName sysname = N'SALARY';
    SELECT 
        CASE 
            WHEN c.is_computed = 1 
                THEN QUOTENAME(c.name) + N' AS (' + cc.definition + N')'
            ELSE
                QUOTENAME(c.name) 
                + ' ' + TYPE_NAME(c.user_type_id)
                + CASE 
                    --types without length, precision, or scale specification
                    WHEN TYPE_NAME(c.user_type_id) IN (N'int',N'bigint',N'smallint',N'tinyint',N'money',N'smallmoney',N'real',N'datetime',N'smalldatetime',N'bit',N'image',N'text',N'uniqueidentifier',N'date',N'ntext',N'sql_variant',N'hierarchyid','geography',N'timestamp',N'xml') 
                        THEN N''
                    --types with precision and scale specification
                    WHEN TYPE_NAME(c.user_type_id) in (N'decimal',N'numeric') 
                        THEN N'(' + CAST(c.precision AS varchar(5)) + N',' + CAST(c.scale AS varchar(5)) + N')'
                    --types with scale specification only
                    WHEN TYPE_NAME(c.user_type_id) in (N'time',N'datetime2',N'datetimeoffset') 
                        THEN N'(' + CAST(c.scale AS varchar(5)) + N')'
                    --float default precision is 53 - add precision when column has a different precision value
                    WHEN TYPE_NAME(c.user_type_id) in (N'float')
                        THEN CASE WHEN c.precision = 53 THEN N'' ELSE N'(' + CAST(c.precision AS varchar(5)) + N')' END
                    --types with length specifiecation
                    ELSE N'(' + CASE c.max_length WHEN -1 THEN N'MAX' ELSE CAST(c.max_length AS nvarchar(20)) END + N')'
            END
            + CASE WHEN c.is_filestream = 1 THEN N' FILESTREAM' ELSE '' END
            + COALESCE(N' COLLATE ' + c.collation_name, N'')
            + CASE WHEN c.is_sparse = 1 THEN N' SPARSE' ELSE N'' END
            + CASE WHEN c.is_identity = 1 THEN N' IDENTITY(' + CAST(IDENT_SEED(@SourceTableName) AS varchar(20)) + ',' + CAST(IDENT_INCR(@SourceTableName) AS varchar(20)) + ')' ELSE '' END
            + CASE WHEN c.is_rowguidcol = 1 THEN N' ROWGUIDCOL' ELSE '' END
            + CASE WHEN c.is_nullable = 1 THEN N' NULL' ELSE N' NOT NULL' END
    		+ CASE WHEN d.object_id IS NULL THEN '' ELSE ' CONSTRAINT ' + QUOTENAME(d.name) + ' DEFAULT ' + d.definition END
        END
    FROM sys.columns AS c 
    LEFT JOIN sys.computed_columns AS cc ON
        cc.object_id = c.object_id
        AND cc.column_id = c.column_id
    LEFT JOIN sys.default_constraints AS d ON
    	d.parent_object_id = c.object_id
    	AND d.parent_column_id = c.column_id
    WHERE
        c.object_id = OBJECT_ID(@SourceTableName)
    	AND c.name = @ColumnName;


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com




    • Edited by Dan GuzmanMVP, Editor Saturday, July 14, 2018 8:58 PM
    • Proposed as answer by Xi Jin Monday, July 16, 2018 8:25 AM
    • Marked as answer by Aly14 Monday, July 16, 2018 8:47 AM
    Saturday, July 14, 2018 8:10 PM
    Answerer
  • thanks for your effort
    Monday, July 16, 2018 8:47 AM