I have 2 databases test1 and test2. where I need to compare columns from schema1 to schema2 and fetch the results.If table in test1 schema not present on test2 schema ,it should ignore that table. I have below query which shows all the results including table not present in test2 schema. select * from ( select COALESCE(c1.table_name, c2.table_name) as table_name, COALESCE(c1.column_name, c2.column_name) as table_column, c1.column_name as test1, c2.column_name as test2 from (select table_name, column_name from information_schema.columns c where c.table_schema ='test1') c1 right join (select table_name, column_name from information_schema.columns c where c.table_schema ='test2') c2 on c1.table_name = c2.table_name and c1.column_name = c2.column_name union select COALESCE(c1.table_name, c2.table_name) as table_name, COALESCE(c1.column_name, c2.column_name) as table_column, c1.column_name as test1, c2.column_name as test2 from (select table_name, column_name from information_schema.columns c where c.table_schema ='test1') c1 left join (select table_name, column_name from information_schema.columns c where c.table_schema ='test2') c2 on c1.table_name = c2.table_name and c1.column_name = c2.column_name ) tmp where test1 is null or test2 is null order by table_name, table_column; Is it possible to rewrite above query ,just compare table in test1 with test2 and display the mismatch columns.if table not present in test2 schema, it should ignore that table. Thanks

I'm attempting to compare all objects of two schemas located in two different databases. I know there may be ways to do this with TOAD and SQL Developer, but is there another way to do this by script or Sql*Plus? Thanks in advance.

Hi I was trying to do a Sub Query and Comparison Report For Example, I have a Table of Teams with Color AND Name in a Single Table. A person can be on more than one Team and Multiple Team Colors can exist in the table. CREATE TABLE [TEAMS]( [Color] [nvarchar](10) NULL, [Name] [nvarchar](10) NULL ); INSERT INTO TEAMS (Color,Name) VALUES (N'Red',N'Austin'), (N'Blue',N'Austin'), (N'Red',N'James'), (N'Yellow',N'Melissa'), (N'Blue',N'Brandon'); I was trying to get a query to help identify the differences between two Teams like who is different between the Red and Blue Team and get something like this. I was hoping to do a Full Join on the Name with NULLs in Table A and NULLs in Table B to Find the Differences but because its the same Table No NULLS are Occurring. The Query I was doing for my comparison is this. Select a.Color, a.Name, b.Color, b.Name from TEAMS a FULL JOIN TEAMS b on a.Name = b.Name WHERE a.Color = 'Red' AND b.Color = 'Blue'; Removing the WHERE CLAUSE is also Revealing that Team Color is Comparing to itself (Red Team A to Red Team B, etc...) and the team I'm not interested in (Yellow) is also being used in the Query and eating Resources. This seems possible with Temporary Tables if just Red Team was in Table A and just Blue Team was in Table B but is there a Way to Do this with some form of Sub Query?

I am trying to compare two tables in two separate databases (same instance) for a new data extract The tables have around 4 millions rows and due to how they've been extracted are not in the same order in each table, as they are just staging tables (there is no PKs). I have tried using (select * from table 1 Except select * from table 2) UNION ALL (select * from table 2 Except select * from table 1) however this results in around 10 millions rows so is not working as expected. I would guess the results should be around a few thousand.

I created a SQL agent job to perform a SQL Compare using the below command line powershell.exe -File D:\SQLCompareReports\DEVvsPRJ\DEVvsPRJ.ps1 This below given is the command line code in DEVvsPRJ.ps1 file set-location "C:\Program Files (x86)\Red Gate\SQL Compare 13"; ./sqlcompare /s1:Server1 /db1:Database1/s2:Server2 /db2:Database2/r:"D:\SQLCompareReports\DEVvsPRJ\DEVvsPRJ.html" /rt:Classic /f So, when I tried executing the job it succeeded without any errors. But when I checked the location comparison report was not created. So I went back to Job history and found the following error. > ======== Copyright Copyright c Red Gate Software Ltd 2019 Unhandled Exception: System.UnauthorizedAccessException: Access to the path is > denied. at System.IO.__Error.WinIOError(Int32 errorCode, String > maybeFullPath) at > System.IO.Pipes.NamedPipeClientStream.Connect(Int32 timeout) at > RedGate.Ipc.NamedPipes.NamedPipeEndpointClient.Connect(Int32 > timeoutMs) at > RedGate.Ipc.Rpc.RpcConnectionProvider.TryGetConnection(TimeSpan > timeout) at > RedGate.Ipc.ReconnectingConnectionProvider.ReconnectionWorker() at > System.Threading.ExecutionContext.RunInternal(ExecutionContext > executionContext, ContextCallback callback, Object state, Boolean > preserveSyncCtx) at > System.Threading.ExecutionContext.Run(ExecutionContext > executionContext, ContextCallback callback, Object state, Boolean > preserveSyncCtx) at > System.Threading.ExecutionContext.Run(ExecutionContext > executionContext, ContextCallback callback, Object state) at > System.Threading.ThreadHelper.ThreadStart(). Process Exit Code 0. > The step succeeded. Can anyone please suggest me a way on how to automate this task or a way through this error.

