我觉得我的商店有一个漏洞,因为我们没有一个可靠的过程来控制数据库模式更改的版本。我们做了很多备份,所以我们或多或少地得到了保护,但以这种方式依赖于最后一道防线是一种糟糕的做法。
令人惊讶的是,这似乎是一个共同的主线。与我交谈过的许多商店都忽略了这个问题,因为他们的数据库不会经常更改,他们基本上只是尽量做到一丝不苟。
不过,我知道这个故事是怎么发展的。这只是时间问题,迟早会出问题,会有东西丢失。
在这方面有什么最佳实践吗?你有哪些行之有效的策略?
我觉得我的商店有一个漏洞,因为我们没有一个可靠的过程来控制数据库模式更改的版本。我们做了很多备份,所以我们或多或少地得到了保护,但以这种方式依赖于最后一道防线是一种糟糕的做法。
令人惊讶的是,这似乎是一个共同的主线。与我交谈过的许多商店都忽略了这个问题,因为他们的数据库不会经常更改,他们基本上只是尽量做到一丝不苟。
不过,我知道这个故事是怎么发展的。这只是时间问题,迟早会出问题,会有东西丢失。
在这方面有什么最佳实践吗?你有哪些行之有效的策略?
当前回答
Sadly, I've seen more than one team developing PL/SQL programs (stored procedures in Oracle) - sometimes ten thousands LOC - just by editing the code in TOAD (a database tool), without even saving the source to files (except for deployment). Even if the database is backuped regulary (wouldn't take that for granted, though), the only way to retrieve an old version of a stored procedure is to restore the whole database, which is many GB large. And of course sometimes concurrent changes in one file lead to loss of work, when more than one developer works on the same project.
其他回答
我已经在http://dbdeploy.com/上使用了ThoughtWorks的dbdeploy工具。它鼓励使用迁移脚本。在每个版本中,我们将更改脚本合并到一个文件中,以简化理解,并允许dba“支持”更改。
我使用SQL CREATE脚本从MySQL workbench导出,然后使用他们的“导出SQL ALTER”功能,我最终得到了一系列创建脚本(当然编号)和ALTER脚本,可以应用它们之间的更改。
3.- Export SQL ALTER script Normally you would have to write the ALTER TABLE statements by hand now, reflecting your changes you made to the model. But you can be smart and let Workbench do the hard work for you. Simply select File -> Export -> Forward Engineer SQL ALTER Script… from the main menu. This will prompt you to specify the SQL CREATE file the current model should be compared to. Select the SQL CREATE script from step 1. The tool will then generate the ALTER TABLE script for you and you can execute this script against your database to bring it up to date. You can do this using the MySQL Query Browser or the mysql client.Voila! Your model and database have now been synchronized!
来源:MySQL Workbench Community Edition: Schema同步指南
当然,所有这些脚本都在版本控制下。
我通过保存创建/更新脚本和生成sampledata的脚本来完成。
我对创建脚本进行了版本控制,并在其中使用了svn版本标记。然后,每当我得到一个将要使用的版本时,我就在dbpatches/目录中创建一个脚本,命名为要上卷到的版本。该脚本的任务是在不破坏数据的情况下修改当前数据库。例如,Dbpatches /可能有名为201、220和240的文件。如果数据库当前处于201级别,则应用补丁220,然后再应用补丁240。
DROP TABLE IF EXISTS `meta`;
CREATE TABLE `meta` (
`property` varchar(255),
`value` varchar(255),
PRIMARY KEY (`property`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `meta` VALUES ('version', '$Rev: 240 $');
在考虑一个好的补丁之前,不要忘记测试你的代码。购者自慎!
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 =@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= '<'
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