查看使用源代码控制管理数据库更改的LiquiBase。

任何数据库接口代码都应该进入版本控制(存储过程、函数等)。

对于结构和数据，这是一个判断。我个人对我的数据库保持一个干净的结构模板，但由于它们的大小，不将它们存储在版本控制中。但是将它存储在版本控制中是非常有益的，即使对于只有历史记录也是如此。

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转储到一个颠覆回购。这是完全自动化的，但这是一项非常繁重的任务。

你会想要限制修改的数量，因为它真的会在一段时间后消耗磁盘空间!

My team versions our database schema as C# classes with the rest of our code. We have a homegrown C# program (<500 lines of code) that reflects the classes and creates SQL commands to build, drop and update the database. After creating the database we run sqlmetal to generate a linq mapping, which is then compiled in another project that is used to generate test data. The whole things works really well because data access is checked at compile time. We like it because the schema is stored in a .cs file which is easy to track compare in trac/svn.

Visual Studio中的新数据库项目提供了源代码控制和更改脚本。

他们有一个很好的工具，可以比较数据库，并可以生成一个脚本，将一个数据库的模式转换为另一个数据库，或者更新一个数据库中的数据以匹配另一个数据库。

db模式被“分解”以创建许多许多小的.sql文件，每个DDL命令一个描述db。

+tom

附加信息2008-11-30

作为一名开发人员，我在过去的一年里一直在使用它，我真的很喜欢它。它可以很容易地比较我的开发工作和生产工作，并生成用于发布的脚本。我不知道它是否缺少dba在“企业级”项目中所需要的特性。

因为模式被“分解”到sql文件中，所以源代码控制可以正常工作。

One gotcha is that you need to have a different mindset when you use a db project. The tool has a "db project" in VS, which is just the sql, plus an automatically generated local database which has the schema and some other admin data -- but none of your application data, plus your local dev db that you use for app data dev work. You rarely are aware of the automatically generated db, but you have to know its there so you can leave it alone :). This special db is clearly recognizable because it has a Guid in its name,

VS DB项目在将其他团队成员所做的DB更改集成到您的本地项目/相关DB方面做得很好。但是您需要采取额外的步骤，将项目模式与本地dev db模式进行比较，并应用mods。这是有道理的，但一开始似乎很尴尬。

DB项目是一个非常强大的工具。它们不仅生成脚本，而且可以立即应用它们。请确保不要用它破坏您的生产数据库。；）

我真的很喜欢VS DB项目，我希望在我所有的DB项目中使用这个工具。

+tom