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