SQL Server Data Tools (SSDT) transforms database development by introducing a ubiquitous, declarative model that spans all the phases of database development inside Visual Studio. You can use SSDT Transact-SQL design capabilities to build, debug, maintain, and refactor databases. You can work with a database project, or directly with a connected database instance on or off-premise.
Developers can use familiar Visual Studio tools for database development. Tools such as: code navigation, IntelliSense, language support that parallels what is available for C# and Visual Basic, platform-specific validation, debugging, and declarative editing in the Transact-SQL editor. SSDT also provides a visual Table Designer for creating and editing tables in either database projects or connected database instances. While you are working on your database projects in a team-based environment, you can use version control for all the files. When it's time to publish your project, you can publish to all supported SQL platforms; including SQL Database and SQL Server. SSDT platform validation capability ensures that your scripts work on the target you specify.
The SQL Server Object Explorer in Visual Studio offers a view of your database objects similar to SQL Server Management Studio. SQL Server Object Explorer allows you to do light-duty database administration and design work. You can easily create, edit, rename and delete tables, stored procedures, types, and functions. You can also edit table data, compare schemas, or execute queries by using contextual menus right from the SQL Server Object Explorer.
The following topics and sections discuss how SSDT can help you do database development. How To topics are included to help guide you through completing tasks for your database project. These tasks, written like a tutorial and completed in order, use Northwind Traders, a fictitious company that imports and exports specialty foods.
Topics/Section | Description |
---|---|
Project-Oriented Offline Database Development | Topics in this section describe SQL Server Data Tools features for authoring, building, debugging and publishing a database project. |
Project-Oriented Database Development using Command-Line Tools | Topics in this section describe command-line tools which enable a number of project-oriented database development scenarios. |
Connected Database Development | Topics in this section describe SQL Server Data Tools features for designing and querying a connected database. |
Compare and Synchronize Data in One or More Tables with Data in a Reference Database | Discusses how to compare data in a source database and a target database, specify which values should match, and then either update the target to synchronize the databases or export the update script to the Transact-SQL editor or to a file. |
Use Transact-SQL Editor to Edit and Execute Scripts | Topics in this section describe how to use the Transact-SQL Editor, which provides a rich editing and debugging experience when working with scripts. |
Manage Tables, Relationships, and Fix Errors | Topics in this section describe how to: - Use the Table Designer to design tables and manage table relationships. - Fix common syntax or semantic errors. |
Verifying Database Code by Using SQL Server Unit Tests | Discusses how you can use SQL Server unit tests to establish a baseline state for your database and then to verify any subsequent changes that you make to database objects. |
Extending the Database Features | You can create feature extensions that let you extend features such as unit testing, and database code analysis. |
Required Permissions for SQL Server Data Tools | Discusses required access permission to use SQL Server Data Tools. |
DAC Framework Compatibility | Describes compatibility issues with DAC framework. |
Is there a way to find the differences in two SQL Server databases (schema only). One is local and the second is at a customer's site. We are experiencing problems with crystal reports running some reports and some code not executing and it would appear that the schemas don't match.
Can I run the same command on both databases and compare the results to tell where the differences are?
16 Answers
If you cannot use one of the many tools out there because of connectivity problems and want an 'offline' compare, you can use SSMS to generate scripts for all database objects by right clicking on the database and using the 'Tasks../Generate Scripts' function, and make sure you select to create one file per object.
When you have done that for both databases, get the two sets of scripts onto a local machine in two separate folders and use WinMerge (or similar) to compare the two.
Another option is to use SQL Server Data Tools (SSDT), an extension of Visual Studio. You can extract your database schema as a .dacpac file and compare that with another .dacpac file or an existing database. SSDT is included with SQL Server 2012 client tools, making it pretty accessible. You can find the full instructions of how to run the compare on the MSDN site.
After struggling with an easy way to do this same task - see what's changed between 2 models, I wrote the following SQL Script that will compare two schemas to determine new and deleted columns
If you need to compare more than one database file you could script SQLPackage.exe
.
I don't have working code for you but you could look at the SQLPackage.exe documentation for some inspiration.
Nov 9, 2018 - ASUS PC Link is licensed as freeware for Windows 32 bit and 64 bit. Tools category and is available to all software users as a free download. Aug 21, 2018 - Earlier versions of PC Link are also available for download – these can be. Here: http://www.linkecu.com/software-support/pc-link-downloads/. Mar 30, 2017 - Review of ASUS PC Link with a rating, Screenshots along with a virus test. Is available to all software users as a free download (Freeware). Link ECU's PC Link engine management software downloads. PC Link allows real-time configuration of all ECU functions, automated tuning, data log analysis. Pc link software download.
You would extract your master database to a dacpac file and then compare the dacpac file to the rest of your databases. The result of the comparison could either be a xml report of the changes or a .sql file you can run to synchronize the databases.
Something like this:
and then
You can have a look at this article or this one for sample code.
Do a search for 'SQL Server Compare' and you'll find lots of tools. The one we use at my job is Red Gate SQLCompare. It has a 14 day trial. But since you are talking about two different environments I don't think that would work for you, unless the client sends you a backup of their DB. The other option is to write queries against the system tables (like sys.indexes, sys.tables, etc).
The easiest way is to use an automated tool built for this purpose, but if you don't have access to one, you can get all of the basic information that you need from the INFORMATION_SCHEMA
views.
Using the metadata in INFORMATION_SCHEMA
is probably an easier option than generating DDL scripts and doing a source compare because you have much more control over how the data is presented. You can't really control the order in which generated scripts will present the objects in a database. Also, the scripts contain a bunch of text that may be implementation dependent by default and may cause a lot of mismatch 'noise' when what you probably really need to focus on is a missing table, view or column, or possibly a column data type or size mismatch.
Write a query (or queries) to get the information that matters to your code from the INFORMATION_SCHEMA
views and run it on each SQL Server from SSMS. You can then either dump the results to a file and use a text file compare tool (even MS Word) or you can dump the results to tables and run SQL queries to find mismatches.
I am including this answer for the sake of a new question that was marked as a duplicate.
I once had to compare two production databases and find any schema differences between them. The only items of interest were tables that had been added or dropped and columns that had been added, removed, or altered. I no longer have the SQL scripts I developed, but what follows is the general strategy. And the database was not SQL Server, but I think the same strategy applies.
First, I created what can best be described as a metadatabase. The user tables of this database contained data descriptions copied from the system tables of the production databases. Things like Table Name, Column Name, Data Type and Precision. There was one more item, Database Name, that did not exist in either of the production databases.
Next, I developed scripts that coupled selects from the system tables of the production databases with inserts into the user tables of the metadatabase.
Finally, I developed queries to find tables that existed in one database but not the other, and columns from tables in both database that were only in one database, and columns with inconsistent definitions between the two databases.
Out of about 100 tables and 600 columns, I found a handful of inconsistencies, and one column that was defined as a floating point in one database and an integer in the other. That last one turned out to be a godsend, because it unearthed a problem that had been plaguing one of the databases for years.
The model for the metadatabase was suggested by the system tables in question. The queries were not hard to construct, revolving mostly around group by and having count(database name) = 1.
In your case, with 700 production databases, you might want to automate the first two steps more than I did with just two databases to compare. But the idea is similar.
I had this exact same question and I believe that the Microsoft SQL Server Management Studio (SSMS) has a much easier/simpler solution than anything I saw here. I have a production site with MS SQL Server Express and soon to be several more where I don't want to have to install VisualStudio or other applications other than SSMS.
So within SSMS, right click on the database to get the schema for. Select Tasks > Generate Scripts.. to open a wizard to script the schema and configuration for the entire database (or selected objects if you want). I kept all the default options except the path/filename, but the tool has a plethora of options. The wizard created one SQL which I copied via OneDrive back to my PC. I then used Notepad++ to compare the SQL to a file generated in the same way against my SIT database. You have to filter out hits from the date/time in comments, but otherwise it is a great comparison of the two databases.
Presto! Writing this up was significantly harder than doing the actual compare.
A great tool I use (although not updated in a while still works) is AdeptSqlDiff
Does both schema compares as well as data comparisons. Just like RedGate there is a cost but also a 30 day trial. And the price is pretty reasonable.
Maybe this free script https://github.com/dlevsha/compalex can help you. It support Microsoft SQL Server.
Compalex is a free lightweight script to compare two database schemas. It supports MySQL, MS SQL Server and PostgreSQL.
You can try demo here
There are many third party tools out there which will do schema and data compare, and synchronization. Two tools you can use are the ones my team and I have developed, xSQL Schema Compare for schema comparisons and xSQL Data Compare for data comparisons between objects with the same schema. Hope this helps!
Disclaimer: I'm affiliated to xSQL
There is a lot of tools on the market which you might use to get the job done. My company is using ApexSQL Diff for both comparison and sync because it is free for Azure, but you can’t go wrong with either Devart or Redgate tools.
Sql Server Data Comparison
I'm a fan of SQL DBDiff, which is an open source tool you can use to compare tables, views, functions, users, etc. of two instances of SQL Server databases and generate a change script between the source and destination databases.
I use this free (and open source) tool: OpenDBDiff
DBDiff is the best tool for this, you can find it here.
Not the answer you're looking for? Browse other questions tagged sql-serversql-server-2008-r2schema or ask your own question.
-->Before you can perform an action on a database in Visual Studio, you must log on with an account that has certain permissions on that database. The specific permissions that you need vary based on what action you want to perform. The following sections describe each action that you might want to perform and the specific permission that you need to perform it.
Permissions to Create or Deploy a Database
You must have the following permissions to create or deploy a database.
Actions | Required Permissions |
Import database objects and settings | You must be able to connect to the source database. If the source database is based on SQL Server 2005, you must also own or have the VIEW DEFINITION permission on each object. If the source database is based on SQL Server 2008 or later, you must also own or have the VIEW DEFINITION permission on each object. Your login must have the VIEW SERVER STATE permission (for database encryption keys). |
Import server objects and settings | You must be able to connect to the master database on the specified server. If the server is running SQL Server 2005, you must have the VIEW ANY DEFINITION permission on the server. If the source database is based on SQL Server 2008 or later, you must have the VIEW ANY DEFINITION permission on the server. Your login must have the VIEW SERVER STATE permission (for database encryption keys). |
Create or update a database project | You do not require any database permissions to create or modify a database project. |
Deploy new database or deploy with Always Re-create Database option set | You must either have the CREATE DATABASE permission or be a member of the dbcreator role on the target server. When you create a database, Visual Studio connects to the model database and copies its contents. The initial login (for example, yourLogin) that you use to connect to the target database must have db_creator and CONNECT SQL permissions. This login must have a user mapping on the model database. If you have sysadmin permissions, you can create the mapping by issuing the following Transact-SQL statements: USE [model] CREATE USER yourUser FROM LOGIN yourLogin The user (in this example, yourUser) must have CONNECT and VIEW DEFINITION permissions on the model database. If you have sysadmin permissions, you can grant these permissions by issuing the following Transact-SQL statements: USE [model] GRANT CONNECT to yourUser GRANT VIEW DEFINITION TO yourUser If you deploy a database that contains unnamed constraints and the CheckNewContraints option is enabled (it is enabled by default), you must have db_owner or sysadmin permissions or deployment will fail. This is only true for unnamed constraints. For more information about the CheckNewConstraints option, see Database Project Settings. |
Deploy updates to an existing database | You must be a valid database user. You must also be a member of the db_ddladmin role, own the schema, or own the objects that you want to create or modify on the target database. You need additional permissions to work with more advanced concepts such as logins or linked servers in your pre-deployment or post-deployment scripts. NOTE: If you deploy to the master database, you must also have the VIEW ANY DEFINITION permission on the server to which you deploy. |
Use an assembly with the EXTERNAL_ACCESS option in a database project | You must set the TRUSTWORTHY property for your database project. You must have the EXTERNAL ACCESS ASSEMBLY permission for your SQL Server login. |
Deploy assemblies to a new or existing database | You must be a member of the sysadmin role on the target deployment server. |
For more information, see SQL Server Books Online.
Permissions to Refactor a Database
Database refactoring occurs only within the database project. You must have permissions to use the database project. You do not need permissions on a target database until you deploy your changes to it.
Permissions to Perform Unit Testing on a SQL Server Database
You must have the following permissions to perform unit tests on a database.
Actions | Required Permissions |
Execute a test action | You must use the execution context database connection. For more information, see Overview of Connection Strings and Permissions. |
Execute a pre-test or post-test action | You must use the privileged context database connection. This database connection has more permissions than the execution context connection does. |
Run TestInitialize and TestCleanup scripts | You must use the privileged context database connection. |
Deploy database changes before you run tests | You must use the privileged context database connection. For more information, see How to: Configure SQL Server Unit Test Execution. |
Generate data before you run tests | You must use the privileged context database connection. For more information, see How to: Configure SQL Server Unit Test Execution. |
Permissions to Generate Data
You must have the INSERT and SELECT permissions on the objects in the target database to generate test data by using Data Generator. If you purge data before you generate data, you must also have DELETE permissions on the objects in the target database. To reset the IDENTITY column on a table, you must own the table, or you must be a member of the db_owner or db_ddladmin role.
Permissions to Compare Schemas and Data
You must have the following permissions to compare schemas or data.
Actions | Required Permissions |
Compare the schemas of two databases | You must have the permissions to import objects and settings from the databases as described in Permissions to Create or Deploy a Database. |
Compare the schemas of a database and a database project | You must have the permissions to import objects and settings from the database as described in Permissions to Create or Deploy a Database. You must also have the database project open in Visual Studio. |
Write updates to a target database | You must have the permissions to deploy updates to the target database as described in Permissions to Create or Deploy a Database. |
Compare the data of two databases | In addition to the permissions that you need to compare the schemas of two databases, you also need the SELECT permission on all the tables that you want to compare and VIEW DATABASE STATE permission. |
For more information, see SQL Server Books Online.
Permissions to Run the Transact-SQL Editor
What you can do within the Transact-SQL editor is determined by your execution context to the target database.
Permissions for SQL Server Common Language Run-time Projects
The following table lists the permissions that you must have to deploy or debug CLR projects:
Actions | Required Permissions |
---|---|
Deploy (initial or incremental) of a safe permission set assembly | db_DDLAdmin - this permission grants CREATE and ALTER permissions for the assemblies and objects types that you deploy database-level VIEW DEFINITION - required in order to deploy database-level CONNECT - grants the ability to connect to the database |
Deploy an external_access permission set assembly | db_DDLAdmin - this permission grants CREATE and ALTER permissions for the assemblies and objects types that you deploy database-level VIEW DEFINITION - required in order to deploy database-level CONNECT - grants the ability to connect to the database In addition, you must also have: TRUSTWORTHY database option set to ON The login that you use to deploy must have the External Access Assembly server permission. |
Deploy an unsafe permission set assembly | db_DDLAdmin - this permission grants CREATE and ALTER permissions for the assemblies and objects types that you deploy database-level VIEW DEFINITION - required in order to deploy database-level CONNECT - grants the ability to connect to the database In addition, you must also have: TRUSTWORTHY database option set to ON The login that you use to deploy must have the Unsafe Assembly server permission. |
Remote debug a SQL CLR assembly | You must have the sysadmin fixed role permission. |
Important
In all cases, the assembly owner must be the user that you are using to deploy the assembly or the owner must be a role in which that user is a member. This requirement also applies to any assemblies referenced by the assembly that you deploy.
See Also
Creating and Defining SQL Server Unit Tests
SQL Server Data Tools