-2

I need add one common column in whole database tables.Any shortcuts or query or scripts to add the one column in whole database. because near 100 tables are there in that database .Please suggest me any ideas to add columns in all tables

PrakashT
  • 883
  • 1
  • 7
  • 17
  • Maybe you can use a system table to get all existing tables and loop over that somehow. – Joakim Danielson Jun 28 '18 at 12:35
  • you can check this it may help you. https://dba.stackexchange.com/questions/160145/add-columns-to-all-tables-in-a-database-if-the-columns-dont-exist – Sourabh Jun 28 '18 at 12:41

3 Answers3

1

You can generate all queries, and then execute them:

SELECT concat('ALTER TABLE ',TABLE_NAME,' add column mycolumnname mytype;')
 FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'yourschema';
Kobi
  • 2,494
  • 15
  • 30
0

You can use a prepared statement by using the ? operator instead of the table name.

here is the code:

ALTER TABLE ? ADD column_name column-definition;

guroosh
  • 642
  • 6
  • 18
0

There's a very similar question here:

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

And yes, you can also generate a script from a query as @Kobi suggested.

SELECT 
'IF NOT EXISTS(SELECT * FROM sys.columns c WHERE c.[name] = ''COLUMN_NAME'' AND c.[object_id] = OBJECT_ID(N''[' + SCHEMA_NAME(t.schema_id) + '].[' + t.[name] + ']'', N''U''))
    ALTER TABLE [' + SCHEMA_NAME(t.schema_id) + '].[' + t.[name] + ']
    ADD COLUMN_NAME INT NULL
'
FROM sys.tables t
Proxy
  • 1
  • 1