Solved

alter table form existing table in mssql

Posted on 2006-11-29
9
279 Views
Last Modified: 2008-02-26
suppose i have two database in sql server ie abc & xyz
both have same table but suppose i changed struct of xyz table then it will be check in abc tables
if found different then it will be change plz tell me routine or sql command

0
Comment
Question by:yogesh28577
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 6

Expert Comment

by:Gokulm
ID: 18043148
You have 2 tables abc & XYZ. Both have same table structure (same number of columns, same column names and same data type?). Lets say you added one more column to xyz table. After this what is it that you are trying to do?
0
 
LVL 1

Author Comment

by:yogesh28577
ID: 18043159
suppose i have two tables abc & xyz
abc has 4 fields & xyz has five fields
suppose abc fields
a ,b,c,d
suppose xyz fields
a ,b,c,d,e
now i want to alter table abc to add e fields in it
i know i will be add it by using alter tables but in this case i know e fields is added but suppose
i dont know which fields is different in both database then what can i do for that case

0
 
LVL 6

Accepted Solution

by:
Gokulm earned 50 total points
ID: 18043179
This statement will give you the additional or new column in XYZ table. you can do a reverse query to get the new column from ABC column

SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'xyz'
AND column_name NOT IN ( SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'abc')
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:yogesh28577
ID: 18043252
column_name,data_type ok
what about  ,column_length,column_precision
in case of integer i dont found it length
0
 
LVL 6

Expert Comment

by:Gokulm
ID: 18043264
SELECT column_name, data_type, numeric_precision FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'xyz'
AND column_name NOT IN ( SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'abc')

You can find more information on different data type in BOL. search for INFORMATION_SCHEMA views
0
 
LVL 6

Expert Comment

by:Gokulm
ID: 18043273
you can add the column numeric_scale to get more clear information on numeric fields

SELECT column_name, data_type, numeric_precision, numeric_scale FROM INFORMATION_SCHEMA.COLUMNS where table_name = 'xyz' AND column_name NOT IN ( SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'abc')
0
 
LVL 1

Author Comment

by:yogesh28577
ID: 18043285
ok i got column_name & length then tell me programatically how i alter another tables
0
 
LVL 1

Author Comment

by:yogesh28577
ID: 18043508
HI Gokulm
one thing also suppose my xyz & abc  tables are at different location
xyz is in pqr database & abc are lmn  database
0
 
LVL 1

Author Comment

by:yogesh28577
ID: 18043968
SELECT column_name, data_type   FROM INFORMATION_SCHEMA.COLUMNS where table_name = 'city1' AND column_name   NOt IN ( SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'core.dbo.city')
what you think about above it not give result

0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question