Object Packages are a great way to collect together disparate artifact records, for whatever reason the package creator decides. By the same token, Status Flags are a great way to identify artifact records with special attributes, or that require special attention. A great time saver would be if you could mass assign a status flag to the contents of an object package. You can, with this little SQL script…

Simply set @myFlagName, @myPkgName, and @myLoginName with the name of status flag, the object package, and the package owner (As seen in the Owner field of the Package Explorer), respectively.

DECLARE @myFlagName NVARCHAR(32), @myPkgName NVARCHAR(64), @myLoginName NVARCHAR(32)

SET @myFlagName = N'Status_Flag'
SET @myPkgName = N'Package_name'
SET @myLoginName = N'Login_Name'

/* Do not edit anything beneath this line */
DECLARE @myFlagID INT, @myPkgID INT

SELECT @myFlagID = FlagID
FROM FlagLabels
WHERE FlagLabel = @myFlagName

SELECT @myPkgID = PackageID 
FROM Packages 
WHERE TableID = 108
AND Name = @myPkgName
AND Owner = @myLoginName

DELETE FROM StatusFlags
	WHERE FlagID = @myFlagID
	AND ObjectID IN ( SELECT ID FROM PackageList WHERE PackageID = @myPkgID )
	
INSERT INTO StatusFlags ( ObjectID, FlagID, OnOff, LoginID)
	( SELECT ID, @myFlagID, '1', @myLoginName
	FROM PackageList WHERE PackageID = @myPkgID )

What scenarios do you have where you need to batch assign status flags?

Thanks for taking the time to read my post – I hope you found it helpful.
Leave feedback in the comments below.

If this post save you time, then consider sharing it with your friends and colleagues. If it saved you some money, then please consider buying me a coffee. If you want to know when I publish new content, then subscribe to my feed. Your support encourages me to write more. Thanks!