solving sql server problems for millions of dbas and developers since 2006

Maintaining SQL Server database metadata to identify object changes

By: Armando Prato | Read Comments (2) | Print

Armando has over 24 years of industry experience and has been working with SQL Server since version 6.5.

Related Tips: More

Problem
My company sells a packaged software solution to our customers. From time to time, I am called in to troubleshoot and diagnose customer database issues. A lot of times, I find that our support department has added indexes to the customer's model or modified their database source code. Unfortunately, I have to comb through the database to find these changes. Is there a better way?

Solution
Alas, in the past I used to 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 delivery of the software.

For every software release, I now maintain metadata tables about what makes up my database. I am diligent about naming every single object in my schema whether it be a CHECK constraint, a DEFAULT constraint, etc. If it goes into the database, it gets a name... allowing a SQL Server system generated name in my model is unacceptable!

In my model, I create and maintain 2 metadata tables. The first table contains a list of all the objects that make up a standard, delivered database (i.e. tables, constraints, stored procedures, etc). The 2nd table is a child table that lists all the columns, their datatypes, their ordinal position, etc for each object defined in the primary table (if applicable). I can then execute a stored procedure that I've written that compares the metadata tables of the current database against the INFORMATION_SCHEMA views along with select system tables to get an idea of what differences (if any) have been made to the database after it has been installed.

The following scaled down sample script ilustrates this concept. It first creates a parent table that catalogs all objects that make my database. It then creates a child table that catalogs all columns that make up each of the parent entries provided that parent entry is a table, index, foreign key, etc. The 2 tables are populated with metadata based on a schema table called Customer. I catalog the table, the table's columns and ordinal positions, the primary key, the PK's column, and the stored procedure I created along with it's length in bytes. Capturing the stored procedure's length in bytes, I can get an idea if a procedure has changed . It gives me a head's up that I may have to look at this procedure a little bit more closely.

-- create table to hold schema objects 
CREATE TABLE dbo.dbSchemaObject  
(  
objectName VARCHAR(255) NOT NULL, -- name of object  
objectType VARCHAR(3) NOT NULL, -- type of object  
parentObjectName VARCHAR(255) NULL, -- the parent object for this object  
isUniqueIndex bit NULL, -- for indexes  
isClusteredIndex bit NULL, -- for indexes  
isCascadeDelete bit NULL, -- for foreign keys  
isCascadeUpdate bit NULL, -- for foreign keys  
codeLength INT NULL -- for procs, triggers, views, functions   
)  
GO  
CREATE UNIQUE CLUSTERED INDEX uq_dbSchemaObject ON dbo.dbSchemaObject(objectName)  
GO  
ALTER TABLE dbo.dbSchemaObject   
ADD CONSTRAINT fk_dbSchemaObject FOREIGN KEY (parentObjectName) REFERENCES dbo.dbSchemaObject(objectName)  
GO  

-- create table to hold schema columns 
CREATE TABLE dbo.dbSchemaColumn  
(  
objectName VARCHAR(255) NOT NULL,  
columnName VARCHAR(255) NOT NULL,  
dataType VARCHAR(50) NULL,  
ordinal_position INT NOT NULL,  
isNullable bit NOT NULL  
)  
GO  
ALTER TABLE dbo.dbSchemaColumn  
ADD CONSTRAINT pk_dbSchemaColumn PRIMARY KEY (objectName, columnName)  
GO  
ALTER TABLE dbo.dbSchemaColumn  
ADD CONSTRAINT fk_dbSchemaColumn FOREIGN KEY (objectName) REFERENCES dbo.dbSchemaObject(objectName)  
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 the schema object table with parent objects  
INSERT INTO dbo.dbSchemaObject (objectName, objectType)   
SELECT 'customer', 'T' -- table object  
INSERT INTO dbo.dbSchemaObject (objectName, objectType, parentObjectName)   
SELECT 'pk_customer', 'PK', 'Customer' -- Primary Key  
INSERT INTO dbo.dbSchemaObject (objectName, objectType, codeLength)   
SELECT 'GetCustomer', 'P', LEN(object_definition(OBJECT_ID('GetCustomer'))) -- Stored Procedure  

-- load the schema column table with object attributes  
INSERT INTO dbo.dbSchemaColumn (objectName, columnName, datatype, ordinal_position, isNullable)  
SELECT 'customer', 'customerid', 'int', 1, 0  
INSERT INTO dbo.dbSchemaColumn (objectName, columnName, datatype, ordinal_position, isNullable)  
SELECT 'customer', 'firstname', 'varchar', 2, 0  
INSERT INTO dbo.dbSchemaColumn (objectName, columnName, datatype, ordinal_position, isNullable)  
SELECT 'customer', 'lastname', 'varchar', 3, 0  
INSERT INTO dbo.dbSchemaColumn (objectName, columnName, ordinal_position, isNullable)  
SELECT 'pk_customer', 'customerid', 1, 0  
GO

I will now create a database schema checking program that can be used to read these tables and determine if the database has changed from its originally released version. In this sample, I will check to make sure all tables are present and that no stored procedures have changed.

CREATE PROCEDURE dbo.CheckDatabase  
AS  
SET nocount ON  

CREATE TABLE #table (objectName VARCHAR(255))  
CREATE TABLE #proc (objectName VARCHAR(255))  

-- check if all tables present  
INSERT INTO #table (objectName)  
SELECT objectName  
FROM dbo.dbSchemaObject db   
WHERE objectType = 'T'  
AND NOT EXISTS (SELECT 1   
                   FROM information_schema.tables i  
                   WHERE i.table_name = db.objectName  
                   AND table_type = 'base table')  

IF EXISTS (SELECT 1 FROM #table)  
BEGIN  
   PRINT 'The following tables are missing'  
   PRINT '--------------------------------'  
   SELECT objectName FROM #table  
END  
ELSE  
   PRINT 'All tables accounted for'  

   -- check if procedures changed  
   INSERT INTO #proc (objectName)  
   SELECT objectName  
   FROM dbo.dbSchemaObject db  
   JOIN information_schema.routines i ON i.routine_name = db.objectName   
   AND routine_type = 'procedure'  
   WHERE objectType = 'P'  
   AND LEN(object_definition(OBJECT_ID(routine_name))) <> db.codeLength  

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

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

You should see the following output

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 CheckDatabase 
GO

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 creating metadata tables that can be interrogated and that document what makes up an instance of your database
Read more about the OBJECT_DEFINTION function in the SQL Server 2005 Books Online

Related Tips: More | Become a paid author

Last Update: 7/1/2008

Share:

Free SQL Server Learning

Comments and Feedback:

Tuesday, September 09, 2008 - 8:41:40 AM - lpesdan

Read The Tip

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                                ) ;

Tuesday, September 09, 2008 - 10:36:53 AM - aprato

Read The Tip

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).

New SQL Monitor v3.0

Try it live

Sponsor Information

"SQL doctor ROCKS! As soon as I ran it, problems that have been giving me headaches were identified and cured."

SQL Monitor – For database professionals who need results on Day One. Try it online.

What grade do you think your SQL Servers get? Find out with Edgewood's SQL Server Health Check consulting services.

Get SQL Server Tips Straight from Kevin Kline.

Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now.

Demystify TempDB Performance and Manageability