How To Compare Objects In Two Databases In Sql 2k...
Jun 15, 2003
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.
View 6 Replies
ADVERTISEMENT
Jul 23, 2005
Is there a way to compare two databases one being an olddatabase and the second being a new database, I willupdate the old database's objects to match the new database'sobjects?Is there SQL code that could do this easily?How do you do this if you deal with the same scenario?Thank you
View 2 Replies
View Related
Jul 20, 2015
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....
View 1 Replies
View Related
Jul 23, 2005
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 Related
Feb 8, 2005
Hi,
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
View 2 Replies
View Related
Aug 23, 2005
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 Related
Sep 25, 2000
Is 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:-)
View 4 Replies
View Related
Sep 20, 2005
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!
View 5 Replies
View Related
Dec 13, 2007
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
View 3 Replies
View Related
Jul 3, 2007
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
View 4 Replies
View Related
Dec 8, 2007
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
View 4 Replies
View Related
Sep 9, 2004
How to compare db2 and sql7 database using VB script
View 2 Replies
View Related
Jul 23, 2005
Can someone recommend what SQL Database compare tool to useand why?Thank you
View 1 Replies
View Related
May 17, 2006
Hello,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 Related
Jul 20, 2005
Hello 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 Related
Jun 1, 2007
Hi 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
View 2 Replies
View Related
Apr 2, 2002
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.
View 2 Replies
View Related
Nov 20, 2000
How can I create a database link so that I can check data between 2 similar
SQL Server 7 databases ?
View 1 Replies
View Related
Feb 7, 2014
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.
View 9 Replies
View Related
Jun 3, 2001
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
View 2 Replies
View Related
Aug 12, 2015
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?
View 4 Replies
View Related
Dec 3, 2014
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 Related
Mar 15, 2000
Hello,
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
View 1 Replies
View Related
Dec 27, 2006
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 Related
Nov 20, 2013
passing serialised objects to a stored procedure for the purpose of data inserts. I see this as being a way to handle multiple row inserts efficiently.
However, in my limited use of XML data I am not so sure how to link the data when I have a dependency on another "object" within the serialised XML.
Below is a code snippet showing what I have so far.
The first insert statement works fine - but how to retrieve the identifier created by the DB - I want to use an SQL statement that finds the record in the table based on the XML representation (of the PluginInfo), allowing me to insert the ConfigurationInfo with the correct reference to the PluginInfo
DECLARE @Config NVARCHAR(MAX)
DECLARE @Handle AS INT
DECLARE @TransactionCount AS INT
SELECT @Config = '
<ConfigurationDirectory >
<ConfigurationInfo groupKey="Notifications" sectionKey="App.Customization.PluginInfo"
[code]....
View 1 Replies
View Related
Jul 22, 2007
Table MediaImportLog
column ↘ImportIndex ImportFileTime ImportSource
value ↘80507 20060506001100 815
80511 20061109120011 CRD � P.S the values type of ImportFileTime 20060506001100 → 2006 -year 05-month 06-day 00-HH 11-minute 00-second】
Table BillerChain
column↘BillerInfoCode ChainCode
value ↘750 815
value ↘81162 CRD
Table Biller
column↘CompanyCode BillerCode
value ↘999 750
value ↘81162 516
TAble DataBackup
column↘CompanyCode Keepmonth
value ↘999 6
value ↘81162 12
---------------------------------------------------
when I'm in MediaImportLog , I want use column ImportSource to compare with column ChainCode in table BillerChain ( so I get BillerInfoCode) and then use the BillerInfoCode I got to compare with column BillerCode in Table Bill ( I get CompanyCode) finally I use CompanyCode to compare with column CompanyCode in table DataBackup so I can get the company's keepmonth
How can I get the keepmonth? can I use parameters ?
thank you very much
View 3 Replies
View Related
Dec 10, 2007
Dear Readers,Is it possible, like in Access, to link to tables in other SQL databases that are on the same server? I have a query that I originally had in Access that queered from multiply databases. It did this by having those other tables in the other databases linked to the database that had the query.
View 3 Replies
View Related
Jan 24, 2006
I just restored my SQL server 2000 database on the SQL server 2005. after this i ran the Service broker sample ("Hello World") on this database by changing the AdventureWorks name to the new database name. The "setup.sql" runs fine. When i run the "SendMessage.sql" i was not getting any rows in the output (The message was not getting inserted into the queue). I checked the Service broker is enabled on this databased using the query "select is_broker_enabled from sys.databases where name = 'newdbname' " It was 1. I even tried the ALTER DATABASE SET ENABLE_BROKER. but it didnt work.
When i tried the sample on a newly created database it worked fine.
Is there any solution to make the restored database to work for service broker.
Thanks
Prashanth
View 3 Replies
View Related
Feb 8, 2000
hi from France !!!
i would like how to duplicate a database to another server with all datas, constraints, keys, indexes...
should i use sp_attach_db, dts, backup/restore, sql scripts... ???
thanks to all, nico
View 1 Replies
View Related
Aug 27, 2001
Hi, there,
I use VB to make an dll file and register on my local PC, then use sp_OACreate to create OLE object on local SQL server, it works fine. But when I register that dll on other SQL server, it dosen't work, anybody have idea about this? Thank you.
Tony
View 1 Replies
View Related
Jul 17, 2000
I see these type of objects in Books on Online. I was just wondering
if anyone knows how to use these objects, seems to have lot of good
information. Could these objects be called in VB?
--------------------------------
CreateDate Property (SQL-DMO)
The CreateDate property indicates the date and time the referenced SQLServer object was created.
Applies To
Database Object Table Object
DBObject Object TransactionLog Object
Default Object Trigger Object
Rule Object View Object
StoredProcedure Object
Syntax
object.CreateDate
Part Description
object Expression that evaluates to an object in the Applies To list
Remarks
The string returned is formatted by using the locale setting of the workstation.
Data Type
String
Modifiable
Read-only
Prototype (C/C++)
HRESULT GetCreateDate(SQLDMO_LPBSTR pRetVal);
--------------------------------------------------------
View 1 Replies
View Related
Sep 21, 2007
Anyone know how to grant a user to see all data in sys.objects view?
Thanks.
View 1 Replies
View Related
Sep 21, 2007
Anyone know how to grant a user (who is not a sa) to see all data in sys.objects view?
Thanks.
View 1 Replies
View Related
