I'm trying to use sp_MSforeachtable to add a new column to all my tables with a named constraint.
So far, I've done this:
USE [MYTable]
GO
exec sp_MSforeachtable 'ALTER TABLE ? ADD ChangedBy nvarchar(100) DEFAULT (suser_name()) NOT NULL'
That works except that the constraint name comes out something like: DF_TableName_Change_51EF2864 I want it to be named DF_TableName_ChangedBy
I've played around and found that PARSENAME(''?'',1) will give me the name of the table. Is there anyway to dynamically build the constraint name using this?
Example: ... CONSTRAINT ''DF_''+PARSENAME(''?'',1)+''_CreatedBy'' DEFAULT ...
(That doesn't seem to work but I included it to give a feel for what I'm hoping can be done.)
Thanks for any help!
It's a little cumbersome but you can do it by adding the column as nullable, adding the constraint, deciding what value you want to store for pre-existing rows, then making the column NOT NULL. I would also shy away from unsupported, undocumented stored procedures. I've discovered a problem with sp_MSforeachdb (with a workaround here) and it's possible this can manifest itself here as well. This is how I would accomplish this:
(Change the comment when you trust that it is doing what you expect. Note that you may not see the entire command in the PRINT output; depending on the number of tables you have, it will likely get truncated. You can use TOP 1 or an additional WHERE clause against sys.tables to see what a single table's set of commands will look like.)
You could also simply rename the constraint afterward:
These scripts both assume you don't have silly object names, like
1 of My Friend's Cool High % Table!
.