CODEWITHSUNDEEP
sqlsql-server
Sajeetharan
Sajeetharan

Reputation: 222682

how to add three columns to existing tables with particular schema

I need to add three columns to all tables on database with default values for a particular schema,

EXECUTE sp_MSforeachtable  @command1 = N'ALTER TABLE ? ADD CompanyID Varchar NOT NULL DEFAULT 0'
EXECUTE sp_MSforeachtable1  @command2 = N'ALTER TABLE ? ADD TenantID Varchar NOT NULL DEFAULT 3'
EXECUTE sp_MSforeachtable2  @command3 = N'ALTER TABLE ? ADD ViewObjectID Varchar NOT NULL DEFAULT 0'

How can i mention the schema?

Upvotes: 0

Views: 211

Answers (1)

rene
rene

Reputation: 42483

You can add the @whereand parameter to restrict the tables used in the foreach sp. The where is used when selecting rows from the sysobjects table.

EXECUTE sp_MSforeachtable  
            @command1 = N'ALTER TABLE ? ADD CompanyID Varchar(50) NOT NULL DEFAULT 0'  
          , @whereand = N'AND SCHEMA_NAME(schema_id)  = ''CDA'' '

I peeked at this article and grabbed the schema_name function from example D

notice I added the length on the varchar type as stated in the comments by marc_s

Upvotes: 1

Related Questions