Maintaining SQL Server database metadata to identify object changes

By:   |   Comments (4)   |   Related: More > Database Administration


Problem

From time to time, I am called in to troubleshoot and diagnose database issues. I often find that someone has modified the database tables or database source code which forces me to comb through the database to find any changes made since it was created. Is there an easier way to figure out if anything has changed?

Solution

I run into this quite frequently. Changes would get made, they would never get communicated, and I'd find myself going down rabbit holes only to discover that a change was made after creation of the database.

To help myself, I now maintain my own "system tables" which describe what makes up my database. I am diligent about naming every single object in my schema whether it is a CHECK constraint, a DEFAULT constraint, etc. If it goes into the database, it gets a name. Allowing a SQL Server system generated name to enter my model is unacceptable!

In this tip, I will create custom tables which contain the objects that make up an simple database (table, stored procedure). I will then execute a stored procedure that I've written that compares my database against SQL Server's INFORMATION_SCHEMA views to get an idea of what differences (if any) have been made to the database.

The following simple script creates 2 tables which are populated with data based on a table and stored procedure related to a Customer table.

-- create our own system table to hold all our tables
CREATE TABLE dbo.MyTables 

table_name VARCHAR(50) NOT NULL, -- name of table 
column_name VARCHAR(50) NOT NULL, -- column name 

