|
|
Archives of the TeradataForum
Message Posted: Wed, 27 Nov 2002 @ 13:10:10 GMT
Subj: | | Re: Show table question |
|
From: | | Christian Schiefer |
Hi,
here is a sample sql-script, which creates a Sql to collect statistics .
.export report file = outfile.txt
/************************** All Indexes *****************************/
sel
cast ( ( Case
when col_num = 1 then 'Collect statistics on '|| trim (both from databasename)||'.'||trim(both from tablename)
else ' '
End) as Char(83) ) (Title ''),
cast( (Case
when col_num = 1 then ' index ('
else ' ,'
End) as Char(8) )
|| trim(both from columnname) ||
cast( (Case
when col_num = max_num then ');'
else ' '
End) as Char(2) ) (Title '')
from
(
sel p1.databasename, p1.tablename, p1.columnname, p1.indexnumber, p1.col_num, p2.max_num
from
(sel databasename, tablename, indexnumber, columnname, csum(1,1) col_num
from dbc.indices group by 1, 2, 3) p1
join
(sel databasename, tablename, indexnumber, count(*) max_num from
dbc.indices group by 1,2,3) p2
on p1.databasename = p2.databasename
and p1.tablename = p2.tablename
and p1.indexnumber = p2.indexnumber
) tab1
where databasename = ''
/********************** CHANGE DATABASENAME **********************/
order by tab1.databasename, tab1.tablename, indexnumber, tab1.col_num;
/************************** Columns *****************************/
Select 'Collect statistics on '|| trim(both from tab1.databasename)
||'.'||trim(both from tab1.tablename)||' column '||trim(both from
tab1.columnname)||';' (Title '')
from
(
sel p1.databasename, p1.tablename, p1.columnname, p1.indexnumber,
p1.col_num, p2.max_num
from
(sel databasename, tablename, indexnumber, columnname, csum(1,1)
col_num
from dbc.indices group by 1, 2, 3) p1
join
(sel databasename, tablename, indexnumber, count(*) max_num from
dbc.indices group by 1,2,3) p2
on p1.databasename = p2.databasename
and p1.tablename = p2.tablename
and p1.indexnumber = p2.indexnumber
) tab1
where (databasename, tablename, columnname)
not in
(sel databasename, tablename, columnname
from
(sel p1.databasename, p1.tablename, p1.columnname, p1.indexnumber,
p1.col_num, p2.max_num
from
(sel databasename, tablename, indexnumber, columnname, csum(1,1)
col_num
from dbc.indices group by 1, 2, 3) p1
join
(sel databasename, tablename, indexnumber, count(*) max_num from
dbc.indices group by 1,2,3) p2
on p1.databasename = p2.databasename
and p1.tablename = p2.tablename
and p1.indexnumber = p2.indexnumber
where p1.col_num = 1
and p2.max_num = 1
) temp_inner
group by 1,2,3
)
and databasename = ''
/********************** CHANGE DATABASENAME **********************/
group by 1 order by 1;
.export reset
.run file = outfile.txt
.quit
| |