FAQ / Microsoft SQL Server / Скрипт создания всех объектов БД программно [SQL.Ru]

Опубликовано: 27 сен 02
Рейтинг:

Оценить и прокомменитировать
Распечатать статью

Автор: Dankov
Прислал:

Способ 1
Использовать SQL-DMO
В частности на VBScript это выглядит так

===================== DBScript.vbs ==========================
server_name="SomeServer"
db_name="SomeDB"
file_name="c:\DBScript.sql"

set SQLServer = CreateObject("SQLDMO.SQLServer") 
SQLServer.LoginSecure = True
SQLServer.Name=server_name
SQLServer.Connect 
set Transfer=CreateObject("SQLDMO.Transfer")
Transfer.CopyAllObjects=true
SQLServer.Databases(db_name).ScriptTransfer Transfer, 2, file_name 
==========================================================

Способ 2
Скрипт базы можно сделать просто утилитой командной строки "scptxfr.exe", что находиться в папке Upgrade (в дереве каталогов MSSQL2000). Запуск этой утилиты из командной строки с ключиком "/?" выдаст описание всех возможных опций. Из T-SQL эту утилиту можно вызsвать через хранимую процедуру xp_cmdshell

Способ 3 (от Snake)
Использовать все того же SQL-DMO но уже в хранимой процедуре на TSQL

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO
/* Сохранение скрипта БД
* @Database - имя базы 
* @Path - куда класть скрипты 
* @Objects - 
*	1	 - роли
*	2	 - дефаулты
*	4	 - полнотекст. каталог
*	8	 - правила
*	16	 - хранимые процедуры
*	32	 - типы пользователя
*	64	 - пользовательские функции
*	128  - пользователи
*	256  - представления
*	512  - таблицы
* @ScriptType 
*	266663 -- заскриптовать обьекты типа @Objects 
*			  в файл в формате UNICODE и 
*			  выдать пользователю пр print
* для вью и таблиц + 16 - заскриптовать вью и таблицы + триггеры
* для таблиц + ? - заскриптовать таблицу триггеры
??? смотреть значение @ScriptType в ВOL

*/ 
CREATE   PROCEDURE dbo.usp_ScriptDBObjects
(
@Database SYSNAME ,
@Path VARCHAR(500) ,
@Objects int , 
@ScriptType int = 266663 
)
AS 
DECLARE 
	@SQL int, -- SQL сервер 
	@SP int, -- обьекты
	@i int, @j int, @s varchar(200), @k int, @l sysname, 
	@ERROR int, -- код ошибки 
	@Source varchar(8000), 
	@Description varchar(8000) -- описание ошибки 

exec @error = sp_OACreate 'SQLDMO.SQLServer', @SQL out 
if @error<>0 begin 
	exec sp_OAGetErrorInfo @SQL, @Source out, @Description out 
	print ISNULL(@Source,'')+', '+ ISNULL(@Description,'') 
end 
-- использовать Windows authentication 
exec @error = sp_OASetProperty @SQL, 'LoginSecure ', 'TRUE' 
if @error<>0 begin 
	exec sp_OAGetErrorInfo @SQL, @Source out, @Description out 
	print ISNULL(@Source,'')+', '+ ISNULL(@Description,'') 
end 
exec @error = sp_OAMethod @SQL, 'Connect(".")'
if @error<>0 begin 
	exec sp_OAGetErrorInfo @SQL, @Source out, @Description out 
	print ISNULL(@Source,'')+', '+ ISNULL(@Description,'') 
end 

if (@Objects & 1) > 0 begin -- получить в @SP коллекцию ролей  
	set @s='Databases("'+@Database+'").DatabaseRoles' 
	exec @error = sp_OAGetProperty @SQL, @s, @SP out 
	if @error<>0 begin 
		exec sp_OAGetErrorInfo @SQL, @Source OUT, @Description OUT 
		print isnull(@Source,'')+', '+ isnull(@Description,'') 
	end
	set @s='Databases("'+@Database+'").DatabaseRoles.Count' 
	exec @error = sp_OAGetProperty @SQL, @s, @j out 
	if @error<>0 begin 
		exec sp_OAGetErrorInfo @SQL, @Source OUT, @Description OUT 
		print isnull(@Source,'')+', '+ isnull(@Description,'') 
	end
	set @i=1 
	while @i<=@j begin 
		set @s='Item(' +ltrim(str(@i))+ ').Name' 
		exec @error = sp_OAGetProperty @SP, @s, @l out -- имя роли 
		if @error<>0 begin 
			exec sp_OAGetErrorInfo @SP, @Source out, @Description out 
			print ISNULL(@Source,'')+', '+ ISNULL(@Description,'') 
		end 
		if left(@l,3) <> 'db_' begin
			set @s='Item(' +ltrim(str(@i))+ ').Script('+str(@ScriptType)+',"'+@Path+'",6)' 
			exec @error = sp_OAMethod @SP, @s, @Description out 
			print @Description 
			if @error<>0 begin 
				exec sp_OAGetErrorInfo @SP, @Source out, @Description out 
				print ISNULL(@Source,'')+', '+ ISNULL(@Description,'') 
			end 
		end 
		set @i=@i+1 
	end 
	-- закрыть коллекцию
	set @SP = null
