 |
 |
Archives of the TeradataForum
Message Posted: Thu, 31 Jul 2003 @ 10:50:38 GMT
| Subj: | | Re: User/Database DDL |
| |
| From: | | Chris Coffing |
I know what you mean Sanjaya... If you improve on these scripts please send the enhancements back, I would like to see them...
Try this script... It provides the ability to re-create the users DDL...
select
trim(databasenamei),1
,'Create User ' || trim(DatabaseName) || ' From ' || trim(OwnerName) ||
' As Perm=' || trim(cast(cast(PermSpace as format 'zzzzzzzzzzz9') as char(12))) ||
', Password=PWD, Spool=' || trim(cast(cast(SpoolSpace as format
'zzzzzzzzzzz9') as char(12))) ||
', Temporary=' || trim(cast(cast(TempSpace as format 'zzzzzzzzzzz9') as char(12))) ||
(CASE
When DefaultDataBase is not null
Then ', Default Database=' || trim(DefaultDataBase)
ELSE ' '
END) ||
', Account='''|| trim(AccountName) || '''' ||
(CASE
When DefaultCollation EQ 'H'
Then ', Collation=Host '
Else ', Collation=' || DefaultCollation
END) ||
(CASE
When DefaultCharType EQ 1
Then ', Default Character Set Latin'
Else ' '
END) ||
(CASE
When DefaultDateForm Is not Null
Then ', Dateform=' || trim(DefaultDateForm)
Else ' '
END) ||
(CASE
When TimeZoneHour is not Null
Then ', Time Zone=' || TimeZoneHour
Else ', Time Zone=LOCAL'
END) ||
';' (Title '')
,current_timestamp
>From dbc.dbase
where rowtype = 'u'
***********************************
This script creates the database
***********************************
select
trim(databasenamei),1
,'Create Database ' || trim(DatabaseName) || ' From ' || trim(OwnerName)
||
' As Perm=' || trim(cast(cast(PermSpace as format 'zzzzzzzzzzz9') as char(12))) ||
', Password=PWD, Spool=' || trim(cast(cast(SpoolSpace as format
'zzzzzzzzzzz9') as char(12))) ||
', Temporary=' || trim(cast(cast(TempSpace as format 'zzzzzzzzzzz9') as char(12))) ||
(CASE
When DefaultDataBase is not null
Then ', Default Database=' || trim(DefaultDataBase)
ELSE ' '
END) ||
', Account='''|| trim(AccountName) || '''' ||
(CASE
When DefaultCollation EQ 'H'
Then ', Collation=Host '
Else ', Collation=' || DefaultCollation
END) ||
(CASE
When DefaultCharType EQ 1
Then ', Default Character Set Latin'
Else ' '
END) ||
(CASE
When DefaultDateForm Is not Null
Then ', Dateform=' || trim(DefaultDateForm)
Else ' '
END) ||
(CASE
When TimeZoneHour is not Null
Then ', Time Zone=' || TimeZoneHour
Else ', Time Zone=LOCAL'
END) ||
';' (Title '')
,current_timestamp
>From dbc.dbase
where rowtype = 'd'
Chris Coffing , Sr Vice President
Coffing Data Warehousing
www.coffingdw.com
| |
