| 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:51Edited 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. |
 |
|
|
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. |
|
|
|
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 MasterEXEC sp_configure 'allow updates', 1GORECONFIGURE WITH OVERRIDE Once you turn this on, you can make direct updates to the system tables your queries.ENJOY!!Jay |
|
|
|
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.INFLICTNew York, New York. |
|
|
|
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 |
|
|
|
chadmat
The Chadinator
1974 Posts |
|
|
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" |
|
|
|
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  .-------------------------------------------------------------- |
|
|
|
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 that2) Identify your change requirements - You have done that3) Generate and save the script for the changes - See below4) Run the script.Point 3Lets 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...HTHDavidMTomorrow is the same day as Today was the day before. |
|
|
|
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" |
|
|
|
INFLICT
Starting Member
15 Posts |
Posted - 2002-03-12 : 11:51:24
|
| Table name is MembersI need to change varchar = 15 to varchar = 16Only 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 |
|
|
|
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. |
|
|
|
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. |
|
|
|
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) |
|
|
|
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 |
|
|
|
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=13668You'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 |
|
|
|
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.INFLICTNew York, New York.
|
|
|
|
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. |
|
|
|
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 -------------------------------------------------------------- |
|
|
|
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.... |
|
|
|
Next Page
|
Changing a field in many tables at once.