Is there a way to compare two databases one being an old
database and the second being a new database, I will
update the old database's objects to match the new database's
objects?
Is there SQL code that could do this easily?
How do you do this if you deal with the same scenario?
Thank you
Hi,
I want to compare the production (say prod_db) objects against development database say dev_db.
In Oracle I would run a query using "minus" that is
select table_name from user_tables@prod_db
minus
select table_name from
user_tables@dev_db
what is the equivalent sql in t-sql(or are there any GUI tools to do this).I understand that from prod_db I need to create a link server to the dev_db to run such a query.
Thanks,
copernicus.
Is there an easy way to compare the contents of objects between 2 different databases? For example, say I had 2 databases, My_DB_1 and My_DB_2, each with a SEC_User table. Say I wanted to do an object-to-object comparison between databases to see if there were any differences. Here's some sample SQL:
use My_DB_1
select * from sys.dm_sql_referencing_entities('dbo.sec_user', 'object')
use My_DB_2
select * from sys.dm_sql_referencing_entities('dbo.sec_user', 'object')
Say that the result sets above both returned a SEC_GetUser sproc object ref. Is there a way to write SQL that will compare the SEC_GetUser sprocs (and other objects in the above rowsets) from both databases? For example, if My_DB_1.SEC_GetUser returns an extra column in the result set than My_DB_2.SEC_GetUser then I'd like my comparison SQL to return a single column "IsDifferent" with SEC_GetUser as a row....
Is there a stored procedure to allow me to compare twoSQL objects? In my case for example I want to comparetwo stored procedures on two different databases?If there is no SP that does comparison, would there beany code in SQL DMO that does this?How does someone learn SQL DMO? Does SQL Server2000 have by default DMO learning material or I have tosearch for books?Thank you
View 3 Replies View RelatedHi,
I would like to compare an object inside a local and remote db and I would like see if any changes has been made to the object structure (table) since the last update.
Thanks
Hi,I have a field TestDescription in tableA in DatabaseA. The data in this field is coming from three different fields in three different fields in DatabaseB.Is there a way to write a script to check if the length of this field TestDescription is greater than length of all those three fields in DatabaseB?Thanks
View 1 Replies View RelatedIs there a way by which I can compare 2 databases?...I mean get to know, the differences in the 2 databases, so I can update the other, accordingly....?
Any suggestions would be appreciated.
Thanks in advance:-)
I have two databases - I will call them database A and database B.
I want to run some code on database A, and then check if database A became identical to database B.
How can I compare two databases? I want to compare them in terms of structure, not content.
Any suggestions? Thank you!
I have two databases - version 1 and version 2
I have the same table in both versions but the number of columns has changed from version 1 to version 2.
I can get the list of columns with:
select colid, name, col_text from _SCHEMA
where table_name like
'tableA' order by colid
How can I use something like this to compare tableA in dbo.1 to tableA in dbo.2 and just return the columns that exist in dbo.2 that aren't in dbo.1?
I thought something like this might do it, but I get major errors in execution:
use dbo.2 select colid, name, col_text from _SCHEMA
where table_name like
'tableA' and
name not in (use dbo.1 select name from _SCHEMA
where table_name like
'tableA')
order by colid
Hi
I want Tom Compare two databases to see if there are stroed procedure table that exist in one database
but not exist in the other.....
how do i do it?
Thanks
The following thread worked fine and i used this one for copying from one database to another database.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2494847&SiteID=1
Now one more question i would to confirm based on the above thread.
Suppose i have 2 records in database1.dbo.Department and 6 records in database2.dbo.Department.
I want to copy 2 records in database2.dbo.Department and 6 records in database1.dbo.Department.
It is just like comparing two databases.
Do i need to follow the same thread for copying databases which is above. I mean to say the same query.
How can i write script for comparing databases in terms of both schema and data.
Regards
Kashif Chotu
How to compare db2 and sql7 database using VB script
View 2 Replies View RelatedCan someone recommend what SQL Database compare tool to useand why?Thank you
View 1 Replies View RelatedHello,I wish to see if the tables from database A server A1 exist in databaseA on server B1.I setup a linked server from my first server (robertcamarda) to atarget (cognos-dev)This works from robertcamarda:select count(*) from [cognos-dev].ds_v6_source.dbo.stdmasand this works:select * from ds_v6_source.information_schema.tables order bytable_namebut this wont work:select * from [cognos-dev].ds_v6_source.information_schema.tables orderby table_nameError:Msg 117, Level 15, State 1, Line 1The object name 'cognos-dev.ds_v6_source.dbo.information_schema.tables'contains more than the maximum number of prefixes. The maximum is 3.I want to do something like:select * from ds_v6_source.information_schema.tableswhere table_name not in (select table_name from[cognos-dev].ds_v6_source.information_schema.tables order bytable_name)so I can see of the sql server (robertcamarda) has any missing tablesthat exist on the server (cognos-dev)TIARobSQL Server 2005 Enterprise
View 3 Replies View RelatedHello all!My question is: how to compare two database structures if I only havetheir DDL files? As the result I would expect an sql-script upgratingone database structure to another.The most 3rd-party tools I tested require connection to both databases.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedHi all,
From SQL Server 2000 Import wizard if we use Copy Objects & data between SQL Server Databases it'll copies tables with primary key. Similar to this what is the alternative method in SQL Server 2005.
Please suggest me to solve the problem
Thanks in advance
Karna
Hi,
I need to merge tables from several databases. Before the merge the table contents must be checked to rule out any duplicates.
Is there a most efficient way to do it? Thanks to you all.
How can I create a database link so that I can check data between 2 similar
SQL Server 7 databases ?
A customer has messed up while moving their databases. After working for a week they found that data is missing in the database.I have two backups, one from the old server and one from the new server today, they have been working in the new one for a week.
I need to compare these two databases and then update the new database with all data that is in the old one but not in the new database. Join the data in the two databases so to say. Both databases are from the same application so they use the same users, schema and so on.
Copy objects and data between SQL Server databases
"
Display the Select Objects to Transfer dialog box, where you can specify both objects and data to copy, if both the data source and destination are Microsoft® SQL Server™ databases. The objects you can transfer include tables, views, stored procedures, defaults, rules, constraints, user-defined data types, logins, users, roles, and indexes. You can transfer objects only between multiple instances of SQL Server version 7.0, from an instance of SQL Server 7.0 to an instance of SQL Server 2000, and between multiple instances of SQL Server 2000.
"
can I apply "Copy objects and data between SQL Server databases"
to run in two different sqlserver 2000 ( not an instance ) . what I mean is I have two different sql servers located in two different locations( I am not using an instance installation) can I still run the copy and get an identical database in both servers.
Q2. if I have two sql server 2000 with different collations (one is binary and the other is the default) will I be able to run the copy wizard and still have an identical copy of sql server in both servers.
I personally tried to run the copy wizard and IT NEVER WORKED FOR ME and I really do not know the reason.
Thanks for your input.
ali
I need to compare columns in tables on 1 database on one server versus the same on a 2nd server.
I'm looking for added columns in dbase 1.
Is there a system T-SQL script that can be used for this?
Is there any way to compare two similar databases (A & B) stored procedure. I have to find stored procedure in second database B with respect to the difference.
View 7 Replies View RelatedHello,
This request is for sql7 on nt4.
I am searching for a sp that can generate the entire objects on all my databass in the server. Something like the 'Generate sql scripts' for sql7 but i need it to be capable to run as a scheduled job and automaticly enters in all my databases in the server.
I need to take sql scripts for all my development databases every night. I have something like 80 databases in development on the server.
Thanks
I have 2 tables. They both have the same fields [OFO_Code] and [Description]
Table 1 is Called Qualification and Table 2 OFO_Code. The second table is a lookup table. The info in Table 1 's description column in some places does not match that of Table 2.
I am trying to loop through these tables to Update the info in Table 1's description to match the correct description in Table 2.
Their are 1417 items in Table 2 and 77000 items in table 1.
There are NO errors when executing, just no update
Here is my code so far:
Code:
Update Qualification SET [Description] = B.Description FROM Qualification A INNER JOIN OFO_code B ON A.OFO_CODE = B.OFO_CODE WHERE A.OFO_Code IN ('" & OFO & "' )"
ans writes "I’m using SQL Server
• I’ve got 3 tables: tblOne, tblTwo, tblThree
• Using the stored procedure(let call this stored procedure sp_Result) I generate result using all the above tables(tblOne, tblTwo, tblThree).
• The problem is, it takes too long to execute(around 15 sec), which is not acceptable as I’m using in the result in the web application
• I’m thinking of scheduling to run the above stored procedure to run once every hour and write the result in a new table, tblResult.
• now the user only queries in the tblResult tables. Since it is a straight forward query it will take less than 1 sec. Perfect !!!
• Now the tblResult already exist, and the user are queries only to this table.
• How would I compare the result produced by stored procedure sp_result and the table tblResult?
• How would I update tblResult with the produced result?
• Would be a good idea to write the result in a new table called temp, then drop the table tblResult and then rename the table temp to tblResult?"
Hi,I have three columns and want to update the third column based on the comparison of first two columns in the same table.For instance:Col1 - val1, val2, val3, val4, val5......Col2 - NULL, NULL, val1, NULL, val2....Col3 - TRUE, TRUE, FALSE, FALSE, FALSE....Thanks for any help.
View 4 Replies View RelatedI compare two table with JOIN statement. Now I would like to update one of them base on result. How it to do?
View 2 Replies View Relatedwe run 2008 std edition (I believe r2 on this server, will double check) and I need to find a needle in a haystack.
I am looking at two tables and would like to learn which, if any objects on same db updates these tables.  I'd even live with any references to these tables. I already know sql agent is out of the picture. I'm looking for a static approach, not a monitoring one because its possible updates don't occur till month end.
Is there a way in sql server to query the answer to such a question using dmvs or something like dmvs?  What if I get bold and want to search all db's on this server for objects that update my two tables even across dbs?
In the mean time, i'll look at scripting all triggers and procs , and doing a search on these table names in the scripts.Â
I don't know that I've ever seen a way to script all triggers before. Procs I can easily script from the object explorer details. But I'm not sure if in explorer details--> server objects --> triggers does what I hope it does.
please help newbieI need to create a lot of objects the same type (let's say: schemas)I wish to use paramerized block in loop to do so.- how to put names of my objects to such control-flow?belss you for help
View 5 Replies View RelatedHi,
I develop ASP.NET applications and use MS SQL 2000 Enterprise.
Each customer has one database and one custom design application. And, there is a gateway and a central database to get data from all databases.
Supposed there are 10 DBs. I want the data in those 10 databases updating to the central database directly.
So, the gateway can gather all data of 10 DBs from a central DB.
Now, my question is how should I do it in MS SQL 2000? Can I do it with a stored procedure (SP)? If so, how does the SP like?
Or, SP can only perform on it's own DB, but not execute across DBs? If so, how can I do that?
Thanks for advice
I am new to programming in SQL and in VB. I use VWD to create web applications with a Hosted SQL database, locally though the databases are created in SQLExpress through Visual Web Developer.
I have used the SQL Publishing Wizard to upload my local data to the web sql server. But, I'd like to have the same local database as is on the web, which is being updated by users, both data and user / membership data but am not sure how to create an easy, or even programmatical way to update both databases periodically.
Thank you for your help.
Cjsteury
Hiya!
I'm developing an app. using SQL Server 7 (as the back end) for a company supporting approx. 200 unrelated clients, all in different locations. There is no LAN or WAN connection between them, so we'd probably need to use TCP/IP. The problem is as follows: We have dynamic reports in our app. which are run based on data in two tables. Whenever we add a new report we'd like to send it to all of our clients i.e. update their tables to reflect the additional rows of new report info. How would this best be handled, by DTS or Replication or BCP? And how could it be done with either?
Thanks,
Sarah
I have 50 MSDE SQL2k servers, each server has around 10 customer databases.
There are 5 stored procecures need to update to 50*10 = 500 databases.
These 5 stored procedures each has many 'Go' keywords and 4 of 5 with more than 8000 characters.
What might be the best way to loop execute them automatically, instead of
isql/w to each database connection to run the script?
I had bumpped by 'Go' keywords error and limitation of max varchar of 8000 error.
thanks for the help
David