Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Changing a field in many tables at once.

Author  Topic 

INFLICT
Starting Member

15 Posts

Posted - 2002-03-11 : 14:55:46

Any way, I'm trying to run a query that will affect 64 tables. What I'm trying to do is change the column length from 15 to 16. This query will affect a large amount of data on our many tables.

Here is the query I am trying to run.

Update informationschema.columns Set Character_maxlength='16' where TABLECATALOG = 'pubs' and Tableschema='dbo' and Character_maxlength='15' and Columnname='columnname'


But this query doesn't work, is there a way I can change just the maxlenght field and fix all 64 tables without crashing the whole system? A programmer warned me if I use this query during the day it could be hazardous.

Thanking you in advance..I apologize for the comments I made that were not so smart.

Edited by - INFLICT on 03/11/2002 15:44:51

Edited by - INFLICT on 03/11/2002 15:56:58

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-03-11 : 15:11:40
inflict,

usually tech support people gauge the sophistication level of the individual they're speaking with in the first few minutes, and treat them accordingly. when folks on this board direct you to BOL, it's no different ...

one clear sign you don't have a handle on things is your assertion that the query "doesn't work". what do you mean? executes successfully but doesn't do what you want? returns an error? remember that no one here is clairvoyant; we can't see inside your scattered mind and intuit the real meaning behind your questions.

Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-11 : 15:13:40
The reason we keep telling you to read Books Online is BECAUSE YOU NEED TO. You ARE NOT getting vital information out of it that you need in order to accomplish the work you need to do. If you have any desire to get more out of SQL Server, you NEED to refer to Books Online and learn how to use it properly, and learn how the SQL Server software works. You're not giving yourself or the documentation a chance, and you will never learn anything if you continue to ignore it.

The reason your query does not work is that the INFORMATION_SCHEMA views cannot be updated. You would know this IF YOU READ BOOKS ONLINE. Their function is to provide system information only. You would know this IF YOU READ BOOKS ONLINE. To modify a table's structure, you need to use the ALTER TABLE command, which you would know IF YOU READ BOOKS ONLINE. And you should have that "programmer" read it to, because he/she also doesn't understand how the software functions.

A word of advice: if you want our help in the future, you need to get off of this kick that we'll somehow solve all of your problems for you. Help yourself first: READ BOOKS ONLINE AND LEARN SQL SERVER.

Go to Top of Page

Jay99

468 Posts

Posted - 2002-03-11 : 15:27:12
You could create a series of ALTER TABLE statements. (See BOL for details).

You can make it a bit easier on yourself by doing a little SQL-FROM-SQL by using the undocumented (in BOL) sp_MSforeachtable stored procedure. Search this site for more info on sp_MSforeachtable.

If that isn't appealing to you, there is a configuration option called allow updates.


USE Master
EXEC sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE


Once you turn this on, you can make direct updates to the system tables your queries.

ENJOY!!

Jay
Go to Top of Page

INFLICT
Starting Member

15 Posts

Posted - 2002-03-11 : 15:32:54
Jeesh you guys are harsh, I do read BOL but I don't know the terminology. Give me a break I'm just starting out I don't know what constraints and strings are. Basically I just got this job because I'm a single father and the boss thought I could learn. I can't learn SQL in a day and I have to use it everyday so I have problems all the time I thought learning just a simple update or search for a member meant that I really was doing well considering that I knew nothing 2 weeks ago. I don't have schooling and I don't have a pc at home. So please if you want me to learn more how do you suggest is the quickest way for me to learn. I've tried reading books but they only cover the basics which I know already they don't go into complicated situations that you run into in the work place. When I go to BOL I just can't find through numerous different searches the information I am looking for. I'm not being a Dick I'm just trying to support my son and keep this job this is the first time I've ever got paid this much in my life all I need is a chance so please, give me a break.

INFLICT
New York, New York.

Go to Top of Page

chadmat
The Chadinator

1974 Posts

Posted - 2002-03-11 : 15:56:32
Inflict,

I don't think they were being harsh. They were being truthful. I work for MS, I supported SQL Server for over a year, and now I am a Microsoft consultant specializing in SQL Server, and I reference BOL EVERY DAY!

