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) |
