How do I generate scripts to drop indexes, constraints, and keys for a database to change columns from int to bigint?

Question

I've worked myself into a corner, and I'm not sure how to handle this.

I have a large set of databases with generally the same structure. Each database has a list of ID columns that are currently int that i need to convert to bigint. Presently, i'm creating scripts by hand to remove all of the constraints, indexes, and keys before modifying the columns to bigint and putting all of those objects back.

The problem I'm running into with slightly modifying the same script over and over is that I wind up losing objects or creating new objects along the way, which i'm not supposed to do. There's a few catches to this:

1) Some indexes are used in custom logic on a database, so they'll be present in some databases but not all. So, losing indexes is very bad.

2) Disk space is a major concern, so I need to make sure I don't include additional indexes (which apparently cost this?)

3) There's a lot of logic that depends on keys and constraints working, so I can't risk losing any of these.

Is there a way to create scripts for this, so I don't have to do these comparisons by hand and risk losing or adding objects that shouldn't be there?

Answer 1

The DDL to make the change is just a text string. The necessary meta data is in the relational database already. This meta data can be queried by SQL. SQL can produce a string as output.

Put this together and you get a meta-SQL-generator ... thing. Something like this:

select
    'ALTER TABLE ' +
    OBJECT_SCHEMA_NAME(c.object_id) +
    '.' +
    OBJECT_NAME(c.object_id) +
    ' ALTER COLUMN ' +
    c.name +
    ' bigint ;'
from sys.columns as c
inner join sys.objects as o
    on o.object_id = c.object_id
where c.name = 'id'
and o.type = 'u'

You may need to add some square brackets, depending on your naming convention.

Write a similar query for each object type you're dealing with - constraints, indexes etc. There is a sys. or INFORMATION_SCHEMA. table to cover all of them. At the end you'll have a script which will capture the current schema. Run it once per target database. Pipe the output to a file (or in SSMS use "results to file" setting). That file will be a new SQL DDL script which will effect your change and restore the database to its previous state, including all customisations. The meta-script and produced scripts can be pushed to source control for tracking.