end

if (@Objects & 2) > 0 begin -- получить в @SP коллекцию дефаултов  
	set @s='Databases("'+@Database+'").Defaults' 
	exec @error = sp_OAGetProperty @SQL, @s, @SP out 
	if @error<>0 begin 
		exec sp_OAGetErrorInfo @SQL, @Source OUT, @Description OUT 
		print isnull(@Source,'')+', '+ isnull(@Description,'') 
	end
	set @s='Databases("'+@Database+'").Defaults.Count' 
	exec @error = sp_OAGetProperty @SQL, @s, @j out 
	if @error<>0 begin 
		exec sp_OAGetErrorInfo @SQL, @Source OUT, @Description OUT 
		print isnull(@Source,'')+', '+ isnull(@Description,'') 
	end
	set @i=1 
	while @i<=@j begin 
		set @s='Item(' +ltrim(str(@i))+ ').Name' 
		exec @error = sp_OAGetProperty @SP, @s, @l out -- имя дефаулта 
		if @error<>0 begin 
			exec sp_OAGetErrorInfo @SP, @Source out, @Description out 
			print ISNULL(@Source,'')+', '+ ISNULL(@Description,'') 
		end 
		set @s='Item(' +ltrim(str(@i))+ ').Script('+str(@ScriptType)+',"'+@Path+'",6)' 
		exec @error = sp_OAMethod @SP, @s, @Description out 
		print @Description 
		if @error<>0 begin 
			exec sp_OAGetErrorInfo @SP, @Source out, @Description out 
			print ISNULL(@Source,'')+', '+ ISNULL(@Description,'') 
		end 
		set @i=@i+1 
	end 
	-- закрыть коллекцию
	set @SP = null
end

if (@Objects & 4) > 0 begin -- получить в @SP коллекцию полнотекст. каталогов  
	set @s='Databases("'+@Database+'").FullTextCatalogs' 
	exec @error = sp_OAGetProperty @SQL, @s, @SP out 

	if @error<>0 begin 
		exec sp_OAGetErrorInfo @SQL, @Source OUT, @Description OUT 
		print isnull(@Source,'')+', '+ isnull(@Description,'') 
	end
	set @s='Databases("'+@Database+'").FullTextCatalogs.Count' 
	exec @error = sp_OAGetProperty @SQL, @s, @j out 
	if @error<>0 begin 
		exec sp_OAGetErrorInfo @SQL, @Source OUT, @Description OUT 
		print isnull(@Source,'')+', '+ isnull(@Description,'') 
	end
	set @i=1 
	while @i<=@j begin 
		set @s='Item(' +ltrim(str(@i))+ ').Name' 
		exec @error = sp_OAGetProperty @SP, @s, @l out -- имя полнотекст. каталога 
		if @error<>0 begin 
			exec sp_OAGetErrorInfo @SP, @Source out, @Description out 
			print ISNULL(@Source,'')+', '+ ISNULL(@Description,'') 
		end 
		set @s='Item(' +ltrim(str(@i))+ ').Script('+str(@ScriptType)+',"'+@Path+'",6)' 
		exec @error = sp_OAMethod @SP, @s, @Description out 
		print @Description 
		if @error<>0 begin 
			exec sp_OAGetErrorInfo @SP, @Source out, @Description out 
			print ISNULL(@Source,'')+', '+ ISNULL(@Description,'') 
		end 
		set @i=@i+1 
	end 
	-- закрыть коллекцию
	set @SP = null
end

