Solved

alter table form existing table in mssql

Posted on 2006-11-29
9
251 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
  • 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.

910 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now