Ultimate Alter Script generator

,

@vTableName = Table to be updated.
@vColumnName = Column to be added/updated.
@vColumnDT = Column Data Type.
@vColumnDF = Column Default value.
@bIsNULL = Is nullable column boolean value
@bNewColFlag = 0:Existing Col, 1:New Column boolean value
@FKtableName = If New col is Foreign Key pass the FK Table otherwise pass empty string ''
@FKColName = If New col is Foreign Key pass the FK Table Column Name otherwise pass empty string ''

 

Expected Result:

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'Employee' AND column_name = 'SSNNumber') 
ALTER TABLE dbo.Employee ALTER COLUMN SSNNumber NVARCHAR(10) NULL DEFAULT ('N')
GO

 

 

 

DECLARE
    @vTableName        VARCHAR(100),
    @vColumnName    VARCHAR(100),
    @vColumnDT        VARCHAR(100),
    @vColumnDF        VARCHAR(100),        
    @bIsNULL        BIT,
    @bNewColFlag    BIT,            --0:Existing Col, 1:New Column
    @FKtableName    VARCHAR(100),
    @FKColName        VARCHAR(100)
    
SET @vTableName = 'Employee'
SET @vColumnName = 'SSNNumner'
SET @vColumnDT = 'NVARCHAR(10)'
SET @bNewColFlag = 1
SET @bIsNULL = 1
SET @FKtableName = ''
SET @FKColName = ''
SET @vColumnDF = 'N'
IF @vColumnDF != '' BEGIN SET @vColumnDF = CASE WHEN ISNUMERIC(@vColumnDF) = 0 THEN ''''+@vColumnDF + '''' END END

BEGIN
    SET NOCOUNT ON

    DECLARE @SQL VARCHAR(500), @IndexName VARCHAR(100), @IndexType VARCHAR(20) 

    IF @bNewColFlag = 1
    BEGIN
        SET @SQL = 'IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = '''+ @vTableName +''' AND column_name = '''+ @vColumnName + ''') ' + CHAR(10) + 
                    '    ALTER TABLE dbo.'+ @vTableName +' ADD '+ @vColumnName + ' ' + @vColumnDT + CASE WHEN @bIsNULL = 1 THEN ' NULL ' ELSE ' NOT NULL ' END + 
                    CASE WHEN @vColumnDF != '' THEN 'CONSTRAINT ' + @vTableName + '_' + @vColumnName + '_DF DEFAULT (' + @vColumnDF + ')' ELSE '' END + CHAR(10) + 'GO'
    PRINT @SQL

    IF @FKtableName != '' BEGIN
        SET @SQL = 'IF NOT EXISTS(SELECT * FROM sys.foreign_keys WHERE name = ''' + @vTableName + '_' + @FKtableName + '_FK'')' + CHAR(10) + 
                    '    ALTER TABLE dbo.' + @vTableName + ' ADD CONSTRAINT ' + @vTableName + '_' + @FKtableName + '_FK ' + CHAR(10) + 
                    '    FOREIGN KEY ('+ @vColumnName + ')' + CHAR(10) + 
                    '    REFERENCES dbo.'+ @FKtableName + '('+ @FKColName+ ')'+ CHAR(10) + 'GO'
        PRINT @SQL

    SET @SQL = 'IF NOT EXISTS(SELECT * FROM sys.indexes WHERE name = ''' + @vTableName + '_' + @vColumnName + '_IDX'')'+ CHAR(10) +
                '    CREATE NONCLUSTERED INDEX ' + @vTableName + '_' + @vColumnName + '_IDX ON dbo.'+ @vTableName + '('+ @vColumnName + ' ASC)' + CHAR(10) + 'GO'
    PRINT @SQL
    END

    END
    ELSE
    BEGIN
        SELECT    @IndexName= I.name, @IndexType = CASE I.type WHEN 1 THEN 'CLUSTERED' ELSE 'NONCLUSTERED' END
        FROM    sys.indexes I 
        JOIN    sys.index_columns IC ON object_name(I.object_id) = @vTableName AND I.object_id = IC.object_id AND I.index_id = IC.index_id 
        JOIN    sys.columns C ON C.object_id = IC.object_id AND IC.column_id = C.column_id AND c.Name = @vColumnName

        IF @IndexName != '' 
        BEGIN
            SET @SQL = 'IF EXISTS(SELECT * FROM sys.indexes WHERE name = '''+ @IndexName + ''') ' + CHAR(10) +
                        '    DROP INDEX '+ @IndexName + ' ON dbo.'+ @vTableName + CHAR(10) + 'GO'
            PRINT @SQL

        END 

        SET @SQL = 'IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = '''+ @vTableName +''' AND column_name = '''+ @vColumnName + ''') ' + CHAR(10) + 
                    '    ALTER TABLE dbo.'+ @vTableName +' ALTER COLUMN '+ @vColumnName + ' ' + @vColumnDT + CASE WHEN @bIsNULL = 1 THEN ' NULL ' ELSE ' NOT NULL ' END + 
                    CASE WHEN @vColumnDF != '' THEN 'DEFAULT (' + @vColumnDF + ')' ELSE '' END + CHAR(10) + 'GO'
        PRINT @SQL

        SET @SQL = 'IF NOT EXISTS(SELECT * FROM sys.indexes WHERE name = '''+ @IndexName +''')'+ CHAR(10) +
                '    CREATE NONCLUSTERED INDEX '+ @IndexName +' ON dbo.'+ @vTableName + '('+ @vColumnName + ' ASC)' + CHAR(10) + 'GO'
        PRINT @SQL
    
    END

END
GO

Rate

1.5 (2)

Share

Share

Rate

1.5 (2)