Help with changing existing and adding new fields to tables 1

[deva1]

Gurus,

I am a beginner in SQL Server
I have three instances of SQL(TEST,QA, PROD) running on the server.I need to change a few tables in all these. Basically the change is adding some fields,changing the length of some fields.Is there any easy way I can make these changes? .What all things I need to be e care when I do this?Onething is ,database name is different in these environments, but table names and field names are same.Any help is appreciated


Thanks in advance

 

[tlbroadbent]

You can modify the tables in each database with SQL Enterprise Manager. Just open the tbales in design view.

You can also modify tables by using the T-SQL Data Definition Language (DDL) command ALTER TABLE. The SQL commands can be run from Query Analyzer, OSQL or any tool the allows you to run queries.

Example: Add a column

ALTER TABLE doc_exa ADD column_b VARCHAR(20) NULL

Example: modify a column length

ALTER TABLE doc_exa ALTER column column_b VARCHAR(30)

See SQL BOL for more details. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[SQLSister]

Some things to think about when making changes of this sort. First, how are you going to ensure the changes do not adversely affect anything else. Make sure you have a test plan and follow it. Make the changes to Development first before going to QA and Production. If you do not have a tool set up to compare the different versions of the database, you will have to keep track of exactly what you changed in order to ensure the same changes happen in all places. It is best to make the changes in the development database, script the changed objects and then apply the scripts to the other databases. This will help ensure consistency rather than using Enterprise manager in all three and maybe naming a new field test1 in one database and tets1 in another thus breaking any stored procedures dependent on the the new field being called test1.

Changes to tables after a database is in production can cause new bugs to creep in. You need to know exactly what objects you will be affecting by these changes, not only in the database, but in the user interface as well. For instance when I change a datatype, the XML that we use to call the stored procedure needs to change in the .NET interface and stored procedures that use table variables or temp tables may also need to change in order for them to still work. Triggers are a particularly difficult area when you make these changes, not only triggers on the tables changed, but triggers on other tables may be updating records in that table and may need to change as well. And you may also need to refresh or adjust any views based on these tables.

Since you are new to SQL Server, you are probably not very familiar with these databases. Before you go about making these changes, I would suggest you spend at least a day familiarizing yourself with the database tables, triggers stored procedures, user defined functions,relationship diagrams and user interface. This will give you a better understanding of the whole project and how much could potentially be affected by your changes. I would also suggest getting familiar with basic T-SQL syntax and how to use Enterprise Manager and Query Analyzer. A few days spent learning and understanding the basic design of your database will save you days maybe even months of debugging later. I highly recommend it.