-
Notifications
You must be signed in to change notification settings - Fork 44
/
generate error tables.sql
88 lines (69 loc) · 2.81 KB
/
generate error tables.sql
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
SET NOCOUNT ON
DECLARE BuildErrorTables CURSOR FAST_FORWARD
FOR
select
o.object_id
from sys.objects o
inner join sys.schemas s
on o.schema_id = s.schema_id
where s.name = 'WH'
and type_desc = 'USER_TABLE'
and o.name <> 'DimDate'
OPEN BuildErrorTables
declare @object_id int
FETCH NEXT FROM BuildErrorTables
INTO @object_id
DECLARE @BuildErrorTable TABLE
(tsql varchar(500) null)
WHILE @@FETCH_STATUS = 0
BEGIN
insert into @BuildErrorTable (tsql)
select 'IF EXISTS (SELECT * FROM sys.objects o inner join sys.schemas s on o.schema_id = s.schema_id where o.name = '''+ object_name(@object_id) + ''' and s.name = ''ERROR'')'
insert into @BuildErrorTable (tsql)
select 'DROP table ERROR.[' + convert(sysname, object_name(@object_id)) + ']'
insert into @BuildErrorTable (tsql)
select 'GO'
insert into @BuildErrorTable (tsql)
select 'create table ERROR.[' + convert(sysname, object_name(@object_id)) + '] ('
insert into @BuildErrorTable (tsql)
select ' ID bigint not null IDENTITY(1,1) PRIMARY KEY '
insert into @BuildErrorTable (tsql)
select
column_name = ', ' + c.name + ' ' + case when t.name in ('binary', 'sysname', 'smallint', 'int', 'bigint', 'decimal', 'float', 'real', 'date', 'time', 'datetime', 'datetime2', 'datetimeoffset'
, 'timestamp', 'numeric', 'money', 'smallmoney')
THEN 'varchar (100)'
when t.name in ('bit', 'tinyint') THEN 'varchar (10)'
when t.name in ('char','varchar') and c.max_length <= 4000 THEN 'varchar (' + cast(c.max_length * 2 as varchar(5)) + ')'
when t.name in ('char','varchar') and c.max_length > 4000 THEN 'varchar (8000)'
when t.name in ('nchar','nvarchar') and c.max_length <= 2000 THEN 'nvarchar (' + cast(c.max_length * 2 as varchar(5)) + ')'
when t.name in ('nchar','nvarchar') and c.max_length > 2000 THEN 'nvarchar (4000)'
when t.name in ('text') THEN 'varchar (8000)'
when t.name in ('ntext') THEN 'nvarchar (4000)'
else 'varchar(8000)'
END + ' NULL'
from
sys.objects o
inner join sys.schemas s
on o.schema_id = s.schema_id
inner join sys.columns c
on c.object_id = o.object_id
inner join sys.types t
on c.user_type_id = t.user_type_id
where
o.object_id = @object_id
insert into @BuildErrorTable (tsql)
select ', ErrorDate datetime2(0) not null constraint DF_' + replace(convert(sysname, object_name(@object_id)), ' ','_') + '_ErrorDate DEFAULT (getdate())'
insert into @BuildErrorTable (tsql)
select ', ErrorText varchar(100) null'
insert into @BuildErrorTable (tsql)
select ', ErrorCode varchar(100) null'
insert into @BuildErrorTable (tsql)
select ', AuditID int null'
insert into @BuildErrorTable (tsql)
select ');'
insert into @BuildErrorTable (tsql)
select 'go'
FETCH NEXT FROM BuildErrorTables
INTO @object_id
END
select * from @BuildErrorTable