Compare master database sys tables

dtaylo4

I recently had a software installation that required access to the system master database because of data inserts that were required. This is the first time I've come across this requirement and a change to a system database. I want to compare the master database (before/after) the installation, but I don't see how to select any of the "sys" objects.

sam.blackburn

These objects are called catalog views, and I think all of them are readonly. They describe the state of your database, for example if you run CREATE TABLE then you'll see an extra row in sys.tables, but you can't create a table by inserting into the view (though that would be awesome). It's possible the installation just reads those views to check that certain objects exist.

Can you tell what changes the installation is making to the master database (e.g. with a SQL Profiler)?

dtaylo4

I did have a trace running and it appears that only custom error messages were added to the sys.messages table. Here are a few snippets of what I've found in the trace so far. Ultimately, I just need to make sure that the system databases weren't affected in a way that might have a negative affect on the other application databases on this instance.

--drop custom error messages
declare @dropsql nvarchar(max)
set @dropsql = ''
select @dropsql = @dropsql + 'exec sp_dropmessage @msgnum=' + cast(message_id as nvarchar) + ' ' + char(13) from sys.messages where message_id between 50000 and 70000-1
exec sp_executesql @dropsq

select @x=count(*) from sys.messages where message_id = 50100;
if (@x=1) exec sp_dropmessage 50100
exec sp_addmessage @msgnum=50100, @severity=16, @msgtext=N'parameter cannot be null: <%s>'

way0utwest

The issue you might have is if there is code with RAISERROR () that might call a specific message. You need to have those inserted into sys.messages with sp_addmessage. I don't have a good way other than scan all your code for the RAISERROR() command and document user defined messages:

RaiseError - https://docs.microsoft.com/en-us/sql/t-sql/language-elements/raiserror-transact-sql

dtaylo4

Unfortunately, I don't have access to the code. This is a 3rd party application and the vendor is unable to tell us what changes are made to the master database. It appears to be all data inserts, but unfortunately it doesn't appear that SQL Data Compare is able to read/compare data in the "sys" objects.

At this point, it seems my only option is to dig through the trace and see what queries were run.

way0utwest

Data Compare doesn't work with sys. objects. Those aren't objects we can work with, at least not most, so we didn't build this in.

If you want to know what changes have been made, you can select a list of values to a text file from another instance (or newly installed instance) and then import that into a table where you run a query between the two lists.

sam.blackburn

It's possible to compare the tables in Data Compare, but not to deploy them:

• Run this on each database:

  CREATE VIEW SysMessagesProxy AS SELECT * FROM sys.messages
  

• ensure the "Include Views" project option is checked
• select the message_id and language_id as the comparison key
• ensure the table is included for comparison (the checkbox on the Tables & Views tab).

This allowed me to compare the data, but deployment fails with "Ad hoc updates to system catalogs are not allowed." I'm guessing an sp_addmessage statement needs to be run for each difference.

Hope that helps!

way0utwest

That's a neat trick. Just tried it and it worked great. Didn't know we could do this.

sam.blackburn

I've figured out a way to make deployment work too, though only for user-defined messages:

CREATE VIEW SysMessagesProxy AS SELECT * FROM sys.messages WHERE message_id < 50000
GO
CREATE OR ALTER TRIGGER AddSysMessage ON SysMessagesProxy INSTEAD OF INSERT AS
BEGIN
    DECLARE @message_id INT, @language_id INT, @severity INT, @text NVARCHAR(MAX)
	DECLARE cur CURSOR FOR SELECT message_id, language_id, severity, text FROM Inserted
	OPEN cur

	FETCH NEXT FROM cur INTO @message_id, @language_id, @severity, @text

	WHILE @@FETCH_STATUS = 0 BEGIN
		DECLARE @languageName NVARCHAR(MAX) = (SELECT name FROM sys.syslanguages WHERE langid = @language_id)
		EXEC sp_addmessage @message_id, @severity, @text, @languageName
		FETCH NEXT FROM cur INTO @message_id, @language_id, @severity, @text
	END

	CLOSE cur    
	DEALLOCATE cur
END
GO
CREATE OR ALTER TRIGGER DeleteSysMessage ON SysMessagesProxy INSTEAD OF DELETE AS
BEGIN
    DECLARE @message_id INT, @language_id INT, @severity INT, @text NVARCHAR(MAX)
	DECLARE cur CURSOR FOR SELECT message_id, language_id, severity, text FROM Deleted
	OPEN cur

	FETCH NEXT FROM cur INTO @message_id, @language_id, @severity, @text

	WHILE @@FETCH_STATUS = 0 BEGIN
		EXEC sp_dropmessage @message_id, @language_id
		FETCH NEXT FROM cur INTO @message_id, @language_id, @severity, @text
	END

	CLOSE cur    
	DEALLOCATE cur
END
GO
CREATE OR ALTER TRIGGER UpdateSysMEssage ON SysMessagesProxy INSTEAD OF UPDATE AS
BEGIN
    DECLARE @message_id INT, @language_id INT, @severity INT, @text NVARCHAR(MAX)
	DECLARE cur CURSOR FOR SELECT message_id, language_id, severity, text FROM Inserted
	OPEN cur

	FETCH NEXT FROM cur INTO @message_id, @language_id, @severity, @text

	WHILE @@FETCH_STATUS = 0 BEGIN
		DECLARE @languageName NVARCHAR(MAX) = (SELECT name FROM sys.syslanguages WHERE langid = @language_id)
		EXEC sp_addmessage @message_id, @severity, @text, @languageName
		FETCH NEXT FROM cur INTO @message_id, @language_id, @severity, @text
	END

	CLOSE cur    
	DEALLOCATE cur

    DECLARE cur CURSOR FOR SELECT message_id, language_id, severity, text FROM Deleted
	OPEN cur

	FETCH NEXT FROM cur INTO @message_id, @language_id, @severity, @text

	WHILE @@FETCH_STATUS = 0 BEGIN
		EXEC sp_dropmessage @message_id, @language_id
		FETCH NEXT FROM cur INTO @message_id, @language_id, @severity, @text
	END

	CLOSE cur    
	DEALLOCATE cur
END

KevinDavis

This is a little disappointing for me. I can understand why Redgate couldn't deploy to the system database but Data Compare should at least do the compare. I was hoping that this tool might be able to show me values in system tables to help troubleshoot an issue I was having with CRL assemblies between a test and production environment. 

way0utwest

The trick with a view works. We hide system tables because there are so many they can clutter up the view, and because we can't deploy to them, this hasn''t been something we included in the product.