<p>Here's a problem I've been trying to solve at work.  I'm not a database expert, so that perhaps this is a bit sophomoric.  All apologies.</p>

<p>I have a given database D, which has been duplicated on another machine (in a perhaps dubious manner), resulting in database D'.  It is my task to check that database D and D' are in fact exactly identical.</p>

<p>The problem, of course, is what to actually do if they are not.  For this purpose, my thought was to run a symmetric difference on each corresponding table and see the differences.</p>

<p>There is a "large" number of tables, so I do not wish to run each symmetric difference by hand.  How do I then implement a symmetric difference "function" (or stored procedure, or whatever you'd like) that can run on arbitrary tables without having to explicitly enumerate the columns?</p>

<p>This is running on Windows, and your hedge fund will explode if you don't follow through.  Good luck.</p>

[{'answer_id': 68395, 'author': 'Evan', 'author_id': 6277, 'author_profile': 'https://Stackoverflow.com/users/6277', 'pm_score': 2, 'selected': True, 'text': '<p>My first reaction is to suggest duplicating to the other machine again in a non-dubious manner.</p>\n\n<p>If that is not an option, perhaps some of the tools available from <a href="http://www.red-gate.com/products/index.htm" rel="nofollow noreferrer">Red Gate</a> could do what you need.</p>\n\n<p>(I am in no way affliated with Red Gate, just remember Joel mentioning how good their tools were on the podcast.)</p>\n'}, {'answer_id': 68402, 'author': 'Charles Graham', 'author_id': 7705, 'author_profile': 'https://Stackoverflow.com/users/7705', 'pm_score': 0, 'selected': False, 'text': '<p>Use the SQL Compare tools by <a href="http://www.red-gate.com/" rel="nofollow noreferrer">Red Gate</a>.  It compares scheamas, and the SQL Data Compare tool compares data.  I think that you can get a free trial for them, but you might as well buy them if this is a recurring problem.  There may be open source or free tools like this, but you might as well just get this one.</p>\n'}, {'answer_id': 2533784, 'author': 'user303677', 'author_id': 303677, 'author_profile': 'https://Stackoverflow.com/users/303677', 'pm_score': 2, 'selected': False, 'text': "<p>Here is the solution. The example data is from the ReportServer database that comes with SSRS 2008 R2, but you can use it on any dataset:</p>\n\n<pre><code>SELECT s.name, s.type \nFROM \n(\n    SELECT s1.name, s1.type\n    FROM syscolumns s1\n    WHERE object_name(s1.id) = 'executionlog2'\n    UNION ALL \n    SELECT s2.name, s2.type\n    FROM syscolumns s2 \n    WHERE object_name(s2.id) = 'executionlog3'\n) AS s \nGROUP BY s.name, s.type   \nHAVING COUNT(s.name) = 1\n</code></pre>\n"}, {'answer_id': 10819534, 'author': 'user1426412', 'author_id': 1426412, 'author_profile': 'https://Stackoverflow.com/users/1426412', 'pm_score': 1, 'selected': False, 'text': '<p>SQL Server 2000 Added the "EXCEPT" keyword, which is almost exactly the same as Oracle\'s "minus"</p>\n\n<pre><code>SELECT * FROM TBL_A WHERE ...\nEXCEPT\nSELECT * FROM TBL_B WHERE ...\n</code></pre>\n'}, {'answer_id': 12780872, 'author': 'user1498198', 'author_id': 1498198, 'author_profile': 'https://Stackoverflow.com/users/1498198', 'pm_score': 2, 'selected': False, 'text': "<p>You can achieve this by doing something like this.</p>\n\n<p>I have used a function to split comma separated value into a table to demostrate.</p>\n\n<pre><code>CREATE FUNCTION [dbo].[Split]\n(\n    @RowData nvarchar(2000),\n    @SplitOn nvarchar(5)\n)  \nRETURNS @RtnValue table \n(\n    Id int identity(1,1),\n    Data nvarchar(100)\n) \nAS  \nBEGIN \n    Declare @Cnt int\n    Set @Cnt = 1\n\n    While (Charindex(@SplitOn,@RowData)&gt;0)\n    Begin\n        Insert Into @RtnValue (data)\n        Select \n            Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))\n\n        Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))\n        Set @Cnt = @Cnt + 1\n    End\n\n    Insert Into @RtnValue (data)\n    Select Data = ltrim(rtrim(@RowData))\n\n    Return\nEND\nGO\n\n\nDECLARE @WB_LIST varchar(1024) = '123,125,764,256,157';\nDECLARE @WB_LIST_IN_DB varchar(1024) = '123,125,795,256,157,789';\n\nDECLARE @TABLE_UPDATE_LIST_IN_DB TABLE ( id varchar(20));\nDECLARE @TABLE_UPDATE_LIST TABLE ( id varchar(20));\n\nINSERT INTO @TABLE_UPDATE_LIST\nSELECT data FROM dbo.Split(@WB_LIST,',');\n\nINSERT INTO @TABLE_UPDATE_LIST_IN_DB\nSELECT data FROM dbo.Split(@LIST_IN_DB,',');\n\n\nSELECT * FROM @TABLE_UPDATE_LIST\nEXCEPT\nSELECT * FROM @TABLE_UPDATE_LIST_IN_DB\nUNION\nSELECT * FROM @TABLE_UPDATE_LIST_IN_DB\nEXCEPT\nSELECT * FROM @TABLE_UPDATE_LIST;\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/68346', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/10675/']