Schema Compare in Toad for SQL Server

Hello, everyone. My name is Gary Jerep. I am a solutions consultant for Quest Software. Toad Solutions provide many capabilities that meet the needs of DBAs, developers, database professionals, including things like administration and management of database objects, code debugging, performance tuning, and way more. But in this video today, I'll be giving a high level overview of Toad for SQL Server Schema Compare and Sync feature, which is found in the professional or higher editions of Toad for SQL Server. So I'm showing my Toad for SQL Server here. Note that I've just logged into my Contoso retail data warehouse. And note also, that I have both a production and a development version of these two databases. I'd like to know what the differences are and maybe sync up development to production or maybe production with the changes on the dev system. The way I can do that is simply to right-click here. You'll see a Schema Compare option. Because I've control-clicked or highlighted both of these databases, the wizard that comes up here will know the context. So it's set my production database. And my target database is already identified. Now, I can add other things here too, by the way. So if I highlight the way I'm connected to, I can actually add other things like, by the way, a snapshot that I took, maybe a few months ago, of my development database. So notice that I can compare a source with multiple targets. Today, I'm just going to simply compare my production version with my dev version but just wanted to let you know that we can add more targets for your comparison purposes. If I say, Next through the wizard, this is an important panel too within the wizard if you don't need to select and compare everything that is every object type and all the attributes of those objects, you can go ahead and display or select just the object types that you want, and then the actual objects within those types get displayed here, as well. So you can pick and choose what you want for ultimate flexibility. Today, I'm simply going to not add any objects, and when I do that, you can see the message here that says I'm going to compare everything within the two databases. So moving ahead with the wizard, yes, there are some options that control how you want to do the compare. Do you want to ignore fill factors, do you want to ignore column orders, do you want to ignore white spaces and permissions and things like statistics select those options according to your purposes, move through the wizard. Now, what the wizard just did there was looked at the metadata for all my objects on the two SQL server databases and gives me a nice comparison. So this is a great comparison. We do have objects that exist on the target and not on the source. There are some that exist on the source and not in the target. Got a lot of objects that are the same, exactly the same defined the same, and then we've got quite a few objects that are different in both. Now, that's just a summary, by the way. When I go ahead and finish, you'll actually have a Doc tab that comes up within Toad for SQL Server that represents the schema comparison. Now, by default, I believe that there is no grouping. So you'll see a list of the objects for both source and target and an indicator whether that object is in the source only, maybe the target only, or there are differences, for example, differences here in the fact table. There is a nice filter option here, and I use this quite a bit. So if you're only interested in the objects that are different, pick and choose the object types and/or the difference types or the comparison status types that are important to you. I do want to note that group by up there, for me, it's really, really useful to have the comparison status first and then the type. That means that whatever I choose here, like for example, if I wanted to choose everything, I get a high-level overview of what's different, maybe what's in the source not in the target not in the target, in the target not in the source. But that can open up that specific status comparison and then see what types of objects are different. For example, there are differences in functions. OK, that's not an important one. But in terms of things like tables, there are five tables that are different between my source and target. What are those differences? You'll see them visually here. What's really nice is that these differences will be color coded. So rows in blue will indicate things that have changed on both the source and target. If you see any green rows highlighted here, those are rows that are appearing only in the source, not in the target. And any red rows here will be changes or rows that are in the target and not in the source. So visually speaking, you're going to see a lot of stuff here that will help you understand what the differences are between the source and the target databases. One of the thing here, though, you might need different filtering approaches. When you look at the different differences, please note that you can build or create different filter profiles. So right now, I've created one that says minimal objects. By default, we're going to be showing you everything all differences, source-only objects, target-only objects, objects of all types, et cetera. And you'll be able to see that here in the objects list, all right. And then, of course, the changes between any of the objects that you're selecting. Most importantly, here, not only seeing the changes and understanding what those differences are, yes, you can highlight here the script, copy it, and