GO 
CREATE UNIQUE CLUSTERED INDEX uq_MyTables ON dbo.MyTables(table_name,column_name
GO 

-- create our system table to hold all our procs
CREATE TABLE dbo.MyProcs 

proc_name VARCHAR(255) NOT NULL, -- name of proc 
proc_size int NOT NULL

GO 
CREATE UNIQUE CLUSTERED INDEX uq_MyProcs ON dbo.MyProcs(proc_name
GO 

-- create user table
CREATE TABLE dbo.Customer 

customerid INT IDENTITY(1,1) NOT NULL, 
firstname VARCHAR(255) NOT NULL, 
lastname VARCHAR(255) NOT NULL 

GO 
ALTER TABLE dbo.Customer 
ADD CONSTRAINT pk_customer PRIMARY KEY (customerid
GO 

-- create user stored procedure
CREATE PROCEDURE dbo.GetCustomer 
AS 
SET
nocount ON 
SELECT
firstname, lastname 
FROM dbo.customer 
GO 

-- load our system tables 
INSERT INTO dbo.MyTables (table_name, column_name)  
SELECT 'Customer' , 'customerid' -- customerid  

INSERT INTO dbo.MyTables (table_name, column_name)  
SELECT 'Customer' , 'firstname' -- firstname  

INSERT INTO dbo.MyTables (table_name, column_name)  
SELECT 'Customer' , 'lastname' -- lastname  

INSERT INTO dbo.MyProcs (proc_name, proc_size)  
SELECT 'GetCustomer' , 282 -- the proc size  
 
GO
 

I will now create a check procedure that can be used to read these tables and determine if the database has changed from when it was first installed. I will check to make sure the table exists and that the stored procedure has not changed.

CREATE PROCEDURE dbo.CheckMyDatabase 
AS 
SET
NOCOUNT ON 

CREATE TABLE
#table (table_name VARCHAR(50)) 
CREATE TABLE #proc (proc_name VARCHAR(255)) 

-- check if all tables present 
INSERT INTO #table (table_name
SELECT table_name 
FROM dbo.MyTables db  
WHERE NOT EXISTS (SELECT 1  
                  
FROM INFORMATION_SCHEMA.TABLES i 
                  
WHERE i.TABLE_NAME = db.table_name 
                  
AND i.TABLE_TYPE = 'BASE TABLE'

IF EXISTS (SELECT 1 FROM #table
BEGIN 
   PRINT
'Missing Tables!' 
  
PRINT '----------------' 
  
SELECT table_name FROM #table 
END 
ELSE 
   PRINT
'All tables accounted for' 

  
-- check if procedures changed 
  
INSERT INTO #proc (proc_name
  
SELECT proc_name 
  
FROM dbo.MyProcs db 
  
INNER JOIN INFORMATION_SCHEMA.ROUTINES i ON i.ROUTINE_NAME = db.proc_name  
  
AND i.ROUTINE_TYPE = 'PROCEDURE' 
  
AND DATALENGTH(OBJECT_DEFINITION(OBJECT_ID(ROUTINE_NAME))) <> db.proc_size 

  
IF EXISTS (SELECT 1 FROM #proc
  
BEGIN 
       PRINT
'The following procedures may have changed' 
      
PRINT '-----------------------------------------' 
      
SELECT proc_name FROM #proc 
  
END 
   ELSE 
       PRINT
'All stored procedures appear consistent' 
GO 

-- Check the database 
-- Change your Results Pane output to "Results To Text" 
EXEC CheckMyDatabase 
GO 
 

You should see the following output

fig 1

Now, let's assume that a developer modified the GetCustomer stored procedure to accept a passed in customerid. Re-running our database check procedure, we can capture that something has changed in the database.

ALTER PROCEDURE dbo.GetCustomer @customerid INT
AS

SET
NOCOUNT ON
SELECT
firstname, lastname
FROM dbo.customer
WHERE customerid = @customerid

GO

-- Re-check the database
-- Change your Results Pane output to "Results To Text"
EXEC CheckMyDatabase
GO
 

fig 2

As you can see, you can get a good idea of any database changes that have occurred before you dig into a problem which can save you a lot of wasted investigative time.

Next Steps
  • Consider renaming any SQL Server specific generated object names in your model to specific, static names
  • Consider recording all the objects that make up your database
  • Read more about the OBJECT_DEFINTION function in the SQL Server Books Online


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Armando Prato Armando Prato has close to 30 years of industry experience and has been working with SQL Server since version 6.5.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Friday, November 14, 2014 - 6:05:23 AM - Manish Kumar Back To Top (35291)

Hi,

Also in other way we can make a trigger on database level and will fire when a database objects become alter or drop or and make a new table for inserting these records .

 

For example : 

CREATE TRIGGER [Test_Trigger_DB]

ON DATABASE

FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,

    Create_Table,ALTER_Table, DROP_Table,

    Create_View,ALTER_View, DROP_View

 

Also in that we can capture  Host name, DB name etc.


Tuesday, November 11, 2014 - 6:00:08 PM - Don Schaeffer Back To Top (35257)

Using Redgate's tools I save SQL Compare snapshots of all databases every week.  These are not large.  Then I can compare the table structure, stored procedures, etc of any current database to what it looked like historically.


Tuesday, September 9, 2008 - 10:36:53 AM - aprato Back To Top (1770)

 Hi

Thanks for the alternate solution.  In my situation, it's not feasible to expect that all our customers (hundreds of them) will have SB enabled. In some cases, our customers dictate to us what they allow to be enabled and what they don't (for instance, xp_cmdshell).  The software is shipped to the customer where it is installed on their server.  It may be modified manually either by them or by a support rep after the fact.  The tables represent the structure of the database as it was shipped to the customer.  Once they get the software and install it, it's out of our hands as their DBA's (if they have one - a lot of times it's an application developer who has some basic knowledge of SQL Server) take over. Using the approach in the tip, I know I can always interrogate what's in the tables vs what's in the catalogs and know what has definitely changed. I don't have to worry if the customer has disabled Service Broker (on purpose or accidentally).


Tuesday, September 9, 2008 - 8:41:40 AM - lpesdan Back To Top (1769)

To store all the database objects again in a SQL database with there definition is redundant and old school.  Use the service broker and have it write any changes in the schema of sql to a database.  This is realtime and you don't need to poll all the object every day to see what changed.  You will need to create a table for the audited changes and a stored procedure to grab the changes from the broker.  This will save you allot of resources and time.

 

----------------------------------------------------------------------------------------
--  Created By: Laurence
-- Created On: 7/1/2007
--The statements below setup sql audting for various sql objects.  The Code sets up a services Broker with
-- queues, Services and routes to capture certain types of SQL Objects that Change and then
-= in turn calls a stored procedure to log the changes into a SQL server table
--
--
--
--
-------------------------------------------------------------------------------------------------

DROP EVENT NOTIFICATION Audit_Notify_DB_Changes ON SERVER
DROP ROUTE NotifyRoute
DROP SERVICE NotifyService
DROP QUEUE NotifyQueue
USE GbAppsAudit
ALTER DATABASE GBAppsAudit SET ENABLE_BROKER;

--Create a queue to receive messages.
CREATE QUEUE NotifyQueue ;
GO
--Create a service on the queue that references
--the event notifications contract.
CREATE SERVICE NotifyService
ON QUEUE NotifyQueue
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO
--Create a route on the service to define the address
--to which Service Broker sends messages for the service.
CREATE ROUTE NotifyRoute
WITH SERVICE_NAME = 'NotifyService',
ADDRESS = 'LOCAL';
GO


CREATE EVENT NOTIFICATION Audit_Notify_DB_Changes
ON SERVER
FOR CREATE_DATABASE,ALTER_DATABASE,DROP_DATABASE,CREATE_INDEX,ALTER_INDEX,DROP_INDEX,CREATE_LOGIN,ALTER_LOGIN,DROP_LOGIN,CREATE_TABLE,ALTER_TABLE,DROP_TABLE,CREATE_TRIGGER,ALTER_TRIGGER,DROP_TRIGGER,CREATE_USER,ALTER_USER,DROP_USER,CREATE_VIEW,ALTER_VIEW,DROP_VIEW,CREATE_FUNCTION,ALTER_FUNCTION,DROP_FUNCTION,CREATE_PROCEDURE,ALTER_PROCEDURE,DROP_PROCEDURE

TO SERVICE 'NotifyService', 'current database'

ALTER QUEUE [dbo].[NotifyQueue]  
 WITH ACTIVATION (        -- Activation turned on      
  STATUS = ON,        -- The name of the proc to process messages for this queue                                    
  PROCEDURE_NAME = [dbo].[gsp_Audit_SQL_Server_Objects] ,        -- The maximum number of copies of the proc to start       
  MAX_QUEUE_READERS = 2,        -- Start the procedure as the user who created the queue.                            
EXECUTE AS SELF                                ) ;

 















get free sql tips
agree to terms