SQL7--Modifying System Catalogs Directly

[philosinger]

There is a server setting in SQL 7 that allows you to modify System Catalogs Directly (i.e. Edit a table name by editing it entry in sysobjects table).

I have to do some major table modifications (mainly just column renaming) and am wondering if modify the system tables will be faster, but me fear is corrupting my data?

Does anybody have any recommendations?

-Steve

 

[tlbroadbent]

Trying to rename a column by directly updating system tables is problematic because column names appear in multiple tables, in views, in stored procedures... You have to identify and change every occurrence of the column name in whichever table it appears.

If making a large number of changes, I might do the following.

1- Script all views and stored procedures that reference the tables to be changed.
2- Open each table in design mode in Enterprise Manager, change the column names and save the table. This will also change indexes, primary keys, and foreign keys.
3- Load the previously created script in SQL Analyzer.
4- Change all of occurrences in the script of the column names that were changed.
5- Run the script to recreate the views and procedures.

That is about the fastest way I can think of to do what you are trying to do. Changing system tables directly is far too complex and risky.
Terry

http://members.home.net/tlbroadbent/index.htm

 

[philosinger]

OK, I understand that I will still have to update all of my SPs, Views and Indexes if I do the renaming directly in the sys table, but what I am asking is if I do, will I take a chance of corrupting my tables somehow?

(BTW, none of the columns being renamed are indexed, and I do have a lot of SPs and Views, but I would have to update them either way).

-Steve

 

[tlbroadbent]

Everything I read indicates that ad hoc updates of system tables are not advisable. I would never choose to do so because I don't fully understand all of the relationships between system tables.

However, I just completed a simple test where I changed the name of one column by directly updating the syscolumns table. Thus far, I have not encountered any problems in accessing the table.

Despite that success, I would not recommend renaming columns in this way. I would use Enterprise Manager or the stored procedure, sp_rename, to rename columns because of the checks done that will prevent errors that could occur with an ad hoc update.
Terry

http://members.home.net/tlbroadbent/index.htm