We have a Visual Studio solution for our SQL Server 2019 database, and it is stored in a git repo. One table has a couple of check constraints using the BETWEEN operator, like this. CONSTRAINT chk_FileTbl_MonthlyDate CHECK (MonthlyDate BETWEEN 1 AND 31) When I used Redgate SQL Compare 14 to compare the repo to the database, it displayed the constraint reformatted with less than and greater than operators, like this. CONSTRAINT chk_FileTbl_MonthlyDate CHECK (MonthlyDate >= 1 AND MonthlyDate <= 31) I thought it was a bug/feature in Redgate's software and submitted a ticket for which I am awaiting a response. However, my colleague then used the comparison tool in Visual Studio 2019 and it did the same thing. If we look at the file defining the table, it uses BETWEEN, but both comparison tools show and deploy a change script with the less than/greater than format. What is causing this behavior? We somewhat recently moved to SQL Server 2019, is it some kind of built-in optimization?

we have multi-tenants databases and we have special stored procedure to compare the structure of the tenents after each release. Now, we are moving to Azure SQL Server and the stored procedure is not working anymore because we use the INFORMATION_SCHEMA.TABLES, INFORMATION_SCHEMA.COLUMNS, ... like this: SET @SelectTables = 'SELECT TABLE_SCHEMA + ''.''+ TABLE_NAME FROM '+ @SourceDatabase + '.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=''BASE TABLE'' ' INSERT INTO #SourceTableNames EXECUTE sp_executesql @SelectTables SET @SelectTables = 'SELECT TABLE_SCHEMA + ''.''+ TABLE_NAME FROM '+ @DestinationDatabase +'.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=''BASE TABLE'' ' INSERT INTO #DestinationTableNames EXECUTE sp_executesql @SelectTables and we got an error: Reference to database and/or server name in 'Database.INFORMATION_SCHEMA.TABLES' is not supported in this version of SQL Server. In an emergency situation we use Visual Studio SQL Compare or RedGate Compare tools but it would be better to compare the structure of the existing database with a newly created database based on EF Core after each release. Would you please guide me about that? I do not like to compare the tenant one by one after each release with the mentioned tools.

Many similar questions were asked before, and mostly about comparing database schemas and data. That's all fine, but what about all the other SQL Server objects in the system overall? This is the closest answer I've found, but it doesn't answer my query on a tool to be able to also create an SQL script of the variances, that can be easily applied to another server - including new user logins, roles, schemas, linked-servers, maps, maintenance plans, etc. The other tool I found that does this is TOAD for SQL Server, but it's not cheap. I was hoping for an SQL script solution? Particularly with a mix of SQL Server environments from 2008 R2 to 2017? Thanks

i used SQL Server, and have two joined table Select b.team, SUM(a.total) FROM data1 A INNER JOIN team b ON a.teamID = b.teamID WHERE month = 1 and year = 2019 GROUP by team second one is like this Select b.team, SUM(c.total) FROM data2 C INNER JOIN team b ON c.teamID = b.teamID WHERE Date BETWEEN '2019-01-01' AND '2019-01-31' GROUP by team and the result will be : Data 1 Data 2 TEAM | Total TEAM | Total Team 1 | 5 Team 1 | 4 Team 2 | 3 Team 2 | 3 Team 3 | 8 Team 3 | 9 i wanted to create query to get result like this : Team | Total Team 1 | 1 Team 3 | -1