The best way to learn is to take an "Administering SQL Server" class. You might also want to take a database theory class, so you know what the terminology is, and the theory is. So when someone says, "you need to normailze these tables", you know what they mean.
Depending on how detailed you want to get, the 'Inside SQL Server' series is very good, but they are all very similar, so probablly just 2000 will do (If you are using 2000). I'll let Rob tell you about his favorite book, so he can get the all important kickback


I'm glad you are trying to learn, and this forum is a very good place. If you ask questions, typically you will get a quick and accurate response. Sometimes we reference BOL, but I have never seen a RTFM response. We have all been where you are, not really knowing everything, but patience and a willingness to learn will get you there.

Keep it up, you'll be a guru in no time.

-Chad

Go to Top of Page

chadmat
The Chadinator

1974 Posts

Posted - 2002-03-11 : 21:58:01
quote:

Thanks, Chad do you have any idea where I can take these classes or if I can
take them online?



You can find a "Administering a Microsoft SQL Server 2000 Database"
course near you at the following link (Sorry, it's long):

http://www.microsoft.com/traincert/training/find/courseresults.asp?searchType=1&searchInput=2072&Attempt=&submit1=Search&Product=151%2CMicrosoft+SQL+Server+2000&audience=All&Delivery=All&Language=25%2CEnglish&Attempt=&pageid=training

As far as a DB theory class, I got that in college. I would bet a local college, maybe a community college would offer some DB classes, and maybe even online, depends on the school.

-Chad

Go to Top of Page

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-11 : 23:13:54
quote:

give me a break



OK - now back to the problem. Faced with your timeframe, if I were you I probably would have manually altered the 64 tables by now in EM (enterprise manager) -I imagine it would take you about 10-15 minutes.

However, you can do better than that pretty easily.

For example to change the column COLM from my table TABL, from nvarchar(50) to nvarchar(51)
I can do
alter table TABL alter column COLM nvarchar(51)

As long as the new type is compatible, and larger, you won't lose any data.

Your next issue is to then loop through all the tables you want to change, and change those columns. There are a few ways to do this, but without knowing more about your database, I'd be reluctant to suggest anything other than suggesting you manually type and re-run the code 64 times.

There are other ways, however, so if you can post back some specifics about which tables are to be changed - ie give me a few examples, and which columns -
ie are all columns of length 15 to be changed to 16? or just ones named "bla bla bla".

Look forward to helping you out....



--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"
Go to Top of Page

Nazim
A custom title

1408 Posts

Posted - 2002-03-12 : 00:54:41
i think u have learnt your lessons (though a hard way).

Comign to your original question.

i just wrote a sp http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=13752 . follow this, it might help you.

One more thing, if you really want to help yourself . apart from reading books. follow Sqlteam.Com . check for earlier posts . and dont mind some harsh tones. they say it for your own good.

And Keep Asking .


--------------------------------------------------------------
Go to Top of Page

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-03-12 : 01:17:11
Can I say that you INFLICTed these respones?

Anywho....

I think rrb is on the right track...

Believe it or not most programming is repetitive, repetitive, repetitive, repetitive, repetitive...

When it comes to large changes like this people always seem to want to do it in one go. That sounds like a good idea, but can cause unwanted results (Errors).

Personally, I like to script everything..

I would approach your problem this way...

1) Make a backup - I hope you have done that
2) Identify your change requirements - You have done that
3) Generate and save the script for the changes - See below
4) Run the script.


Point 3
Lets assume it is the same column name.....YourColumn Char(16)

Run this code in the Query Analyser..

Select 'Alter Table ' + Table_Name + ' Alter Column YourColumn char(16)' from Information_SCHEMA.Tables


From this results windows paste the results back into a NEW execution window.

Delete the statements for tables that are not involved eg. dtproperties, syscolumns etc...

SAVE this file!!!!!

You now have a complete transcript of the changes your about to make...

HTH


DavidM

Tomorrow is the same day as Today was the day before.
Go to Top of Page

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-12 : 01:45:47
quote:

I think rrb is on the right track...


Have to agree with that!

quote:

Personally, I like to script everything..


No arguments here!

quote:

You now have a complete transcript of the changes your about to make...



Sounds too logical for this late in the afternoon. I'm going home to cry.

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"
Go to Top of Page

INFLICT
Starting Member

15 Posts

Posted - 2002-03-12 : 11:51:24
Table name is Members
I need to change varchar = 15 to varchar = 16

