• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

alter table form existing table in mssql

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
yogesh28577
Asked:
yogesh28577
  • 5
  • 4
1 Solution
 
GokulmCommented:
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
 
yogesh28577Author Commented:
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
 
GokulmCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
yogesh28577Author Commented:
column_name,data_type ok
what about  ,column_length,column_precision
in case of integer i dont found it length
0
 
GokulmCommented:
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
 
GokulmCommented:
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
 
yogesh28577Author Commented:
ok i got column_name & length then tell me programatically how i alter another tables
0
 
yogesh28577Author Commented:
HI Gokulm
one thing also suppose my xyz & abc  tables are at different location
xyz is in pqr database & abc are lmn  database
0
 
yogesh28577Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now