Save SQL Object in Subversion Repository or on Disk
I like to look back at this Time when i was working on that small Company. We had not that much Budget to use a common Sourcecontrol Product like Source Safe or Clear Case to storing our Development Files (Active Server Pages and Sql Object). We also was working acros the hole World with alot of Mobility. My Notebook was always on my side, in the Office, at home and either on tour in the train. Development should be possible everywhere.
We Desided to use Subversion together with Trac. The whole ASP Projectfile was always on Eye, but how we could Track Databasechanges accross all our Clients?
We Desided to use Subversion together with Trac. But we had to Script the Objects from SQL Server in a File.
For this approach we created tree Files:
usp_ScriptObjects.sql
(Can Script one Object)usp_ScriptAllObjects.sql
(get Object Names from Database and execute theusp_ScriptObjects.sql
with all required Parameters)ScriptDatabase.sql
(chosse the Path where the script should be stored)
Maybe there are other users they whould script the Database. But for us it was a confortable way. Just execute this script and Checkin to Subversion.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE usp_ScriptObjects
@ServerName varchar(30),
@DBName varchar(30),
@ObjectName varchar(100),
@ObjectType nvarchar(100),
@TableName nvarchar(100),
@ScriptFile varchar(255)
AS
SET LANGUAGE deutsch
DECLARE @CmdStr varchar(255)
DECLARE @object int
DECLARE @hr int
SET NOCOUNT ON
SET @CmdStr = 'Connect('+@ServerName+')'
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
--Comment out for standard login
EXEC @hr = sp_OASetProperty @object, 'LoginSecure', TRUE
-- Codepage...
EXEC @hr = sp_OAMethod @object, 'SetCodePage(0)'
/* Uncomment for Standard Login
EXEC @hr = sp_OASetProperty @object, 'Login', 'sa'
EXEC @hr = sp_OASetProperty @object, 'password', 'sapassword'
*/
EXEC @hr = sp_OAMethod @object,@CmdStr
SET @CmdStr =
CASE @ObjectType
WHEN 'Database' THEN 'Databases("'
WHEN 'Procedure' THEN 'Databases("' + @DBName + '").StoredProcedures("'
WHEN 'View' THEN 'Databases("' + @DBName + '").Views("'
WHEN 'Table' THEN 'Databases("' + @DBName + '").Tables("'
WHEN 'Index' THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Indexes("'
WHEN 'Trigger' THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Triggers("'
WHEN 'Key' THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Keys("'
WHEN 'Check' THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Checks("'
WHEN 'Job' THEN 'Jobserver.Jobs("'
END
if @ObjectType = 'Table'
BEGIN
SET @CmdStr = @CmdStr + @ObjectName + '").Script(5,"' + @ScriptFile + '")'
END
ELSE
BEGIN
-- Write Files in Unicode
SET @CmdStr = @CmdStr + @ObjectName + '").Script(5,"' + @ScriptFile + '", 4)'
END
print @CmdStr
EXEC @hr = sp_OAMethod @object, @CmdStr
EXEC @hr = sp_OADestroy @object
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE usp_ScriptAllObjects
@ServerName varchar(30),
@DBName varchar(30),
@ObjectType varchar(10) = 'Procedure',
@Path varchar(255) = 'c:\Temp\'
AS
SET LANGUAGE deutsch
DECLARE @Name as varchar(60)
DECLARE @ObjectName as varchar(100)
DECLARE @Filename as varchar(255)
DECLARE @FilterParameter as char(2)
--Means this is Procedure, View and Table
DECLARE @isSimpleGenerate as bit
-- Means this is Trigger or Userdefined Function or Index
DECLARE @isComplexGenerate as bit
DECLARE @stmt nvarchar(4000)
SET @FilterParameter =
CASE @ObjectType
--WHEN 'Database' THEN
--usp_ScriptObjects
WHEN 'Procedure' THEN
'P'
WHEN 'View' THEN
'V'
WHEN 'Table' THEN
'U'
WHEN 'Index' THEN
'I'
WHEN 'Trigger' THEN
'TR'
-- WHEN 'Check' THEN
-- WHEN 'Job' THEN
END
DECLARE @isIndex as bit
if @FilterParameter = 'U' or @FilterParameter = 'P' or @FilterParameter = 'V'
BEGIN
select @isSimpleGenerate = 1
END
if @FilterParameter = 'I' or @FilterParameter = 'TR' or @FilterParameter = 'UF'
BEGIN
select @isComplexGenerate = 1
END
if @isSimpleGenerate = 1
BEGIN
DECLARE c_Objects cursor for
select name from dbo.sysobjects where type = @FilterParameter and not left(name, 3)='dt_'
open c_Objects
FETCH NEXT FROM c_Objects into @Name
while @@fetch_status = 0
begin
FETCH NEXT FROM c_Objects into @Name
select @Filename = @Path + @Name + '.sql'
exec usp_ScriptObjects
@ServerName = @ServerName,
@DBName = @DBName,
@ObjectName = @Name,
@ObjectType = @ObjectType,
@ScriptFile = @Filename
end
close c_Objects
DEALLOCATE c_Objects
END
if @isComplexGenerate = 1
BEGIN
create table #objsearch(
ObjectName sysname COLLATE database_default not null,
ObjectType sysname COLLATE database_default not null,
TableName sysname COLLATE database_default not null)
if @FilterParameter = 'I'
begin
select @stmt = 'insert into #objsearch select i.name as ObjectName, ObjectType = ''I'', o.name as TableName from dbo.sysindexes i, dbo.sysobjects o where i.id = o.id and (i.indid not in (0, 255)) and (i.status&(32 + 64 + 2048 + 4096) = 0) and xtype = ''u'''
end
if @FilterParameter = 'TR'
BEGIN
select @stmt = 'insert into #objsearch select o.name as ObjectName, o.xtype as ObjectType, object_name(o.parent_obj) as TableName from dbo.sysobjects o where o.xtype in (''TR'')'
END
exec (@stmt)
if @@error <> 0
begin
raiserror ('Error Insert Indexes in Table, Procedure aborted', 16, 1, @dbname)
return 1
end
DECLARE c_Objects cursor for
select ObjectName, TableName from #objsearch
open c_Objects
FETCH NEXT FROM c_Objects into @ObjectName, @Name
while @@fetch_status = 0
begin
FETCH NEXT FROM c_Objects into @ObjectName, @Name
select @Filename = @Path + @ObjectName + '.sql'
exec usp_ScriptObjects
@ServerName = @ServerName,
@DBName = @DBName,
@ObjectName = @ObjectName,
@ObjectType = @ObjectType,
@ScriptFile = @Filename
end
close c_Objects
DEALLOCATE c_Objects
drop table #objsearch
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/***************************************************
*
* Script your Database Objects
*
**************************************************/
DECLARE @ServerName as varchar(30)
DECLARE @DBName as varchar(30)
DECLARE @PathProcedure as varchar(255)
DECLARE @PathTable as varchar(255)
DECLARE @PathView as varchar(255)
DECLARE @PathIndex as varchar(255)
DECLARE @PathTrigger as varchar(255)
Select @ServerName = 'localhost'
Select @DBName = DB_Name() --'Northwind'
Select @PathProcedure = 'c:\Temp\_DB\P\'
EXECUTE usp_ScriptAllObjects @ServerName,@DBName,'Procedure',@PathProcedure
Select @PathTable = 'c:\Temp\_DB\U\'
EXECUTE usp_ScriptAllObjects @ServerName,@DBName,'Table',@PathTable
Select @PathView = 'c:\Temp\_DB\V\'
EXECUTE usp_ScriptAllObjects @ServerName,@DBName,'View',@PathView
Select @PathIndex = 'c:\Temp\_DB\I\'
EXECUTE usp_ScriptAllObjects @ServerName,@DBName,'Index',@PathIndex
Select @PathTrigger = 'c:\Temp\_DB\TR\'
EXECUTE usp_ScriptAllObjects @ServerName,@DBName,'Trigger',@PathTrigger
GO
Hinterlassen Sie einen Kommentar