Compare master database sys tables

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.
Tagged:

Answers

  • sam.blackburnsam.blackburn Posts: 224 Gold 2
    edited September 25, 2017 4:07PM
    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)?
    Software Developer
    Redgate Software
  • dtaylo4dtaylo4 Posts: 3 New member
    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>'
    
  • 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
  • dtaylo4dtaylo4 Posts: 3 New member
    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.
  • 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.blackburnsam.blackburn Posts: 224 Gold 2
    edited October 26, 2017 2:51PM
    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!
    Software Developer
    Redgate Software
  • That's a neat trick. Just tried it and it worked great. Didn't know we could do this.
  • 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
    
    Software Developer
    Redgate Software
  • KevinDavisKevinDavis Posts: 3 Bronze 1
    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. 
  • 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.
Sign In or Register to comment.