if (@Objects & 8) > 0 begin -- получить в @SP коллекцию правил
	set @s='Databases("'+@Database+'").Rules' 
	exec @error = sp_OAGetProperty @SQL, @s, @SP out 
	if @error<>0 begin 
		exec sp_OAGetErrorInfo @SQL, @Source OUT, @Description OUT 
		print isnull(@Source,'')+', '+ isnull(@Description,'') 
	end
	set @s='Databases("'+@Database+'").Rules.Count' 
	exec @error = sp_OAGetProperty @SQL, @s, @j out 
	if @error<>0 begin 
		exec sp_OAGetErrorInfo @SQL, @Source OUT, @Description OUT 
		print isnull(@Source,'')+', '+ isnull(@Description,'') 
	end
	set @i=1 
	while @i<=@j begin 
		set @s='Item(' +ltrim(str(@i))+ ').Name' 
		exec @error = sp_OAGetProperty @SP, @s, @l out -- имя правила 
		if @error<>0 begin 
			exec sp_OAGetErrorInfo @SP, @Source out, @Description out 
			print ISNULL(@Source,'')+', '+ ISNULL(@Description,'') 
		end 
		set @s='Item(' +ltrim(str(@i))+ ').Script('+str(@ScriptType)+',"'+@Path+'",6)' 
		exec @error = sp_OAMethod @SP, @s, @Description out 
		print @Description 
		if @error<>0 begin 
			exec sp_OAGetErrorInfo @SP, @Source out, @Description out 
			print ISNULL(@Source,'')+', '+ ISNULL(@Description,'') 
		end 
		set @i=@i+1 
	end 
	-- закрыть коллекцию
	set @SP = null
end

if (@Objects & 16) > 0 begin -- получить в @SP коллекцию процедур 
	set @s='Databases("'+@Database+'").StoredProcedures' 
	exec @error = sp_OAGetProperty @SQL, @s, @SP out 
	if @error<>0 begin 
		exec sp_OAGetErrorInfo @SQL, @Source OUT, @Description OUT 
		print isnull(@Source,'')+', '+ isnull(@Description,'') 
	end
	set @s='Databases("'+@Database+'").StoredProcedures.Count' 
	exec @error = sp_OAGetProperty @SQL, @s, @j out 
	if @error<>0 begin 
		exec sp_OAGetErrorInfo @SQL, @Source OUT, @Description OUT 
		print isnull(@Source,'')+', '+ isnull(@Description,'') 
	end
	set @i=1 
	while @i<=@j begin 
		set @s='Item(' +LTRIM(STR(@i))+ ').Name' 
		exec @error = sp_OAGetProperty @SP, @s, @l out -- имя процедуры 
		if @error<>0 begin 
			exec sp_OAGetErrorInfo @SP, @Source out, @Description out 
			print ISNULL(@Source,'')+', '+ ISNULL(@Description,'') 
		end 
		if left(@l,3) <> 'dt_' begin
			set @s='Item(' +LTRIM(STR(@i))+ ').Script('+str(@ScriptType)+',"'+@Path+'",6)' 
			exec @error = sp_OAMethod @SP, @s, @Description out 
			print @Description 
			if @error<>0 begin 
				exec sp_OAGetErrorInfo @SP, @Source out, @Description out 
				print ISNULL(@Source,'')+', '+ ISNULL(@Description,'') 
			end 
		end
		set @i=@i+1 
	end 
	-- закрыть коллекцию
	set @SP = null
end

if (@Objects & 32) > 0 begin -- получить в @SP коллекцию типов пользователя    
	set @s='Databases("'+@Database+'").UserDefinedDatatypes' 
	exec @error = sp_OAGetProperty @SQL, @s, @SP out 
	if @error<>0 begin 
		exec sp_OAGetErrorInfo @SQL, @Source OUT, @Description OUT 
		print isnull(@Source,'')+', '+ isnull(@Description,'') 
	end
	set @s='Databases("'+@Database+'").UserDefinedDatatypes.Count' 
	exec @error = sp_OAGetProperty @SQL, @s, @j out 
	if @error<>0 begin 
		exec sp_OAGetErrorInfo @SQL, @Source OUT, @Description OUT 
		print isnull(@Source,'')+', '+ isnull(@Description,'') 
	end
	set @i=1 
	while @i<=@j begin 
		set @s='Item(' +ltrim(str(@i))+ ').Name' 
		exec @error = sp_OAGetProperty @SP, @s, @l out -- имя типа пользователя 
		if @error<>0 begin 
			exec sp_OAGetErrorInfo @SP, @Source out, @Description out 
			print ISNULL(@Source,'')+', '+ ISNULL(@Description,'') 
		end 
		set @s='Item(' +ltrim(str(@i))+ ').Script('+str(@ScriptType)+',"'+@Path+'",6)' 
		exec @error = sp_OAMethod @SP, @s, @Description out 
		print @Description 
		if @error<>0 begin 
			exec sp_OAGetErrorInfo @SP, @Source out, @Description out 
			print ISNULL(@Source,'')+', '+ ISNULL(@Description,'') 
		end 
		set @i=@i+1 
	end 
	-- закрыть коллекцию
	set @SP = null
