T-SQL Procedure to Update Tables

Question

I'm not sure the best approach for this task. I have to search a database and find the sname columns in all the tables. Then pass the new value into the stored procedure which will update that reference in the tables. I can find the column with the below query:
SELECT Table_Name,
Column_Name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'AdventureWorks2014'
AND COLUMN_NAME LIKE '%address%';

Now that I've found it I want to pass the old value and then the new value:

Something like:

create proc chngsname
(@keyid int, @srvname nvarchar(120))
as
begin
update tablenames set name =@srvname where keyid =@keyid
end
go

exec chngsname 45325, 'CORRECT_NAME_FOR_SERVER'

Answer 1

Given that you are working with AdventureWorks, I take it that this is not real task, but something you have made up on your own, and I will answer from this presumption.

The answer is that you do normally do these sort of things, because there is rarely any reason to in a well-designed normalised database.

A table in a relational database is supposed to model a unique entity, and from that perspective, each table needs its own code. Yes, two tables may have columns with same name, but they are still different entities.

And the SQL language is designed from this perspective. If you have two unrelated tables, you write two unrelated stored procedures. Yes, they may at this point look the same, but as a system evolves, they may grow apart by time.

You cannot say "UPDATE @tblname" and update different tables at different time. You can build SQL statements dynamically, but that is an advanced feature and not apt for beginners.

Answer 2

This is an actual task but my thinking on it might be flawed. In any case, we have a small db that tracks the commands used for various servers.

One of our dbas wanted a stored procedure that we can use the correct a server name. The server name and ID number are in the same tables. There’s like four tables in there.

Instead of updating the table manually the dba was thinking of a stored procedure that would update ALL the tables associated with that server and it’s ID.

I could do exec sp_msforeachtable ‘update ? set sname = ‘’New Name for Server’’ where ID = 553’

Answer 3

For four tables, I would just write the four commands and be done with it. Or are these name changes being done on a regular basis? In such case, I would list the four tables and leave it at that.

Would there be 40 tables, I would certainly do something else, for instance sp_MSforeachtable.