?
Solved

alter table form existing table in mssql

Posted on 2006-11-29
9
Medium Priority
?
284 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 200 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

777 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