end

if (@Objects & 64) > 0 begin -- получить в @SP коллекцию пользовательских функции  
	set @s='Databases("'+@Database+'").UserDefinedFunctions' 
	exec @error = sp_OAGetProperty @SQL, @s, @SP out
	if @error<>0 begin 
		exec sp_OAGetErrorInfo @SQL, @Source OUT, @Description OUT 
		print isnull(@Source,'')+', '+ isnull(@Description,'') 
	end
	set @s='Databases("'+@Database+'").UserDefinedFunctions.Count' 
	exec @error = sp_OAGetProperty @SQL, @s, @j out 
	if @error<>0 begin 
		exec sp_OAGetErrorInfo @SQL, @Source OUT, @Description OUT 
		print isnull(@Source,'')+', '+ isnull(@Description,'') 
	end
	set @i=1 
	while @i<=@j begin 
		set @s='Item(' +ltrim(str(@i))+ ').Name' 
		exec @error = sp_OAGetProperty @SP, @s, @l out -- имя пользовательской функции 
		if @error<>0 begin 
			exec sp_OAGetErrorInfo @SP, @Source out, @Description out 
			print ISNULL(@Source,'')+', '+ ISNULL(@Description,'') 
		end 
		set @s='Item(' +ltrim(str(@i))+ ').Script('+str(@ScriptType)+',"'+@Path+'",6)' 
		exec @error = sp_OAMethod @SP, @s, @Description out 
		print @Description 
		if @error<>0 begin 
			exec sp_OAGetErrorInfo @SP, @Source out, @Description out 
			print ISNULL(@Source,'')+', '+ ISNULL(@Description,'') 
		end 
		set @i=@i+1 
	end 
	-- закрыть коллекцию
	set @SP = null
end

if (@Objects & 128) > 0 begin -- получить в @SP коллекцию пользователей
	set @s='Databases("'+@Database+'").Users' 
	exec @error = sp_OAGetProperty @SQL, @s, @SP out 
	if @error<>0 begin 
		exec sp_OAGetErrorInfo @SQL, @Source OUT, @Description OUT 
		print isnull(@Source,'')+', '+ isnull(@Description,'') 
	end
	set @s='Databases("'+@Database+'").Users.Count' 
	exec @error = sp_OAGetProperty @SQL, @s, @j out 
	if @error<>0 begin 
		exec sp_OAGetErrorInfo @SQL, @Source OUT, @Description OUT 
		print isnull(@Source,'')+', '+ isnull(@Description,'') 
	end
	set @i=1 
	while @i<=@j begin 
		set @s='Item(' +ltrim(str(@i))+ ').Name' 
		exec @error = sp_OAGetProperty @SP, @s, @l out -- имя пользователя 
		if @error<>0 begin 
			exec sp_OAGetErrorInfo @SP, @Source out, @Description out 
			print ISNULL(@Source,'')+', '+ ISNULL(@Description,'') 
		end 
		set @s='Item(' +ltrim(str(@i))+ ').Script('+str(@ScriptType)+',"'+@Path+'",6)' 
		exec @error = sp_OAMethod @SP, @s, @Description out 
		print @Description 
		if @error<>0 begin 
			exec sp_OAGetErrorInfo @SP, @Source out, @Description out 
			print ISNULL(@Source,'')+', '+ ISNULL(@Description,'') 
		end 
		set @i=@i+1 
	end 

	-- закрыть коллекцию
	set @SP = null
end

if (@Objects & 256) > 0 begin -- получить в @SP коллекцию представлений  
	set @s='Databases("'+@Database+'").Views' 
	exec @error = sp_OAGetProperty @SQL, @s, @SP out 
	if @error<>0 begin 
		exec sp_OAGetErrorInfo @SQL, @Source OUT, @Description OUT 
		print isnull(@Source,'')+', '+ isnull(@Description,'') 
	end
	set @s='Databases("'+@Database+'").Views.Count' 
	exec @error = sp_OAGetProperty @SQL, @s, @j out 
	if @error<>0 begin 
		exec sp_OAGetErrorInfo @SQL, @Source OUT, @Description OUT 
		print isnull(@Source,'')+', '+ isnull(@Description,'') 
	end
	set @i=1 
	while @i<=@j begin 
		set @s='Item(' +ltrim(str(@i))+ ').Name' 
		exec @error = sp_OAGetProperty @SP, @s, @l out -- имя представления 
		if @error<>0 begin 
			exec sp_OAGetErrorInfo @SP, @Source OUT, @Description OUT 
			print isnull(@Source,'')+', '+ isnull(@Description,'') 
		end
		if left(@l,3) <> 'sys' begin
			set @s='Item(' +ltrim(str(@i))+ ').Script('+str(@ScriptType)+',"'+@Path+'",6)' -- скриптовать вместе с триггерами
			exec @error = sp_OAMethod @SP, @s, @Description out 
			print @Description 
 			if @error<>0 begin 
 				exec sp_OAGetErrorInfo @SP, @Source OUT, @Description OUT 
 				print isnull(@Source,'')+', '+ isnull(@Description,'') 
 			end
		end
		set @i=@i+1 
	end 
	-- закрыть коллекцию
	set @SP = null
