Adding a Column to All User Tables in T-Sql

Add a column if it doesn't exist to all tables?

You cannot use variables, like @tableName, in DDL. Besides, splinting the name into part and ignoring the schema can only result in bugs. You should just use the ''?'' replacement in the SQL batch parameter and rely on the MSforeachtable replacement:

EXEC sp_MSforeachtable '
if not exists (select * from sys.columns 
               where object_id = object_id(''?'')
               and name = ''CreatedOn'') 
begin
    ALTER TABLE ? ADD CreatedOn datetime NOT NULL DEFAULT getdate();
end';

Adding a column to all user tables in t-sql

There is an undocumented but well known stored procedure sp_msforeachtable:

exec sp_msforeachtable 'alter table ? add flag bit not null default 0';

Adding Columns to Multiple Tables in SQL

Yes. In mysql you have the alter table command for this purpose. Check out this page for more detailed explanation
https://www.sqlservertutorial.net/sql-server-basics/sql-server-alter-table-add-column/ .

And here is the solution for the ordering of the columns
https://www.mysqltutorial.org/mysql-add-column/

How to Add Column to an existing SQL Server table along with data

You can specify the WITH VALUES clause to apply the default constraint value to existing rows when adding the column:

ALTER TABLE dbo.LanguagePacks
    ADD LgDt DATETIME NOT NULL DEFAULT GETDATE() WITH VALUES;

How to insert a column from another table in T-SQL?

Seems like what you really want an UPDATE:

UPDATE t2
SET t2.total_programs = t1.total_programs
FROM dbo.table2 t2
     JOIN dbo.table1 t1 ON t2.id = t1.id;

If, however, you could have values of id in table1 that don't appear in table2 and you want to insert those values into table2 as well you'll want a MERGE:

MERGE dbo.Table2 AS T2
USING dbo.TAble1 AS T1 ON T2.id = T1.id
WHEN MATCHED THEN
    UPDATE 
    SET total_programs = t1.total_programs
WHEN NOT MATCHED THEN
    INSERT (id,total_programs)
    VALUES(T1.id,T1.total_programs);

Or you could write it as an Upsert as follows:

UPDATE t2
SET t2.total_programs = t1.total_programs
FROM dbo.table2 t2
     JOIN dbo.table1 t1 ON t2.id = t1.id;

INSERT INTO dbo.Table2(id,total_programs)
SELECT t1.id,
       t1.total_programs
FROM dbo.Table1 t1
WHERE NOT EXISTS (SELECT 1
                  FROM dbo.Table2 t2
                  WHERE t2.id = t1.id);

How to add a column in TSQL after a specific column?

Unfortunately you can't.

If you really want them in that order you'll have to create a new table with the columns in that order and copy data. Or rename columns etc. There is no easy way.

Add a column to existing table and uniquely number them on MS SQL Server

This will depend on the database but for SQL Server, this could be achieved as follows:

alter table Example
add NewColumn int identity(1,1)

Is it possible to Add column to multiple table simultaneously?

You probably need something like this. Check that the script does what you want before running it (adds a non null column with a default value of getdate())!

DECLARE @Dynsql nvarchar(max) 
SET @Dynsql = ''

SELECT @Dynsql = @Dynsql + '
alter table ' + QUOTENAME(SCHEMA_NAME(schema_id))+ '.' + QUOTENAME(name)  + 
' add [DateCreated] datetime not null default getdate()' 
FROM sys.tables
WHERE type='U' and object_id NOT IN (select object_id from sys.columns where name='DateCreated')

EXEC (@Dynsql)

How do I add a column to large sql server table

ALTER TABLE table1 ADD
  newcolumn int NULL
GO

should not take that long... What takes a long time is to insert columns in the middle of other columns... b/c then the engine needs to create a new table and copy the data to the new table.

---

Related Topics