I had a simpler repro in mind:
CREATE TABLE #x(z CHAR(1));
CREATE TABLE #y(z CHAR(1));
INSERT #x SELECT 'O';
INSERT #x SELECT 'R';
INSERT #x SELECT 'D';
INSERT #y SELECT 'E';
INSERT #y SELECT 'R';
SELECT z FROM #x
UNION ALL
SELECT z FROM #y;
Results:
O
R
D
E
R
Now add an index:
CREATE CLUSTERED INDEX z ON #x(z);
SELECT z FROM #x
UNION ALL
SELECT z FROM #y;
Results:
D -|
O -|- ordered based on the clustered index, not how originally inserted
R -|
E
R
This still produces the first three rows from #x and the last two rows from #y, so still doesn't prove that SQL Server might return those entire queries in a different order than physically laid out in the query (just that you can't rely on the ordering inside those sets). But clearly this mythical "observe an order once, and it will always be true" statement needs to be nipped.
Bottom line: do not rely on observed order. If you want a certain ordering, say so using ORDER BY.
Also, please read this post by Conor Cunningham, a pretty smart guy on the SQL team.
Back in 2007, I asked for an easy way to generate a CREATE TABLE
script via T-SQL rather than using the UI or SMO. I was summarily rejected.
However, SQL Server 2012 makes this very easy. Let's pretend we have a table with the same schema across multiple databases, e.g. dbo.whatcha
:
CREATE TABLE dbo.whatcha
(
id INT IDENTITY(1,1),
x VARCHAR(MAX),
b DECIMAL(10,2),
y SYSNAME
);
The following script uses the new sys.dm_exec_describe_first_results_set
dynamic management function to retrieve the proper data types for each of the columns (and ignoring the IDENTITY
property). It builds the #tmp table you need, inserts from each of the databases in your list, and then selects from #tmp, all within a single dynamic SQL batch and without using a WHILE
loop (that doesn't make it better, just simpler to look at and allows you to ignore Database_Ref_No
entirely :-)).
SET NOCOUNT ON;
DECLARE @sql NVARCHAR(MAX), @cols NVARCHAR(MAX) = N'';
SELECT @cols += N',' + name + ' ' + system_type_name
FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.whatcha', NULL, 1);
SET @cols = STUFF(@cols, 1, 1, N'');
SET @sql = N'CREATE TABLE #tmp(' + @cols + ');'
DECLARE @dbs TABLE(db SYSNAME);
INSERT @dbs VALUES(N'db1'),(N'db2');
-- SELECT whatever FROM dbo.databases
SELECT @sql += N'
INSERT #tmp SELECT ' + @cols + ' FROM ' + QUOTENAME(db) + '.dbo.tablename;'
FROM @dbs;
SET @sql += N'
SELECT ' + @cols + ' FROM #tmp;';
PRINT @sql;
-- EXEC sp_executesql @sql;
The resulting PRINT
output:
CREATE TABLE #tmp(id int,x varchar(max),b decimal(10,2),y nvarchar(128));
INSERT #tmp SELECT id,x,b,y FROM [db1].dbo.tablename;
INSERT #tmp SELECT id,x,b,y FROM [db2].dbo.tablename;
SELECT id,x,b,y FROM #tmp;
When you are confident it's doing what you expect, just uncomment the EXEC
.
(This trusts you that the schema is the same; it does not validate that one or more of the tables has since been changed, and may fail as a result.)
Best Answer
I personally would use a tool (as indeed I already use) , such as are sold by Apex, Red Gate, Toad, and others. These could be used to script only the rows that have changed. That would make your looking for changes very simple indeed.
If you are looking for a free tool you might examine http://opendbiff.codeplex.com/. I have not personally used it, but some people find it useful. (There are doubtless other such tools in the public domain.)