Here is a sample poor man's solution for a trigger implementing tracking of changes on db objects ( via DDL stateements ) on a sql server 2005 / 2008 database. I contains also a simple sample of how-to enforce the usage of required someValue xml tag in the source code for each sql command ran on the database + the tracking of the current db version and type ( dev , test , qa , fb , prod)
One could extend it with additional required attributes such as , etc.
The code is rather long - it creates the empty database + the needed tracking table structure + required db functions and the populating trigger all running under a [ga] schema.
USE [master]
GO
/****** Object: Database [DBGA_DEV] Script Date: 04/22/2009 13:22:01 ******/
CREATE DATABASE [DBGA_DEV] ON PRIMARY
( NAME = N'DBGA_DEV', FILENAME = N'D:\GENAPP\DATA\DBFILES\DBGA_DEV.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'DBGA_DEV_log', FILENAME = N'D:\GENAPP\DATA\DBFILES\DBGA_DEV_log.ldf' , SIZE = 6208KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [DBGA_DEV] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [DBGA_DEV].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [DBGA_DEV] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [DBGA_DEV] SET ANSI_NULLS OFF
GO
ALTER DATABASE [DBGA_DEV] SET ANSI_PADDING ON
GO
ALTER DATABASE [DBGA_DEV] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [DBGA_DEV] SET ARITHABORT OFF
GO
ALTER DATABASE [DBGA_DEV] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [DBGA_DEV] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [DBGA_DEV] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [DBGA_DEV] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [DBGA_DEV] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [DBGA_DEV] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [DBGA_DEV] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [DBGA_DEV] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [DBGA_DEV] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [DBGA_DEV] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [DBGA_DEV] SET DISABLE_BROKER
GO
ALTER DATABASE [DBGA_DEV] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [DBGA_DEV] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [DBGA_DEV] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [DBGA_DEV] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [DBGA_DEV] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [DBGA_DEV] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [DBGA_DEV] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [DBGA_DEV] SET READ_WRITE
GO
ALTER DATABASE [DBGA_DEV] SET RECOVERY FULL
GO
ALTER DATABASE [DBGA_DEV] SET MULTI_USER
GO
ALTER DATABASE [DBGA_DEV] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [DBGA_DEV] SET DB_CHAINING OFF
GO
EXEC [DBGA_DEV].sys.sp_addextendedproperty @name=N'DbType', @value=N'DEV'
GO
EXEC [DBGA_DEV].sys.sp_addextendedproperty @name=N'DbVersion', @value=N'0.0.1.20090414.1100'
GO
USE [DBGA_DEV]
GO
/****** Object: Schema [ga] Script Date: 04/22/2009 13:21:29 ******/
CREATE SCHEMA [ga] AUTHORIZATION [dbo]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Contains the objects of the Generic Application database' , @level0type=N'SCHEMA',@level0name=N'ga'
GO
/****** Object: Table [ga].[tb_DataMeta_ObjChangeLog] Script Date: 04/22/2009 13:21:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [ga].[tb_DataMeta_ObjChangeLog](
[LogId] [int] IDENTITY(1,1) NOT NULL,
[TimeStamp] [timestamp] NOT NULL,
[DatabaseName] [varchar](256) NOT NULL,
[SchemaName] [varchar](256) NOT NULL,
[DbVersion] [varchar](20) NOT NULL,
[DbType] [varchar](20) NOT NULL,
[EventType] [varchar](50) NOT NULL,
[ObjectName] [varchar](256) NOT NULL,
[ObjectType] [varchar](25) NOT NULL,
[Version] [varchar](50) NULL,
[SqlCommand] [varchar](max) NOT NULL,
[EventDate] [datetime] NOT NULL,
[LoginName] [varchar](256) NOT NULL,
[FirstName] [varchar](256) NULL,
[LastName] [varchar](50) NULL,
[ChangeDescription] [varchar](1000) NULL,
[Description] [varchar](1000) NULL,
[ObjVersion] [varchar](20) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The database version as written in the extended prop of the database' , @level0type=N'SCHEMA',@level0name=N'ga', @level1type=N'TABLE',@level1name=N'tb_DataMeta_ObjChangeLog', @level2type=N'COLUMN',@level2name=N'DbVersion'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'dev , test , qa , fb or prod' , @level0type=N'SCHEMA',@level0name=N'ga', @level1type=N'TABLE',@level1name=N'tb_DataMeta_ObjChangeLog', @level2type=N'COLUMN',@level2name=N'DbType'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The name of the object as it is registered in the sys.objects ' , @level0type=N'SCHEMA',@level0name=N'ga', @level1type=N'TABLE',@level1name=N'tb_DataMeta_ObjChangeLog', @level2type=N'COLUMN',@level2name=N'ObjectName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'' , @level0type=N'SCHEMA',@level0name=N'ga', @level1type=N'TABLE',@level1name=N'tb_DataMeta_ObjChangeLog', @level2type=N'COLUMN',@level2name=N'Description'
GO
SET IDENTITY_INSERT [ga].[tb_DataMeta_ObjChangeLog] ON
INSERT [ga].[tb_DataMeta_ObjChangeLog] ([LogId], [DatabaseName], [SchemaName], [DbVersion], [DbType], [EventType], [ObjectName], [ObjectType], [Version], [SqlCommand], [EventDate], [LoginName], [FirstName], [LastName], [ChangeDescription], [Description], [ObjVersion]) VALUES (3, N'DBGA_DEV', N'en', N'0.0.1.20090414.1100', N'DEV', N'DROP_TABLE', N'tb_BL_Products', N'TABLE', N' some', N'<EVENT_INSTANCE><EventType>DROP_TABLE</EventType><PostTime>2009-04-22T11:03:11.880</PostTime><SPID>57</SPID><ServerName>YSG</ServerName><LoginName>ysg\yordgeor</LoginName><UserName>dbo</UserName><DatabaseName>DBGA_DEV</DatabaseName><SchemaName>en</SchemaName><ObjectName>tb_BL_Products</ObjectName><ObjectType>TABLE</ObjectType><TSQLCommand><SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE"/><CommandText>drop TABLE [en].[tb_BL_Products] --<Version> some</Version>
</CommandText></TSQLCommand></EVENT_INSTANCE>', CAST(0x00009BF300B6271C AS DateTime), N'ysg\yordgeor', N'Yordan', N'Georgiev', NULL, NULL, N'0.0.0')
INSERT [ga].[tb_DataMeta_ObjChangeLog] ([LogId], [DatabaseName], [SchemaName], [DbVersion], [DbType], [EventType], [ObjectName], [ObjectType], [Version], [SqlCommand], [EventDate], [LoginName], [FirstName], [LastName], [ChangeDescription], [Description], [ObjVersion]) VALUES (4, N'DBGA_DEV', N'en', N'0.0.1.20090414.1100', N'DEV', N'CREATE_TABLE', N'tb_BL_Products', N'TABLE', N' 2.2.2 ', N'<EVENT_INSTANCE><EventType>CREATE_TABLE</EventType><PostTime>2009-04-22T11:03:18.620</PostTime><SPID>57</SPID><ServerName>YSG</ServerName><LoginName>ysg\yordgeor</LoginName><UserName>dbo</UserName><DatabaseName>DBGA_DEV</DatabaseName><SchemaName>en</SchemaName><ObjectName>tb_BL_Products</ObjectName><ObjectType>TABLE</ObjectType><TSQLCommand><SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE"/><CommandText>CREATE TABLE [en].[tb_BL_Products](
[ProducId] [int] NULL,
[ProductName] [nchar](10) NULL,
[ProductDescription] [varchar](5000) NULL
) ON [PRIMARY]
/*
<Version> 2.2.2 </Version>

*/
</CommandText></TSQLCommand></EVENT_INSTANCE>', CAST(0x00009BF300B62F07 AS DateTime), N'ysg\yordgeor', N'Yordan', N'Georgiev', NULL, NULL, N'0.0.0')
INSERT [ga].[tb_DataMeta_ObjChangeLog] ([LogId], [DatabaseName], [SchemaName], [DbVersion], [DbType], [EventType], [ObjectName], [ObjectType], [Version], [SqlCommand], [EventDate], [LoginName], [FirstName], [LastName], [ChangeDescription], [Description], [ObjVersion]) VALUES (5, N'DBGA_DEV', N'en', N'0.0.1.20090414.1100', N'DEV', N'DROP_TABLE', N'tb_BL_Products', N'TABLE', N' 2.2.2 ', N'<EVENT_INSTANCE><EventType>DROP_TABLE</EventType><PostTime>2009-04-22T11:25:12.620</PostTime><SPID>57</SPID><ServerName>YSG</ServerName><LoginName>ysg\yordgeor</LoginName><UserName>dbo</UserName><DatabaseName>DBGA_DEV</DatabaseName><SchemaName>en</SchemaName><ObjectName>tb_BL_Products</ObjectName><ObjectType>TABLE</ObjectType><TSQLCommand><SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE"/><CommandText>drop TABLE [en].[tb_BL_Products] 
</CommandText></TSQLCommand></EVENT_INSTANCE>', CAST(0x00009BF300BC32F1 AS DateTime), N'ysg\yordgeor', N'Yordan', N'Georgiev', NULL, NULL, N'0.0.0')
INSERT [ga].[tb_DataMeta_ObjChangeLog] ([LogId], [DatabaseName], [SchemaName], [DbVersion], [DbType], [EventType], [ObjectName], [ObjectType], [Version], [SqlCommand], [EventDate], [LoginName], [FirstName], [LastName], [ChangeDescription], [Description], [ObjVersion]) VALUES (6, N'DBGA_DEV', N'en', N'0.0.1.20090414.1100', N'DEV', N'CREATE_TABLE', N'tb_BL_Products', N'TABLE', N' 2.2.2 ', N'<EVENT_INSTANCE><EventType>CREATE_TABLE</EventType><PostTime>2009-04-22T11:25:19.053</PostTime><SPID>57</SPID><ServerName>YSG</ServerName><LoginName>ysg\yordgeor</LoginName><UserName>dbo</UserName><DatabaseName>DBGA_DEV</DatabaseName><SchemaName>en</SchemaName><ObjectName>tb_BL_Products</ObjectName><ObjectType>TABLE</ObjectType><TSQLCommand><SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE"/><CommandText>CREATE TABLE [en].[tb_BL_Products](
[ProducId] [int] NULL,
[ProductName] [nchar](10) NULL,
[ProductDescription] [varchar](5000) NULL
) ON [PRIMARY]
/*
<Version> 2.2.2 </Version>

*/
</CommandText></TSQLCommand></EVENT_INSTANCE>', CAST(0x00009BF300BC3A69 AS DateTime), N'ysg\yordgeor', N'Yordan', N'Georgiev', NULL, NULL, N'0.0.0')
SET IDENTITY_INSERT [ga].[tb_DataMeta_ObjChangeLog] OFF
/****** Object: Table [ga].[tb_BLSec_LoginsForUsers] Script Date: 04/22/2009 13:21:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [ga].[tb_BLSec_LoginsForUsers](
[LoginsForUsersId] [int] IDENTITY(1,1) NOT NULL,
[LoginName] [nvarchar](100) NOT NULL,
[FirstName] [varchar](100) NOT NULL,
[SecondName] [varchar](100) NULL,
[LastName] [varchar](100) NOT NULL,
[DomainName] [varchar](100) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET IDENTITY_INSERT [ga].[tb_BLSec_LoginsForUsers] ON
INSERT [ga].[tb_BLSec_LoginsForUsers] ([LoginsForUsersId], [LoginName], [FirstName], [SecondName], [LastName], [DomainName]) VALUES (1, N'ysg\yordgeor', N'Yordan', N'Stanchev', N'Georgiev', N'yordgeor')
SET IDENTITY_INSERT [ga].[tb_BLSec_LoginsForUsers] OFF
/****** Object: Table [en].[tb_BL_Products] Script Date: 04/22/2009 13:21:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [en].[tb_BL_Products](
[ProducId] [int] NULL,
[ProductName] [nchar](10) NULL,
[ProductDescription] [varchar](5000) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: StoredProcedure [ga].[procUtils_SqlCheatSheet] Script Date: 04/22/2009 13:21:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [ga].[procUtils_SqlCheatSheet]
as
set nocount on
--what was the name of the table with something like role
/*
SELECT * from sys.tables where [name] like '%POC%'
*/
-- what are the columns of this table
/*
select column_name , DATA_TYPE , CHARACTER_MAXIMUM_LENGTH, table_name from Information_schema.columns where table_name='tbGui_ExecutePOC'
*/
-- find proc
--what was the name of procedure with something like role
/*
select * from sys.procedures where [name] like '%ext%'
exec sp_HelpText procName
*/
/*
exec sp_helpText procUtils_InsertGenerator
*/
--how to list all databases in sql server
/*
SELECT database_id AS ID, NULL AS ParentID, name AS Text FROM sys.databases ORDER BY [name]
*/
--HOW-TO LIST ALL TABLES IN A SQL SERVER 2005 DATABASE
/*
SELECT TABLE_NAME FROM [POC].INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME <> 'dtproperties'
ORDER BY TABLE_NAME
*/
--HOW-TO ENABLE XP_CMDSHELL START
-------------------------------------------------------------------------
-- configure verbose mode temporarily
-- EXECUTE sp_configure 'show advanced options', 1
-- RECONFIGURE WITH OVERRIDE
--GO
--ENABLE xp_cmdshell
-- EXECUTE sp_configure 'xp_cmdshell', '1'
-- RECONFIGURE WITH OVERRIDE
-- EXEC SP_CONFIGURE 'show advanced option', '1';
-- SHOW THE CONFIGURATION
-- EXEC SP_CONFIGURE;
--turn show advance options off
-- GO
--EXECUTE sp_configure 'show advanced options', 0
-- RECONFIGURE WITH OVERRIDE
-- GO
--HOW-TO ENABLE XP_CMDSHELL END
-------------------------------------------------------------------------
--HOW-TO IMPLEMENT SLEEP
-- sleep for 10 seconds
-- WAITFOR DELAY '00:00:10' SELECT * FROM My_Table
/* LIST ALL PRIMARY KEYS
SELECT
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME AS TABLE_NAME,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME AS COLUMN_NAME,
REPLACE(INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE,' ', '_') AS CONSTRAINT_TYPE
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ON
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME =
INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
WHERE
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME <> N'sysdiagrams'
ORDER BY
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME ASC
*/
--HOW-TO COPY TABLE AND THE WHOLE TABLE DATA , COPY TABLE FROM DB TO DB
--==================================================START
/*
use Poc_Dev
go
drop table tbGui_LinksVisibility
use POc_test
go
select *
INTO [POC_Dev].[ga].[tbGui_LinksVisibility]
from [POC_TEST].[ga].[tbGui_LinksVisibility]
*/
--HOW-TO COPY TABLE AND THE WHOLE TABLE DATA , COPY TABLE FROM DB TO DB
--====================================================END
--=================================================== SEE TABLE METADATA START
/*
SELECT c.name AS [COLUMN_NAME], sc.data_type AS [DATA_TYPE], [value] AS
[DESCRIPTION] , c.max_length as [MAX_LENGTH] , c.is_nullable AS [OPTIONAL]
, c.is_identity AS [IS_PRIMARY_KEY] FROM sys.extended_properties AS ep
INNER JOIN sys.tables AS t ON ep.major_id = t.object_id
INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id
= c.column_id
INNER JOIN INFORMATION_SCHEMA.COLUMNS sc ON t.name = sc.table_name and
c.name = sc.column_name
WHERE class = 1 and t.name = 'tbGui_ExecutePOC' ORDER BY SC.DATA_TYPE
*/
--=================================================== SEE TABLE METADATA END
/*
select * from Information_schema.columns
select table_name , column_name from Information_schema.columns where table_name='tbGui_Wizards'
*/
--=================================================== LIST ALL TABLES AND THEIR DESCRIPTOINS START
/*
SELECT T.name AS TableName, CAST(Props.value AS varchar(1000)) AS
TableDescription
FROM sys.tables AS T LEFT OUTER JOIN
(SELECT class, class_desc, major_id, minor_id,
name, value
FROM sys.extended_properties
WHERE (minor_id = 0) AND (class = 1)) AS
Props ON T.object_id = Props.major_id
WHERE (T.type = 'U') AND (T.name <> N'sysdiagrams')
ORDER BY TableName
*/
--=================================================== LIST ALL TABLES AND THEIR DESCRIPTOINS START
--=================================================== LIST ALL OBJECTS FROM DB START
/*
use DB
--HOW-TO LIST ALL PROCEDURE IN A DATABASE
select s.name from sysobjects s where type = 'P'
--HOW-TO LIST ALL TRIGGERS BY NAME IN A DATABASE
select s.name from sysobjects s where type = 'TR'
--HOW-TO LIST TABLES IN A DATABASE
select s.name from sysobjects s where type = 'U'
--how-to list all system tables in a database
select s.name from sysobjects s where type = 's'
--how-to list all the views in a database
select s.name from sysobjects s where type = 'v'
*/
/*
Similarly you can find out other objects created by user, simple change type =
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = In-lined table-function
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table ( this is the one I discussed above in the example)
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure
*/
--=================================================== HOW-TO SEE ALL MY PERMISSIONS START
/*
SELECT * FROM fn_my_permissions(NULL, 'SERVER');
USE poc_qa;
SELECT * FROM fn_my_permissions (NULL, 'database');
GO
*/
--=================================================== HOW-TO SEE ALL MY PERMISSIONS END
/*
--find table
use poc_dev
go
select s.name from sysobjects s where type = 'u' and s.name like '%Visibility%'
select * from tbGui_LinksVisibility
*/
/* find cursor
use poc
go
DECLARE @procName varchar(100)
DECLARE @cursorProcNames CURSOR
SET @cursorProcNames = CURSOR FOR
select name from sys.procedures where modify_date > '2009-02-05 13:12:15.273' order by modify_date desc
OPEN @cursorProcNames
FETCH NEXT
FROM @cursorProcNames INTO @procName
WHILE @@FETCH_STATUS = 0
BEGIN
set nocount off;
exec sp_HelpText @procName --- or print them
-- print @procName
FETCH NEXT
FROM @cursorProcNames INTO @procName
END
CLOSE @cursorProcNames
select @@error
*/
/* -- SEE STORED PROCEDURE EXT PROPS
SELECT ep.name as 'EXT_PROP_NAME' , SP.NAME , [value] as 'DESCRIPTION' FROM sys.extended_properties as ep left join sys.procedures as sp on sp.object_id = ep.major_id where sp.type='P'
-- what the hell I ve been doing lately on sql server 2005 / 2008
select o.name ,
(SELECT [definition] AS [text()] FROM sys.all_sql_modules where sys.all_sql_modules.object_id=a.object_id FOR XML PATH(''), TYPE) AS Statement_Text
, a.object_id, o.modify_date from sys.all_sql_modules a left join sys.objects o on a.object_id=o.object_id order by 4 desc
-- GET THE RIGHT LANG SCHEMA START
DECLARE @template AS varchar(max)
SET @template = 'SELECT * FROM {object_name}'
DECLARE @object_name AS sysname
SELECT @object_name = QUOTENAME(s.name) + '.' + QUOTENAME(o.name)
FROM sys.objects o
INNER JOIN sys.schemas s
ON s.schema_id = o.schema_id
WHERE o.object_id = OBJECT_ID(QUOTENAME(@LANG) + '.[TestingLanguagesInNameSpacesDelMe]')
IF @object_name IS NOT NULL
BEGIN
DECLARE @sql AS varchar(max)
SET @sql = REPLACE(@template, '{object_name}', @object_name)
EXEC (@sql)
END
-- GET THE RIGHT LANG SCHEMA END
-- SEE STORED PROCEDURE EXT PROPS end*/
set nocount off
GO
EXEC sys.sp_addextendedproperty @name=N'AuthorName', @value=N'Yordan Georgiev' , @level0type=N'SCHEMA',@level0name=N'ga', @level1type=N'PROCEDURE',@level1name=N'procUtils_SqlCheatSheet'
GO
EXEC sys.sp_addextendedproperty @name=N'ProcDescription', @value=N'TODO:ADD HERE DESCRPIPTION' , @level0type=N'SCHEMA',@level0name=N'ga', @level1type=N'PROCEDURE',@level1name=N'procUtils_SqlCheatSheet'
GO
EXEC sys.sp_addextendedproperty @name=N'ProcVersion', @value=N'0.1.0.20090406.1317' , @level0type=N'SCHEMA',@level0name=N'ga', @level1type=N'PROCEDURE',@level1name=N'procUtils_SqlCheatSheet'
GO
/****** Object: UserDefinedFunction [ga].[GetDbVersion] Script Date: 04/22/2009 13:21:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [ga].[GetDbVersion]()
RETURNS VARCHAR(20)
BEGIN
RETURN convert(varchar(20) , (select value from sys.extended_properties where name='DbVersion' and class_desc='DATABASE') )
END
GO
EXEC sys.sp_addextendedproperty @name=N'AuthorName', @value=N'Yordan Georgiev' , @level0type=N'SCHEMA',@level0name=N'ga', @level1type=N'FUNCTION',@level1name=N'GetDbVersion'
GO
EXEC sys.sp_addextendedproperty @name=N'ChangeDescription', @value=N'Initial creation' , @level0type=N'SCHEMA',@level0name=N'ga', @level1type=N'FUNCTION',@level1name=N'GetDbVersion'
GO
EXEC sys.sp_addextendedproperty @name=N'CreatedWhen', @value=N'getDate()' , @level0type=N'SCHEMA',@level0name=N'ga', @level1type=N'FUNCTION',@level1name=N'GetDbVersion'
GO
EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'Gets the current version of the database ' , @level0type=N'SCHEMA',@level0name=N'ga', @level1type=N'FUNCTION',@level1name=N'GetDbVersion'
GO
/****** Object: UserDefinedFunction [ga].[GetDbType] Script Date: 04/22/2009 13:21:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [ga].[GetDbType]()
RETURNS VARCHAR(30)
BEGIN
RETURN convert(varchar(30) , (select value from sys.extended_properties where name='DbType' and class_desc='DATABASE') )
END
GO
/****** Object: Default [DF_tb_DataMeta_ObjChangeLog_DbVersion] Script Date: 04/22/2009 13:21:40 ******/
ALTER TABLE [ga].[tb_DataMeta_ObjChangeLog] ADD CONSTRAINT [DF_tb_DataMeta_ObjChangeLog_DbVersion] DEFAULT ('select ga.GetDbVersion()') FOR [DbVersion]
GO
/****** Object: Default [DF_tb_DataMeta_ObjChangeLog_EventDate] Script Date: 04/22/2009 13:21:40 ******/
ALTER TABLE [ga].[tb_DataMeta_ObjChangeLog] ADD CONSTRAINT [DF_tb_DataMeta_ObjChangeLog_EventDate] DEFAULT (getdate()) FOR [EventDate]
GO
/****** Object: Default [DF_tb_DataMeta_ObjChangeLog_ObjVersion] Script Date: 04/22/2009 13:21:40 ******/
ALTER TABLE [ga].[tb_DataMeta_ObjChangeLog] ADD CONSTRAINT [DF_tb_DataMeta_ObjChangeLog_ObjVersion] DEFAULT ('0.0.0') FOR [ObjVersion]
GO
/****** Object: DdlTrigger [trigMetaDoc_TraceDbChanges] Script Date: 04/22/2009 13:21:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create trigger [trigMetaDoc_TraceDbChanges]
on database
for create_procedure, alter_procedure, drop_procedure,
create_table, alter_table, drop_table,
create_function, alter_function, drop_function ,
create_trigger , alter_trigger , drop_trigger
as
set nocount on
declare @data xml
set @data = EVENTDATA()
declare @DbVersion varchar(20)
set @DbVersion =(select ga.GetDbVersion())
declare @DbType varchar(20)
set @DbType =(select ga.GetDbType())
declare @DbName varchar(256)
set @DbName [email protected]('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)')
declare @EventType varchar(256)
set @EventType [email protected]('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)')
declare @ObjectName varchar(256)
set @ObjectName = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)')
declare @ObjectType varchar(25)
set @ObjectType = @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)')
declare @TSQLCommand varchar(max)
set @TSQLCommand = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)')
declare @opentag varchar(4)
set @opentag= '<'
declare @closetag varchar(4)
set @closetag= '>'
declare @newDataTxt varchar(max)
set @newDataTxt= cast(@data as varchar(max))
set @newDataTxt = REPLACE ( REPLACE(@newDataTxt , @opentag , '<') , @closetag , '>')
-- print @newDataTxt
declare @newDataXml xml
set @newDataXml = CONVERT ( xml , @newDataTxt)
declare @Version varchar(50)
set @Version = @newDataXml.value('(/EVENT_INSTANCE/TSQLCommand/CommandText/Version)[1]', 'varchar(50)')
-- if we are dropping take the version from the existing object
if ( SUBSTRING(@EventType , 0 , 5)) = 'DROP'
set @Version =( select top 1 [Version] from ga.tb_DataMeta_ObjChangeLog where ObjectName=@ObjectName order by [LogId] desc)
declare @LoginName varchar(256)
set @LoginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
declare @FirstName varchar(50)
set @FirstName= (select [FirstName] from [ga].[tb_BLSec_LoginsForUsers] where [LoginName] = @LoginName)
declare @LastName varchar(50)
set @LastName = (select [LastName] from [ga].[tb_BLSec_LoginsForUsers] where [LoginName] = @LoginName)
declare @SchemaName sysname
set @SchemaName = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
--declare @Description xml
--set @Description = @data.query('(/EVENT_INSTANCE/TSQLCommand/text())')
--print 'VERSION IS ' + @Version
--print @newDataTxt
--print cast(@data as varchar(max))
-- select column_name from information_schema.columns where table_name ='tb_DataMeta_ObjChangeLog'
insert into [ga].[tb_DataMeta_ObjChangeLog]
(
[DatabaseName] ,
[SchemaName],
[DbVersion] ,
[DbType],
[EventType],
[ObjectName],
[ObjectType] ,
[Version],
[SqlCommand] ,
[LoginName] ,
[FirstName],
[LastName]
)
values(
@DbName,
@SchemaName,
@DbVersion,
@DbType,
@EventType,
@ObjectName,
@ObjectType ,
@Version,
@newDataTxt,
@LoginName ,
@FirstName ,
@LastName
)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
DISABLE TRIGGER [trigMetaDoc_TraceDbChanges] ON DATABASE
GO
/****** Object: DdlTrigger [trigMetaDoc_TraceDbChanges] Script Date: 04/22/2009 13:21:29 ******/
Enable Trigger [trigMetaDoc_TraceDbChanges] ON Database
GO