To generate the tsql commands that we can use to alter the primary key datatype.
My way of achieving it
Using powershell to check some of the relations between the tables. As with all other things that I am posting here, this, is far from being a final script. It is just something that you can use in order to check the level of work required to change the data type of a primary key. Sometimes in our job we are asked to perform this type of activity and although it seems like a trivial task there are hidden things that make it very difficult. I tried to show as many things that I could for which the change of datatype might fail but I am sure that depending on the database structure the script will still fail even after we will perform the steps described in the output of the script. The output of the script must be followed and changed accordingly to your requirements. In the script I changed the data type of InqNo column from int to numeric(18,0).
The script requires the instance name, database name and table name
$instancename = “UCPHMA”
$databasename = “Inquiry2012”
$tablename = “Inquiries”
The output of the script can be seen below and as you can see asks for dropping and recreating some indexes among other things.
IMP – Those actions needs to be very good planned when it comes to the production servers and big tables due to all the problems that dropping an index creates and all the problems that creating an index creates. But in case the tables are small then the impact will not be big. As with every script, first needs to be tested in a controlled environment and used only after the operation was successful there.
Very important is to check the script output before executing it.
The script can be downloaded from here