I was working with the Redgate compare tool today (something I am a big fan of I might add,) using it to take a snapshot of my database so I could drop all code and do a collation change to a database (an article I updated because I discovered a new wrinkle with computed columns needing to be modified.)
When I got to that point in the process to start adding back indexes, I noticed that a table said it was missing. Oiks, that is not a good feeling. This was a fairly large database that had taken well over an hour to do, and if I lost a table I was just going to...take a deep breath, you didn't drop a table by accident...I hope...
So I look at the other side of the comparison and hmm, a table with the same name is listed in the column of stuff only on the other side. So in essence, it is saying that there is a table named fred on one database, and fred on the other. This made me very unhappy, or at least curious. So I went looking, and couldn't find anything at first. I went into sys.objects, there were two objects that looked the same.
But then I noticed something in the scripts that were being displayed that told me the issue. While it is generally a good idea to use quoted identifiers when generating a script using existing objects, the name you enter will be the name that is used, for better or for worse. My problem turned out to be that there was just a space appended to the object name. You would never ever notice in code because SQL Server does name comparisons just like it does strings:
create table fred
create table [barney ]
cross join barney
Returns data just like if the name was not quoted. But if you quote the names:
select name, quotename(name)
where name in ('fred','barney')
barney [barney ]
Now it would be clear that [barney ] <> [barney] and if you were comparing the two names, the names would not be the same.
Crossposted to http://drsql.spaces.live.com