RedGate很棒，当数据库发生变化时，我们会生成新的快照(一个小的二进制文件)，并将该文件作为资源保存在项目中。每当我们需要更新数据库时，我们使用RedGate的工具包来更新数据库，并且能够从空数据库创建新的数据库。

RedGate也制作数据快照，虽然我个人没有使用过，但它们同样强大。

是的,总是这样。您应该能够在需要时使用一组有用的示例数据重新创建生产数据库结构。如果你不这样做，随着时间的推移，保持运行的小改变会被遗忘，然后有一天你会被咬，严重的。这是一种保险，你可能认为你不需要，但当你这样做的那一天，它的价格是它的10倍!

我已经在http://dbdeploy.com/上使用了ThoughtWorks的dbdeploy工具。它鼓励使用迁移脚本。在每个版本中，我们将更改脚本合并到一个文件中，以简化理解，并允许dba“支持”更改。

我们维护由ER工具(PowerAMC)生成的DDL(有时是DML)脚本。

我们有一个shell脚本工作台，它重命名以主干分支上的数字开头的脚本。 提交每个脚本并标记bugzilla编号。

这些脚本在需要时与应用程序代码合并到发布分支中。

我们有一个记录脚本及其状态的表。 在部署工具的每次安装中，每个脚本都按顺序执行，并记录在此表中。

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>&#x0D;
</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](&#x0D;
    [ProducId] [int] NULL,&#x0D;
    [ProductName] [nchar](10) NULL,&#x0D;
    [ProductDescription] [varchar](5000) NULL&#x0D;
) ON [PRIMARY]&#x0D;
/*&#x0D;
<Version> 2.2.2 </Version>&#x0D;
&#x0D;
*/&#x0D;
</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] &#x0D;
</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](&#x0D;
    [ProducId] [int] NULL,&#x0D;
    [ProductName] [nchar](10) NULL,&#x0D;
    [ProductDescription] [varchar](5000) NULL&#x0D;
) ON [PRIMARY]&#x0D;
/*&#x0D;
<Version> 2.2.2 </Version>&#x0D;
&#x0D;
*/&#x0D;
</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 =@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)')
declare @EventType varchar(256)
set @EventType =@data.value('(/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= '&lt;'
declare @closetag varchar(4) 
set @closetag= '&gt;'
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

是的，我们通过保留SQL作为构建的一部分来做到这一点——我们保留DROP。sql,创造。sql,用户。sql,值。SQL和版本控制，所以我们可以恢复到任何带标签的版本。

我们还有ant任务，可以在需要时重新创建db。

此外，SQL还被标记为与之配套的源代码。