SQL Server 数据库所有表增加同一列

SQL Server 数据库所有表增加同一列

SET @COLUMN_NAME = 'ColumnNameYouWantToAdd';
SET @COLUMN_DATATYPE = 'DataTypeOfColumn';

------------------------------------------------Code---------------------------------------------------
USE TESTDB;
GO
--Declare Variables
DECLARE @TableName VARCHAR(100);
DECLARE @TableSchema VARCHAR(100);
DECLARE @COLUMN_NAME VARCHAR(50);
SET @COLUMN_NAME = 'CreatedOn'; -- Change Column Name according to your requirement
DECLARE @COLUMN_DATATYPE VARCHAR(50);
SET @COLUMN_DATATYPE = 'DateTime'; -- Provide data type for column

--Declare Cursor
DECLARE CUR CURSOR FOR
SELECT TABLE_SCHEMA,
       TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
--OPEN CURSOR
OPEN CUR;
--Fetch First Row
FETCH NEXT FROM CUR
INTO @TableSchema,
     @TableName;
--Loop
WHILE @@FETCH_STATUS = 0
BEGIN

    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL = NULL;
    IF NOT EXISTS
    (
        SELECT 1
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = @TableName
              AND COLUMN_NAME = @COLUMN_NAME
              AND TABLE_SCHEMA = @TableSchema
    )
    BEGIN
        SET @SQL = 'ALTER TABLE ' + @TableSchema + '.' + @TableName + ' ADD ' + @COLUMN_NAME + ' ' + @COLUMN_DATATYPE + ' ';
        PRINT @SQL;
        EXEC (@SQL);
    END;

    IF EXISTS
    (
        SELECT 1
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = @TableName
              AND COLUMN_NAME = @COLUMN_NAME
              AND TABLE_SCHEMA = @TableSchema
    )
    BEGIN
        PRINT 'Column Already exists in Table';
    END;
    FETCH NEXT FROM CUR
    INTO @TableSchema,
         @TableName;
END;
--Close and Deallocate Cursor
CLOSE CUR;
DEALLOCATE CUR;

 

posted on 2018-01-29 16:12 NET未来之路 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/lonelyxmas/p/8377867.html