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?
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
I have two table with the same name that reside in different databases. The two database have the same name, but reside in two linked servers
TABLE A: [ServerA].[ProdDB].dbo.[Orders]
TABLE B: [ServerB].[ProdDB].dbo.[Orders]
How do i find out if the two tables have the same number of columns or if the a column that exists on TableA does not exist on TableB.
Does any one have a script that could help me with this task. Thanks
I am using SQL Server 2005
I am doing some administrative tasks and need to collect some principals information from multiple instances and user databases.
I have table "dbo.instances" with list of instances.
I have databases from "sys.databases".
How can I execute the query to get principals information from "sys.database_principals" on each remote instance and database. I know that can use cursor, but not sure how to do this with multiple servers and databases.
I have two databases under two different servers.
Server1:
DB1
Table1
Server2:
DB2
Table2
I need to compare Table 1 & 2 using SQL server and how can I achieve this?
I want to compare the database structures(columns,datatypes..etc) across same databases located in different servers.
View 9 Replies View RelatedI am trying to reproduce a problem that is probably related to how a particular SQL server is configured. Is there a tool or best practice that is useful to compare all of the configuration settings between two SQL Servers (same version)?
View 2 Replies View RelatedHi all,
I have two tables A and B on two both tables have similar architectures bout contain some deferent records.
I like to compare them and find and view the differences in records.
Thanks for any help.
Abrahim
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
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.
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 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,
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.
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 RelatedIs 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 RelatedIs 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....
Hi, If you have 2 databases with the exact same table columns, and in the first database in a table column the indentidy seed starts at 1 and finishes at 32,000 can you attatch a second database so that the indentity starts at 32,001and carries on. What happens if you create a site and run out of disk space and need to attach another database which is located on another server??? I am really confused about this.Hope someone can give some links to some articles about this as I can't find any information about it.How can this be done? Thanks
View 1 Replies View RelatedHello,This is my first post, and i am looking for help about Databases in remote servers.This is my problem:1) I have a free account in "HostBasket", with 50 mb of space, and 10 mb of SQL Server... with ASP.NET 2 support --> http://www.hostbasket.com/dotnet2.shtml?LinkFrom=100602) They gave me this info for the database:Your SQL Server: sql15.hostbasket.comYour database name: myDatabaseNameYour login for the database: myLoginYour password for the database: myPasswordConnection string for ASP.NET (using ADO.NET with System.Data.SqlClient namespace):Persist Security Info=True;User ID=myLogin;Password=myPassword;Initial Catalog=MyDatabaseName;Data Source=sql15.hostbasket.com 3) Actually, i don't know what to do with that info... I mean, i have read so much info, and all people saying "their way" to do things... oh, I am so confused...4) I read in a website, this: "the database can be uploaded as a FILE saved in the App_Data folder, under the .mdf extension"I read that from : http://dotnetjunkies.com/QuickStartv20/aspnet/doc/data/vwd.aspx5) In the url given above, there is a "step by step" example... i follow it, exactly (i start a asp.net website, i create the database and the table, i show the data with the grids, etc )6) All is OK until here, i mean, when i run the website with VWD, it shows me the info OK.7) But... ¿How can i do this works on the Remote Server i described in 1) and 2) ?I know how to upload my website, in fact i uploaded a very basic one, and all was OK, but when i work with databases starts my problem...So, i am almost sure, that i have an error in the "Conecction String"... connectionString="Data Source=.SQLExpress;AttachDbFilename=|DataDirectory|MiBaseDeDatos.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" ¿How should it be, to work with my free remote server?or,if you have done the "easy way" of uploading the .MDF file in the server (but a server different of mine) please, tell me the "how to..." (and the server you used :p )--------------------Ok guys, i am a Chilean young (i dont speak english very well) and i tried to write my ideas in an easy way, so I REALLY HOPE you can help me.I am a New (very new) on this (vwd , .net , asp.net ) in fact, i am using this just 3 days ago...THANK YOU SO MUCH,Carlos,La Serena, Chile.
View 7 Replies View RelatedI need some advice on copying databases, stored procedures, views, logins ..etc from a SQL Serevr 7.0 server, to a new SQL Server 2000 server.
Is it better to backup the databases on one server and then restore them to the other. Or id it easier to use the Wizards to Import everything from new server, or to Export everything from the old server ?
Any advice would be appreciated. I need to ensure that I do not miss anything, in particular Stored Procedures in the old master database.
I'm fairly new to a DBA role, I've been tasked with documenting all the SQL servers/databases. Is there anywhere on the Web where I could find a standard list of what needs to be documented. Any help/advice would be appreciated.
View 1 Replies View RelatedI tried using Import/Export to transfer a database from one SQL Server box to another using NT authentication.
The database appeared to transfer OK, however the process crashed with an error suggesting that a SQL Server login was not present.
How are the SQL Server logins transferred ?
Richard
We are wanting to know how to connect 2 databases residing on 2SQL servers. The workstation has access to both databases. Join on Cols 1 and 2.
Server1, Database1, Table1, Column1 (Char)
Server2, Database2, Table2, Column2 (VarChar)
Hello,
We have adedicated MS-SQL Server for our CRM database and we have another MS-SQL database server for in house applications, written in c#.Net 2.0. To avoid duplicating information already in our CRM database, it would be nice if we could build a query that combined columns from both data sources.Example
Code Snippet
CRM database on Server1
Table Accounts
Columns
AccountID UniqueIdentifier Primary Key
Name NvarChar(50)
Application Database on Server2
Table Transaction
Columns
TransactionID UniqueIdentifier Primary Key
AccountID UniqueIdentifier
Amount Money
Would it be possible to create a T-SQL statement that will select
Code Snippet
Select
Server1.CRM.Accounts.Name,
Server2.Application.Transaction.TransactionID,
Server2.Application.Transaction.AccountID,
Server2.Application.Transaction.Amount
From Server1.CRM.Accounts INNER JOIN Server2.Application.Transaction
ON (Server1.CRM.Accounts.AccountID = Server2.CRM.Transaction.AccountID)
I appreciate there will need to be 2 connections and therefore some credential information would need to be passed too.
Is anything like this possible? I do not wish to copy the Name field from Server1.CRM.Accounts to Server2..Application.Transaction as this is duplication and any change to the value of Server1.CRM.Accounts.Name would not be reflected in the results.
Many thanks for any information provided.
Code Snippet
SELECT ReciptItems.acc_TopicCode, ReciptItems.acc_DetailCode, ReciptItems.acc_CTopicCode,
SUM(ReciptItems.TotalInputPrice + ReciptItems.TotalOutputPrice), a.MoeenName_L1
FROM ReciptItems LEFT OUTER JOIN
Acc_mydbname.dbo.Categories AS a ON ReciptItems.acc_TopicCode = a.TopicCode
GROUP BY ReciptItems.acc_TopicCode, ReciptItems.acc_DetailCode, ReciptItems.acc_CTopicCode, a.MoeenName_L1
How Replace Acc_mydbname with (SELECT AccountDBName FROM Config)
(SELECT AccountDBName FROM Config) ='Acc_mydbname_2008.dbo.'
Hi,
I was wondering if the following is possible: I have 2 SQL Express servers on different machines. One holds the production and one the development database. On a request basis I'd like mirror all the data of the production database to the development database. Right now I have to stop the sql engine, zip the mdf, send it over, attach it to database, etc. and that's quite cumbersome. So, is there an easier way to do this? If not with SQL Express, is it possible with the full version?
Thanks,
Tom