CODEWITHSUNDEEP
sql-serversql-server-2008
Jeff
Jeff

Reputation: 2871

Changing field length on multiple tables quickly

I have inherited a database design that has a somewhat important flaw: the primary key of a core table (referenced by roughly half the other tables, many of which have constraints which check it) is a varchar(20).

This boggled my mind for a bit, but the rationale behind it is sensible. The number (and to date, they've always been numbers) is a combination of two fields of data (which are guaranteed to be unique when combined) but frequently starts with one or more 0s.

I'm sure there are better ways to solve this problem, but a complete change of the database schema is out of scope at the moment.

The problem is that we have a new client who has a combined ID which is 25 characters.

When we created the database for this client's site, I updated all the fields. This took me most of a day - I haven't done a lot with SQL at the table-creation/modification level, there are roughly 25 tables which reference this field (and a dozen or more stored procedures which take it's value in as a parameter) and each table has numerous constraints that have to be dropped so the table can be edited.

In total, updating the one database took me almost a full work day. It was long, boring work, yet a single typo could have broken the database schema. I had to write a script that dropped constraints from the tables in the right order, updated all the fields, and re-added all the constraints.

Is there an easier way to do this? I have to change the field length of a primary key that is also a foreign key on 20-25 tables, and I have to do it for roughly 8 different databases. The constraints have subtly different names on each database, so my script for the one won't work for the others.

Upvotes: 0

Views: 1206

Answers (1)

makciook
makciook

Reputation: 1555

There are two ways.

First

is not recommended and very "intrusive".

All schemas are stored in table sys, where you can find tables, columns and other tables. You have to prepare an UPDATE query to change the needed values - this will affect the tables, schemas, columns and whatever you want directly.

It's not trivial though, as noone is allowed to UPDATE/DELETE sys tables. There are some workarounds, but I didn't try any of them on my own.

Second

From information schema of your database collect the tables you want to change:

SELECT 'ALTER TABLE ' + TABLE_NAME + ' ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE + '(100)' AS [Edit],
    TABLE_NAME,
    COLUMN_NAME,
    CASE
        WHEN CHARINDEX('numeric',DATA_TYPE) <> 0 THEN DATA_TYPE + '(' + CAST(NUMERIC_PRECISION AS varchar) + ', ' + CAST(NUMERIC_SCALE as varchar) + ')'
        WHEN CHARINDEX('varchar',DATA_TYPE) <> 0 THEN DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH as varchar) + ')'
        ELSE DATA_TYPE
    END AS TYP
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE TABLE_NAME like '%'
AND TABLE_SCHEMA like '%'
AND CASE
        WHEN CHARINDEX('numeric',DATA_TYPE) <> 0 THEN DATA_TYPE + '(' + CAST(NUMERIC_PRECISION AS varchar) + ', ' + CAST(NUMERIC_SCALE as varchar) + ')'
        WHEN CHARINDEX('varchar',DATA_TYPE) <> 0 THEN DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH as varchar) + ')'
        ELSE DATA_TYPE
    END = 'varchar(50)'

Adjust it, run it, copy [Edit] column and paste it into SSMS and run again.

Upvotes: 1

Related Questions