Only the columns named "blah blah blah" are going to be changed from 15 to 16.

We have a few million members won't these changes affect the database while we have traffic on the site potentially crashing the database?

So it looks like my query will be something like this.

Alter Table 'Tablename' Alter Column 'Columnname' varchar(16) ????




Edited by - INFLICT on 03/12/2002 11:54:22
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-12 : 12:07:01
quote:
We have a few million members won't these changes affect the database while we have traffic on the site potentially crashing the database?


Yes, especially if the column you are altering is indexed; the indexes will be rebuilt and it could take a while (if you don't have indexes on this column then it's not an issue).

This kind of operation is best done during a maintenance window when the database is either off-limits or has relatively few people accessing it. In any case, the entire table will be locked during the ALTER TABLE statement and will block users.

Do you have to do all 64 tables in one operation? Can you do them in smaller batches over a longer period? It makes more sense IMHO to do it this way because it will be less of a strain on the system and the users.

Go to Top of Page

INFLICT
Starting Member

15 Posts

Posted - 2002-03-12 : 12:16:44
No I can do them in more than one operation but some of the tables are so large, I'm going to have to have this query run at a very early time in the morning.

Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-12 : 12:35:29
Do the largest ones first and get them out of the way. You may also want to drop all of the indexes on the tables and add them back afterward, it might speed up the ALTER TABLE (can't hurt anyway, it's just a little more typing)

Go to Top of Page

chadmat
The Chadinator

1974 Posts

Posted - 2002-03-13 : 00:17:33
Wow,

I set Rob up for a plug opportunity, and he totally missed it. Your slippin Rob

Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-13 : 08:32:23
Ahhhhhh, but Inflict already has Ken's book:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=13668



You're right, already having the book is no excuse for not plugging it, so:

The Guru's Guide To Transact-SQL by Ken Henderson

...is an indispensible tool on the subtle yet powerful abilities that SQL Server possesses, and anyone reading this who HASN'T bought it yet should! You can get it through the SQL Team bookstore:

http://www.sqlteam.com/store.asp

Go to Top of Page

poorme
Starting Member

2 Posts

Posted - 2002-03-14 : 09:32:09
quote:

Jeesh you guys are harsh, I do read BOL but I don't know the terminology. Give me a break I'm just starting out I don't know what constraints and strings are. Basically I just got this job because I'm a single father and the boss thought I could learn. I can't learn SQL in a day and I have to use it everyday so I have problems all the time I thought learning just a simple update or search for a member meant that I really was doing well considering that I knew nothing 2 weeks ago. I don't have schooling and I don't have a pc at home. So please if you want me to learn more how do you suggest is the quickest way for me to learn. I've tried reading books but they only cover the basics which I know already they don't go into complicated situations that you run into in the work place. When I go to BOL I just can't find through numerous different searches the information I am looking for. I'm not being a Dick I'm just trying to support my son and keep this job this is the first time I've ever got paid this much in my life all I need is a chance so please, give me a break.

INFLICT
New York, New York.





Go to Top of Page

poorme
Starting Member

2 Posts

Posted - 2002-03-14 : 09:38:30
I agreed SQL Server people are harsh. I start working for my company 1 year ago without knowing anything about SQL Server. (I have other database experience.) They didn't send me to any training class and expect me to know everything. Whenever I have a question, the DBA and my coworkers tell me to read BOL.

Go to Top of Page

Nazim
A custom title

1408 Posts

Posted - 2002-03-14 : 10:33:46
What is happening .

Poorme if want to improve urself and want to use Sql Server, then take their word ,Read BOL. its bible for Sql Server.

Regarding Questions , keep them coming here. Apart from giving you suggestions on reading BOL , v do provide useful hints.

SqlTeam will make you into HappyMe , just stick to this site



--------------------------------------------------------------
Go to Top of Page

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2002-03-14 : 10:34:12
I'm afraid that the problem is not SQL SERVER PEOPLE.

It sounds as if your company is , in the same vein as INFLICT's, dumping you in the creek without a paddle. Training is good - but attitude is more - imho. People learn more from trying, than asking...

I think that the advice given to INFLICT was good, and I'm sure that he will take it in the light it was intended. That can only be to his benefit....

Go to Top of Page
    Next Page

- Advertisement -