You can use a DDL trigger to log schema changes.
CREATE DATABASE AuditDB;
GO
USE AuditDB;
GO
CREATE TABLE dbo.DDLEvents
(
EventDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
EventType NVARCHAR(64),
EventDDL NVARCHAR(MAX),
EventXML XML,
DatabaseName NVARCHAR(255),
SchemaName NVARCHAR(255),
ObjectName NVARCHAR(255),
HostName VARCHAR(64),
IPAddress VARCHAR(32),
ProgramName NVARCHAR(255),
LoginName NVARCHAR(255)
);
Then in your database, you can add events to a DDL trigger:
CREATE TRIGGER DDLTrigger_Sample
ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
CREATE_VIEW, ALTER_VIEW, DROP_VIEW
--, etc etc
AS
BEGIN
SET NOCOUNT ON;
DECLARE @EventData XML = EVENTDATA();
DECLARE @ip VARCHAR(32) = (SELECT client_net_address
FROM sys.dm_exec_connections WHERE session_id = @@SPID);
INSERT AuditDB.dbo.DDLEvents
(
EventType,
EventDDL,
EventXML,
DatabaseName,
SchemaName,
ObjectName,
HostName,
IPAddress,
ProgramName,
LoginName
)
SELECT
@EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
@EventData,
DB_NAME(),
@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(255)'),
@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)'),
HOST_NAME(),
@ip,
PROGRAM_NAME(),
SUSER_SNAME();
END
GO
The advantage over audit is that this does not require Enterprise Edition. The advantages over the default trace are that (a) you can control which events you capture and (b) you can keep your history as long as you want (the default trace rolls over).
This is culled from a tip I wrote here.
Best Answer
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.