Add new column to all tables in database

Question

I'm trying to figure out if there's a quick way or single query to add a new column to all tables in database.

Right now I'm doing this for each table

ALTER TABLE [dbo].[%TABLE_NAME%] ADD %COLUMN_NAME% DATATYPE NOT NULL DEFAULT %VALUE%;

Is there a procedure or query I can make in AzureDataStudio to add a new column to all tables with the same name and default value.

You can write yourself a script which iterates through all the tables in the database... loads of examples out there. — Dale K, Mar 10 '21 at 20:15
As everyone will comment sooner or later `Why do that at all?` What problem are you trying to solve? A single bit column won't fix any "locking" or row-level security problems. — Panagiotis Kanavos, Mar 10 '21 at 20:25
This isn't a security question the query I written was just example query I want to run on all tables. I just want an example script on how to alter all tables and add columns with the same name and type. — Anthony R, Mar 10 '21 at 20:39
@jarlh There could be any number of reasons. I've done that myself to add a CreateDate column to a bunch of tables in a schema where the developer omitted it. — Stu, Mar 10 '21 at 20:57

score 0 · Answer 1 · edited Mar 10 '21 at 22:55

0

    select 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME([schema_id])) + '.' + QUOTENAME([name])
        + ' ADD %COLUMN_NAME% DATATYPE NOT NULL DEFAULT %VALUE%;'
    from sys.tables

Create the statements you need with the above then run them.

edited Mar 10 '21 at 22:55

Charlieface

52,284
6
19
43

answered Mar 10 '21 at 20:38

Stu

30,392
6
14
33

ItalianStallion4215 · Answer 2 · 2021-03-11T04:05:55.483

I'd personally create a loop with dynamic SQL which gets executed as it is ran. The code below creates a temp table which is utilized for the loop which will iterate through each table listed in the temp table based on a calculated row number. The dynamic SQL is then set and executed.

Once you make the necessary changes, putting in your database name, column name, data type, and default value and you are satisfied with the results that get printed, you can un-comment the EXECUTE(@SQL) and re-run the script and it will add the new column to all your tables.

USE [INSERT DATABASE NAME HERE]
GO

IF OBJECT_ID(N'tempdb..#TempSysTableNames') IS NOT NULL
BEGIN
    DROP TABLE #TempSysTableNames
END;

DECLARE @ColumnName VARCHAR(250) = 'INSERT COLUMN NAME HERE'
        ,@DataType VARCHAR(250) = 'INSERT DATA TYPE HERE'
        ,@DefaultValue VARCHAR(250) = 'INSERT DEFAULT VALUE HERE'
        ,@SQL VARCHAR(8000)
        ,@MaxRowNum INT
        ,@I INT = 1;

SELECT '[' + DB_NAME() + '].[' + OBJECT_SCHEMA_NAME([object_id],DB_ID()) + '].[' + name + ']' AS [name]
        ,ROW_NUMBER() OVER (ORDER BY [create_date]) AS RowNum
INTO #TempSysTableNames
FROM sys.tables
WHERE [type] = 'U';

SET @MaxRowNum = (SELECT MAX(RowNum)
                 FROM #TempSysTableNames);

WHILE (@I <= @MaxRowNum)
BEGIN

    SET @SQL = (SELECT 'ALTER TABLE ' + [name] + ' ADD ' + @ColumnName + ' ' + @DataType + ' NOT NULL DEFAULT ' + @DefaultValue + ';'
                FROM #TempSysTableNames
                WHERE RowNum = @I);

    PRINT(@SQL);
    --EXECUTE(@SQL);

    SET @I += 1;

END;

I agree that a cursor might be a better choice in this case for some needs. Note **cursor** - not the which is a poor substitute for a cursor. You assume all tables are in dbo schema - which is not a good assumption. I suggest you use terminators for EVERY statement, not just some. — SMor, Mar 11 '21 at 01:45
@SMor whats the downsides of using the "temptable loop over a sequence pattern"? — Dale K, Mar 11 '21 at 03:48
@SMor - I just modified the code to not be hard coded to the dbo schema and I also added the database name to qualify the table. — ItalianStallion4215, Mar 11 '21 at 04:07

Add new column to all tables in database

2 Answers2