CODEWITHSUNDEEP
sqlsql-serversp-msforeachtable
RHarris
RHarris

Reputation: 11197

Need to add constraint containing table name to sql table using sp_MSforeachtable

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!

Upvotes: 0

Views: 231

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280340

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:

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

SELECT @sql = @sql + CHAR(13) + CHAR(10) + N'ALTER TABLE '
  + QUOTENAME(SCHEMA_NAME([schema_id])) + '.'
  + QUOTENAME(name) 
  + ' ADD ChangedBy NVARCHAR(100) NOT NULL;
ALTER TABLE '  + QUOTENAME(SCHEMA_NAME([schema_id])) + '.'
  + QUOTENAME(name) + ' ADD CONSTRAINT DF_' + name 
  + '_ChangedBy DEFAULT (SUSER_SNAME()) FOR ChangedBy;
  UPDATE ' + QUOTENAME(SCHEMA_NAME([schema_id])) + '.'
  + QUOTENAME(name) + ' SET ChangedBy = N''pre-existing'';
  ALTER TABLE ' + QUOTENAME(SCHEMA_NAME([schema_id])) + '.'
  + QUOTENAME(name) + ' ALTER COLUMN ChangedBy NVARCHAR(100) NOT NULL;'
  FROM sys.tables WHERE is_ms_shipped = 0;

PRINT @sql;
--EXEC sys.sp_executesql @sql;

(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:

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

SELECT @sql = @sql + CHAR(13) + CHAR(10) 
+ N'EXEC sp_rename N''' + c.name + ''',N''DF_' + t.name
+ '_ChangedBy'', N''OBJECT'';'
FROM sys.default_constraints AS c
INNER JOIN sys.tables AS t
ON c.parent_object_id = t.[object_id]
WHERE c.name LIKE 'DF[_]%[_]Change[_]%'
AND LOWER(c.[definition]) LIKE '%suser%';

PRINT @sql;
--EXEC sys.sp_executesql @sql;

These scripts both assume you don't have silly object names, like 1 of My Friend's Cool High % Table!.

Upvotes: 0

Related Questions