1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104
|
CREATE procedure [dbo].[ps_script_db_tables]
(
@output nvarchar(255), --filepath ** NOT the filename **
@table sysname = N'ALL',--default to all tables
@options int = 4, --SQLDMO script options
@print int = 0, --print results ** 1 = print file content **
@file_name nvarchar(50) --output file name}
)
as
set nocount on
declare @dbname sysname ; set @dbname = db_name()
declare @server sysname ; set @server = @@servername
declare @hr int
declare @sql int
declare @ot int
declare @databases int
declare @db int
declare @cmd varchar(500)
declare @file nvarchar(400)
/* Trailing backslash */
If RIGHT(@output,1)<>'\' set @output = @output + '\'
If @table = N'ALL'
BEGIN
if @file_name IS NULL set @file_name = @dbname + N'_tables'
set @file = @output + @file_name + N'.sql'
END
else
BEGIN
if @file_name IS NULL set @file_name = @table
set @file = @output + @file_name + N'.sql'
END
/* Create objects */
EXEC @hr = sp_OACreate 'SQLDMO.Transfer',@ot OUTPUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @ot
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer',@sql OUTPUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @sql
/* Connect to server */
EXEC @hr = sp_OASetProperty @sql,'Name',@server
IF @hr <> 0 EXEC sp_OAGetErrorInfo @sql
EXEC @hr = sp_OASetProperty @sql,'LoginSecure','True'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @sql
EXEC @hr = sp_OAMethod @sql,'Connect',NULL
IF @hr <> 0 EXEC sp_OAGetErrorInfo @sql
/* Set database */
EXEC @hr = sp_OAGetProperty @sql,'Databases',@databases OUTPUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @sql
EXEC @hr = sp_OAMethod @databases, 'Item', @db OUTPUT, @dbname
IF @hr <> 0 EXEC sp_OAGetErrorInfo @databases
/* Script properties */
If @table = N'ALL'
begin
EXEC @hr = sp_OASetProperty @ot,'CopyAllTables','True'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @ot
end
else
begin
EXEC @hr = sp_OAMethod @ot,'AddObjectByName',NULL,@table,8
IF @hr <> 0 EXEC sp_OAGetErrorInfo @db
end
EXEC @hr = sp_OASetProperty @ot,'ScriptType',@options
IF @hr <> 0 EXEC sp_OAGetErrorInfo @ot
/* Script to file */
EXEC @hr = sp_OAMethod @db,'ScriptTransfer',NULL,@ot,2,@file
IF @hr <> 0 EXEC sp_OAGetErrorInfo @db
/* Clean up objects */
EXEC @hr = sp_OAMethod @sql,'DisConnect',NULL
IF @hr <> 0 EXEC sp_OAGetErrorInfo @sql
EXEC @hr = sp_OADestroy @ot
IF @hr <> 0 EXEC sp_OAGetErrorInfo @ot
EXEC @hr = sp_OADestroy @sql
IF @hr <> 0 EXEC sp_OAGetErrorInfo @sql
If @print = 1
begin
set @cmd = 'TYPE ' + @file
create table #text([id] int identity(1,1),txt varchar(255) NULL)
insert #text
exec master..xp_cmdshell @cmd
delete #text where txt is null
select txt from #text order by [id]
drop table #text
end
Return(0) |
Partager