How to find missing columns, constraints, indexes in the database compared to others

Question

I have a C # .net application for Windows that uses a database in Microsoft SQL Server 2008. During the deployment for the first time for our clients, we create a copy of our database and deploy it to the clients, the remote server along with the user interface application. The client database may be in SQL Server 2005 or later.

Over time, the UI application and its associated database have made many changes. Since this is a thick client application, the client database does not synchronize with our latest database, and, unfortunately, no one ever took notes on all the changes made. So my problems are as follows:

• How to find the missing columns in a database table in a customer database compared to my database? If there are any?

• How to find missing primary / unique constraints in a database table in a client database compared to my database? If there are any?

• How to find the missing indexes in the database table existing in the customer database compared to my database? If there are any?

Please keep in mind that the size of the client database can vary from 10 to 100 GB, so I cannot plan to simply drop all client tables and recreate them.

Answer 1

, SQL. , , , .

AdoptSQLDiff, . RedGate , , . SQL Database google, . , .

, , . , , , , , . , script, . , script , - , , .

, .

, , .

Answer 2

. SQL Server, .

( SSMS → → ) DACPAC, ( script).

SQL Server.

Answer 3

SQLServer Management Studio , Task- > Generate Script, .

2 , . , .

db.

, . . dbs ,

, db. db, .. , .

Answer 4

- diff DB, VS2010 + , .

, .

• EF - Migrations, .

• SQL Server , DAC ( , Jakub)

• , DB.UP python , Alembic, API- python.

• , db , DB.UP

Answer 5

, , .

, ? , ?

- ( , , , ).

USE [master]
GO

SELECT
    LocalDataBaseTable.name AS TableName,
    LocalDataBaseTableColumns.name AS [Column],
    LocalDataBaseTypes.name AS DataType,
    LocalDataBaseTableColumns.max_length,
    LocalDataBaseTableColumns.[precision]
INTO #tmpLocalInfo
FROM LocalTable.sys.columns as LocalDataBaseTableColumns 
INNER JOIN LocalTable.sys.tables AS LocalDataBaseTable
    ON LocalDataBaseTableColumns.object_id = LocalDataBaseTable.object_id
INNER JOIN LocalTable.sys.types AS LocalDataBaseTypes
    ON LocalDataBaseTypes.user_type_id = LocalDataBaseTableColumns.user_type_id

SELECT 
    ServerDataBaseTable.name AS TableName,
    ServerDataBaseTableColumns.name AS [Column],
    ServerDataBaseTypes.name AS DataType,
    ServerDataBaseTableColumns.max_length,
    ServerDataBaseTableColumns.[precision]
INTO #tmpServerInfo
FROM ServerTable.sys.columns as ServerDataBaseTableColumns 
INNER JOIN ServerTable.sys.tables AS ServerDataBaseTable
    ON ServerDataBaseTableColumns.object_id = ServerDataBaseTable.object_id
INNER JOIN ServerTable.sys.types AS ServerDataBaseTypes
    ON ServerDataBaseTypes.user_type_id = ServerDataBaseTableColumns.user_type_id

SELECT
    #tmpServerInfo.* 
FROM #tmpLocalInfo 
RIGHT OUTER JOIN #tmpServerInfo 
    ON #tmpLocalInfo.TableName = #tmpServerInfo.TableName COLLATE DATABASE_DEFAULT
    AND #tmpLocalInfo.[Column] = #tmpServerInfo.[Column]  COLLATE DATABASE_DEFAULT
WHERE #tmpLocalInfo.[Column] IS NULL

DROP TABLE #tmpLocalInfo
DROP TABLE #tmpServerInfo

. , 'sys' , .

Answer 6

script, , , ..

Compalex is a free, lightweight script to compare two database schemas. It supports MySQL, MS SQL Server and PostgreSQL.

or look at this question Compare two MySQL databases . This question is about comparing the two MySQL schemas, but some of the listed tools support MSSQL or have a version of MSSQL (for example http://www.liquibase.org/ ).

Another answer What is the best tool for comparing two SQL Server databases (schema and data)?