Adding a filed to all of tables and procedures without edit them one by one-CodePudding

I have more than 100 tables in the database and there are more than 500 store procedures use them, is it posible that I add a column to all tables and use in all procedures? Means not need to edit them one by one! For example adding a filed with name company and two of procedures that I expected:

select * from Reseller

change to

select * from Reseller where company='DefaultValue'

Insert into Reseller values(@name,@phone)

change to

Insert into Reseller values(@name,@phone,'DefaultValue')

CodePudding user response：

It isn't a great idea to update all fields in your database with one query, as it is too easy to provide incorrect ranges and update tables that perhaps don't need it.

On the few occasions I've needed to update several tables at once I query a list of all table names in the database I need and append the update TSQL logic to it either in the query or using excel so I can view it row by row.

SELECT CONCAT('ALTER TABLE ', Table_Name, ' ADD Company NVARCHAR(50);' )
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE='BASE TABLE'

Then, I can copy the results and run results of the select statement as a new TSQL update.

With that said, I would absolutely not alter a stored procedure using this method. It would be far too easy to overwrite the existing content and lose the logic that the stored procedure utilizes

CodePudding user response：

One option is to rename Reseller to ResellerWithCompany, then create a view Reseller that selects from it.

EXEC sp_rename N'dbo.Reseller', N'ResellerWithCompany', 'OBJECT';

ALTER TABLE dbo.ResellerWithCompany
  ADD Company varchar(50) NOT NULL DEFAULT 'default value';

GO

CREATE VIEW dbo.Reseller
AS
SELECT *
FROM dbo.ResellerWithCompany
WHERE Company = 'default value';

GO

Given that this view is very simple, it can be inserted into, updated, and deleted from. On an INSERT, as long as all columns are specified or have a default value, it will just work.

There are some things that will not work.

TRUNCATE
Any kind of DDL, such as adding columns or constraints, rebuilding indexes, must be done on the base table

db<>fiddle