The Title could be a bit misleading so what is this about? After we started with our Deployments we regularly came to a point where we needed to change, add or delete certain values within the MDT Database. You might think of adding Roles to a computer on-the-fly, prestaging a new computer with some different settings, giving a local siteadmin access to a subset of his location settings etc. And you really don't want to give the Helpdesk access to the Deployment Workbench and with it access to the complete database.

In every case we had to sit down, write some custom code to do the exact task (I've published some examples on Update Computerinformation in MDT 2008 and Set or Get a single Setting from MDT Database). But as soon as you know that you have this possibility and how to achieve a certain task you get more and more ideas on how to improve the whole process. So instead of adding some chunks here and some chunks there I started writing a complete (more or less) set of Stored Procedures, which gives access to almost every part of the MDT Database while persisting the integrity of the database and also ensuring some business logic.

This post will more or less just list all available Stored Procedures with a short explanation on what it does and which parameters it will take. Soon to follow is a .Net API and an appropriate webservice which will give additional access to these stored procedures and can be implemented in further custom deployments. I just haven't finished the "Make it nice and tidy" step yet :-)

Also even if every Stored Procedure has been tested I use only a fraction of it in my current Deployments, so use this at your own risk. I will not be responsible for any damage or problems on your environment and I still consider it Beta. Be sure to really test everything you want to use before putting it into production. As I did not change anything on the original MDT Database objects, it should work with every standard implementation of MDT 2008 (Haven't tested it on BDD). Be careful if you have customized some of the original objects as you may need to apply changes to some of the procedures. Any feedback, comments, etc are highly appreciated.

OK, a rather long text already but the longest part is just about to come. Just give it a quick walk-through to see if and what it could do for you. You will recognize that a lot of the Stored Procedures will be quite similar.

• AddComputer @SerialNumber(nvarchar(255)), @AssetTag(nvarchar(255)), @MacAddress(nvarchar(50)), @UUID(nvarchar(50)), @Description(nvarchar(255))
  Adds a new computer to the MDT Database and returns the new ID. At least one value for SerialNumber/ServiceTag, AssetTag, MacAddress, UUID needs to contain a uniqe value
   
• DeleteComputer @SerialNumber(nvarchar(255)), @AssetTag(nvarchar(255)), @MacAddress(nvarchar(50)), @UUID(nvarchar(50))
  Deletes a computer from the MDT Database. At least one value for SerialNumber/ServiceTag, AssetTag, MacAddress, UUID needs to be supplied
   
• DeleteComputerByID @ComputerID(int)
  Deletes a computer from the MDT Database based on the ID
   
• GetComputer @ComputerID(int)
  Returns the specified computer as a table row from the MDT Database. Columns will be ID, SerialNumber, AssetTag, MacAddress, UUID and Description
   
• GetComputers
  Returns a list of computers from the MDT Database. Columns will be ID, SerialNumber, AssetTag, MacAddress, UUID and Description
   
• GetComputerID @SerialNumber(nvarchar(255)), @AssetTag(nvarchar(255)), @MacAddress(nvarchar(50)), @UUID(nvarchar(50))
  Returns the ID for the specified computer. At least one value for SerialNumber/ServiceTag, AssetTag, MacAddress, UUID needs to be supplied
   
• UpdateComputer @SerialNumber(nvarchar(255)), @AssetTag(nvarchar(255)), @MacAddress(nvarchar(50)), @UUID(nvarchar(50)), @Description(nvarchar(255))
  Will update a Computer record with the supplied values. If the computer does not exist in the database, it will be created.
   
• UpdateComputerByID @ComputerID(int),@SerialNumber(nvarchar(255)), @AssetTag(nvarchar(255)), @MacAddress(nvarchar(50)), @UUID(nvarchar(50)), @Description(nvarchar(255))
  Will update a specific Computer record with the supplied values.
   
• SynchronizeComputers
  This will clean up the database from inconsistent values. It will remove all references to a computer which has not been deleted completely
   
   
• AddLocation @Location(nvarchar(50)
  Adds a new Location to the MDT Database and returns the new ID
   
• DeleteLocation @Location(nvarchar(50))
  Deletes a Location from the MDT Database
   
• DeleteLocationByID @LocationID(int)
  Deletes a Location from the MDT Database based on the ID
   
• GetLocation @LocationID(int)
  Returns a List of Locations from the MDT Database. Columns will be ID and Location
   
• GetLocations
  Returns the specified Location as a table row from the MDT Datbase. Columns will be ID and Location
   
• GetLocationID @Location(nvarchar50))
  Returns the ID for the specified Location
   
• UpdateLocation @LocationID(int), @Location(nvarchar(50))
  Will update a specific Location record with the supplied value
   
• SynchronizeLocations
  This will clean up the database from inconsistent values. It will remove all references to a location which has not been deleted completely
   
  
• AddMakeModel @Make(nvarchar(255)), @Model(nvarchar(255))
  Adds a new Make/Model to the MDT Database and returns the new ID
   
• DeleteMakeModel @Make(nvarchar(255)), @Model(nvarchar(255))
  Deletes a Make/Model from the MDT Database
   
• DeleteMakeModelByID @MakeModelID(int)
  Deletes a Make/Model from the MDT Database based on the ID
   
• GetMakeModel @MakeModelID(int)
  Returns the specified Make/Model as a table row from the MDT Database. columns will be ID, Make and Model
   
• GetMakeModels
  Returns a list of Make/Models from the MDT Database. columns will be ID, Make and Model
   
• GetMakeModelID @Make(nvarchar(255)), @Model(nvarchar(255))
  Returns the ID for the specified Make/Model
   
• UpdateMakeModel @MakeModelID(int), @Make(nvarchar(255)), @Model(nvarchar(255))
  Will update a specific Make/Model with the supplied values
   
• SynchronizeMakeModels
  This will clean up the database from inconsistent values. It will remove all references to a Make/Model which has not been deleted completely
   
   
• AddRole @Role(nvarchar(50)
  Adds a new Role to the MDT Database and returns the new ID
   
• AddRoleToObject @MDTID(int), @Type(nvarchar(50), @Role(nvarchar(50))
  Adds a role to an MDT Object.
   
• AddRoleToObjectByID @MDTID(int), @Type(nvarchar(50), @RoleID(int)
  Adds a role to an MDT Object
   
• DeleteAllRolesFromObject @MDTID(int), @Type(nvarchar(50))
  Removes all roles from the specified MDT object
   
• DeleteRole @Role(nvarchar(50))
  Deletes a Role from the MDT Database
   
• DeleteRoleByID @RoleID(int)
  Deletes a Role from the MDT Database based on the ID
   
• DeleteRoleFromObject @MDTIT(int), @Type(nvarchar(50)), @Role(nvarchar(255))
  Deletes a Role from the specified MDT Object
   
• DeleteRoleFromObjectByID @MDTIT(int), @Type(nvarchar(50)), @RoleID(int)
  Deletes a Role from the specified MDT Object
   
• GetRole @RoleID(int)
  Returns the specified Role as a table row from the MDT Database. columns will be ID and Role
   
• GetRoleID @Role(nvarchar(50))
  Returns the ID for the specified Role
   
• GetRoles
  Returns a Table of all Roles from the MDT Database. Columns will be ID and Role
   
• UpdateRole @RoleID(int), @Role(nvarchar(50))
  Will update a specific Role with the supplied value
   
• SynchronizeRoles
  This will clean up the database from inconsistent values. It will remove all references to a Role which has not been deleted completely
   
• AddRoleMapping @RoleID(int), @ARPName(nvarchar(255))
  Adds a new RoleMapping to the MDT Database. (See Using Roles to deploy Applications based on previous Applications for more info about RoleMapping itself)
   
• DeleteRoleMapping @RoleID(int), @ARPName(nvarchar(255))
  Deletes a RoleMapping entry
   
• DeleteAllRoleMappings @RoleID(int)
  Deletes all Mappings for the specified Role
   
• GetRoleMappings
  Returns a list of all Role Mappings
   
• GetRoleMappingsForRole @RoleID(int)
  Returns a list of all Mappings for the specified Role
   
  
  (I've added a custom PackageIdentity table to the MDT Databases similar to RoleIdentity which can store a unique list of SMS/SCCM Packages. This makes it easier to use it for Dropdowns, update them, etc. As this is a custom extension, it can't be used with the Deployment Workbench)
   
• AddPackage @Package(nvarchar(255)
  Adds a new Package to the MDT Database and returns the new ID
   
• AddPackageToObject @MDTID(int), @Type(nvarchar(50), @Package(nvarchar(255))
  Adds a Package to an MDT Object.
   
• AddPackageToObjectByID @MDTID(int), @Type(nvarchar(50), @PackageID(int)
  Adds a Package to an MDT Object
   
• DeleteAllPackageFromObject @MDTID(int), @Type(nvarchar(50))
  Removes all Package from the specified MDT object
   
• DeletePackage @Package(nvarchar(255))
  Deletes a Package from the MDT Database
   
• DeletePackageByID @PackageID(int)
  Deletes a Package from the MDT Database based on the ID
   
• DeletePackageFromObject @MDTIT(int), @Type(nvarchar(50)), @Package(nvarchar(255))
  Deletes a Package from the specified MDT Object
   
• DeletePackageFromObjectByID @MDTIT(int), @Type(nvarchar(50)), @PackageID(int)
  Deletes a Role from the specified MDT Object
   
• GetPackage @PackageID(int)
  Returns the specified Package as a table row from the MDT Database. columns will be ID and Package
   
• GetPackageID @Package(nvarchar(255))
  Returns the ID for the specified Package
   
• GetPackages
  Returns a Table of all Package from the MDT Database. Columns will be ID and Package
   
• UpdatePackage @PackageID(int), @Package(nvarchar(255))
  Will update a specific Package with the supplied value
   
• SynchronizePackage
  This will clean up the database from inconsistent values. It will remove all references to a Package which has not been deleted completely. Be carefull if you have added packages via the Deployment Workbench as they will not be within the PackageIdentity Table and so might get deleted if running this function!
   
• AddPackageMapping @Package(nvarchar(255)), @ARPName(nvarchar(255))
  Adds a new PackageMapping to the MDT Database. (See Technet:Deploying Applications Based on Previous Application Versions for more info about PackageMapping)
   
• DeletePackageMapping @Package(nvarchar(255)), @ARPName(nvarchar(255))
  Deletes a PackageMapping entry
   
• DeleteAllPackageMappings @Package(nvarchar(255))
  Deletes all Mappings for the specified Package
   
   
• AddDefaultSettings @MDTID (int), @Type(nvarchar(50))
  Adds a set of settings to the specified MDT object with default values. This is just to ensure that an entry exists if changing single values
   
• DeleteSettingsFromObject @MDTID (int), @Type(nvarchar(50))
  Deletes all Settings from the specified object
   
• GetSetting @MDTID(int), @Type(nvarchar(50)), @Setting(nvarchar(50))
  Returns the value of the specified setting for the specified MDT object. (See Set or Get a single Setting from MDT Database for details)
   
• SetSetting @MDTID(int), @Type(nvarchar(50)), @Setting(nvarchar(50)), @Value(nvarchar(255))
  Updates the value of the specified seting for the specified MDT object. (see Set or Get a single Setting from MDT Database for details)
   
• UpdateSetting @MDTID(int), @Type(nvarchar(50)), ...the complete list of settings
  This will update all settings for the specified MDT object. This will be rarely used and I`m not really willing to write down all 84 Settings again :-)
   
   
• AddApplicationToObject @MDTID(int), @Type(nvarchar(50), @Application(nvarchar(255))
  Adds an Application to an MDT Object.
   
• DeleteAllApplicationsFromObject @MDTID(int), @Type(nvarchar(50))
  Removes all Applications from the specified MDT object
   
• DeleteApplicationFromObject @MDTIT(int), @Type(nvarchar(50)), @Application(nvarchar(255))
  Deletes an Application from the specified MDT Object
   
   
• AddAdministratorToObject @MDTID(int), @Type(nvarchar(50), @Administrator(nvarchar(50))
  Adds an Administrator to an MDT Object.
   
• DeleteAllAdministratorsFromObject @MDTID(int), @Type(nvarchar(50))
  Removes all Administrators from the specified MDT object
   
• DeleteAdministratorFromObject @MDTIT(int), @Type(nvarchar(50)), @Administrator(nvarchar(50))
  Deletes an Administrator from the specified MDT Object
   
• GetAdministrators
  Returns a list of unique Administrator values used in the MDT Database
   
   
• AddDefaultGateway @LocationID(int), @DefaultGateway(nvarchar(50))
  Adds a new Default Gateway entry to the specified Location
   
• DeleteDefaultGateway @DefaultGateway(nvarchar(50))
  Deletes a Default Gateway from the MDT Database
   
• GetDefaultGateways @DefaultGateway(nvarchar(50))
  Returns a list of Default Gateway from the MDT Database 
   
• GetDefaultGatewaysForLocation @LocationID(int)
  Returns a list of Default Gateway from the MDT Database for the specified Location
   

*Notes: @Type can have the following values "Location", "L", "MakeModel", "M", "Computer", "C", "Role", "R"

Just download the complete SQL Script to create all Stored Procedures (Current version 4 from 12. Feb 2009). As said any feedback and help is appreciated.

Change History:

Version 1 - published 30.01.2009: Initial Release
Version 2 - published 09.02.2009: fixed typo on AddComputer, AddMakeModel and AddLocation
Version 3 - published 11.02.2009: added Procedures GetDefaultGateways, GetLocations, GetDefaultGatewaysForLocation, GetMakeModels, GetComputers, GetRoleMappings, GetRoleMappingsForRole. fixed typos in GetLocation, GetRole, AddRoleToObject, AddRoleToObjectByID, AddApplicationToObject, GetPackage, AddPackageMapping
Version 4 - published 12.02.2009. added missing AddDefaultSettings procedure.