[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 294
  • 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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

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