How to write a query on database to add one column to all the tables in that database?

Question

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

baabhu SSCertifiable Points: 6205 More actions · Answer 1

Looks like home work question.

select 'Alter table ' + s.name + '.' + T.name +' Add Name varchar(5)' from sys.tables T Inner join sys.schemas S

on T.schema_id = S.schema_id

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

Undocumented and unsupported, but should work.

EXEC sp_msForEachTable 'ALTER TABLE [?] ADD newColumn int NULL'

-- Gianluca Sartori

Aiello DBA Right there with Babe Points: 784 More actions · Answer 3

Or a cursor over sys.all_objects to filter tables and execute EXEC 'ALTER TABLE ' + @youTable + ' ADD...'

-

Lic. Andrés M. Aiello

DBA MSSQL - Oracle

@AndresAiello

nigel. SSChampion Points: 11636 More actions · Answer 4

Slight variation on a previous response that doesn't require the join, and uses QUOTENAME to ensure validity of the table/schema name:

SELECT

'ALTER TABLE ' +

QUOTENAME(SCHEMA_NAME(schema_id)) + '.' +

QUOTENAME(name) + ' ADD Name VARCHAR(5);'

FROM

sys.tables