end

if (@Objects & 512) > 0 begin -- получить в @SP коллекцию таблиц  
	set @s='Databases("'+@Database+'").Tables' 
	exec @error = sp_OAGetProperty @SQL, @s, @SP out 
	if @error<>0 begin 
		exec sp_OAGetErrorInfo @SQL, @Source OUT, @Description OUT 
		print isnull(@Source,'')+', '+ isnull(@Description,'') 
	end
	set @s='Databases("'+@Database+'").Tables.Count' 
	exec @error = sp_OAGetProperty @SQL, @s, @j out 
	if @error<>0 begin 
		exec sp_OAGetErrorInfo @SQL, @Source OUT, @Description OUT 
		print isnull(@Source,'')+', '+ isnull(@Description,'') 
	end
	set @i=1 
	while @i<=@j begin 
		set @s='Item(' +ltrim(str(@i))+ ').Name' 
		exec @error = sp_OAGetProperty @SP, @s, @l out -- имя таблицы 
		if @error<>0 begin 
			exec sp_OAGetErrorInfo @SP, @Source OUT, @Description OUT 
			print isnull(@Source,'')+', '+ isnull(@Description,'') 
		end
		if left(@l,3) <> 'dt_' and left(@l,3) <> 'sys' begin
			set @s='Item(' +ltrim(str(@i))+ ').Script('+str(@ScriptType)+',"'+@Path+'",,6)' 
			exec @error = sp_OAMethod @SP, @s, @Description out 
			print @Description 
			if @error<>0 begin 
				exec sp_OAGetErrorInfo @SP, @Source OUT, @Description OUT 
				print isnull(@Source,'')+', '+ isnull(@Description,'') 
			end
		end
		set @i=@i+1 
	end 
	-- закрыть коллекцию
	set @SP = null
end

--закрыть соединение с сервером
exec @error = sp_OADestroy @SQL 
if @error<>0 begin 
	exec sp_OAGetErrorInfo @SQL, @Source OUT, @Description OUT 
	print isnull(@Source,'')+', '+ isnull(@Description,'') 
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Комментарии

11 января 2012, 23:38 slavik_msc

как применить 3 способ к базе?
11 января 2012, 23:37 slavik_msc

как применить 3 способ к базе?
01 августа 2010, 18:40 Knyazev Alexey

http://www.t-sql.ru/post/ScriptObjectsPoSh.aspx
25 января 2010, 12:23 AgeNTus

Процедура неработает :(
Выводит:
ODSOLE Extended Procedure, Invalid class string
ODSOLE Extended Procedure, sp_OASetProperty usage: ObjPointer int IN, PropertyName varchar IN, @setval <any> IN [, additional indexing IN params].
ODSOLE Extended Procedure, sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, additional IN, OUT, or BOTH params]]
ODSOLE Extended Procedure, sp_OAGetProperty usage: ObjPointer int IN, PropertyName varchar IN [, @returnval <any> OUT [, additional indexing IN params]].
ODSOLE Extended Procedure, sp_OAGetProperty usage: ObjPointer int IN, PropertyName varchar IN [, @returnval <any> OUT [, additional indexing IN params]].
ODSOLE Extended Procedure, sp_OADestroy usage: ObjPointerToBeDestroyed int IN.
Где я недоглядел?
24 января 2010, 00:51 EvolVER

Oleg_SQL
varchar(MAX)
16 июля 2009, 06:30 Oleg_SQL

а если текст поцедуры/функции более 8К символов?
06 апреля 2008, 09:21 Knyazev Alexey

https://www.sql.ru/forum/actualthread.aspx?tid=489369
20 июня 2007, 16:47 Artvohhh

Супер скрипт
01 июня 2007, 07:51 |Жека|

То что я искал! Спасибо!
02 мая 2007, 13:29 borin

дык а если просто экспортом из локала в локал дунуть?
16 февраля 2007, 15:27 i1

гут :)