How to create PK in thousands of datases where not exist

Viewed 12

I have trouble working out how to create a pk on thousands of databases. I have tried using sp_ineachdb by Aaron Bertrand, but it only works on the first database. I need that the script that finds the PK's to be created, runs against the current Db, which doesn't seem to be the case.

DECLARE @PKScript2 VARCHAR(max)='';
SELECT @PKScript2 += ' ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(obj.SCHEMA_ID))+'.'+
       QUOTENAME(obj.name) + ' ADD CONSTRAINT PK_'+ obj.name+
       ' PRIMARY KEY CLUSTERED (' + QUOTENAME(icol.name) + ')' + CHAR(13)
FROM sys.identity_columns icol INNER JOIN 
     sys.objects obj on icol.object_id= obj.object_id
WHERE NOT EXISTS (SELECT * FROM sys.key_constraints k
                  WHERE k.parent_object_id = obj.object_id AND k.type = 'PK')
      AND obj.type = 'U'
      Order by obj.name
PRINT (@PKScript2);
EXEC [master].[dbo].[sp_ineachdb] @command = @PKScript2, @database_list= '[vosk][vpb][vpbk][vsb][vsh][vst]' 

For the sake of the example, I have only used 6 databases.

0 Answers
Related