TeraData scripts 20100615_165230

START ===== C:\Temp\Project\SQL\tera\DbForProject\scripts\all.txt

START ===== C:\Temp\Project\SQL\tera\DbForProject\scripts\all.txt
END ================== C:\Temp\Project\SQL\tera\DbForProject\scripts\all.txt
START ===== C:\Temp\Project\SQL\tera\DbForProject\scripts\Cauch_Data_to_Project.sql

-- use the cauch database
DATABASE DbSource ;

-- use the Project database
DATABASE DbForProject ;


-- select * from sys.tables
select * from dbc.tables
where DataBaseName = 'DbSource';
--and table_name=

-- SELECT * FROM INFORMATION_SCHEMA.COLUMNS
select * from dbc.columns
where tablename = 'yourtable'
and databasename = 'AnotherDb';



DATABASE DbForProject ;


CREATE SET TABLE DD63_STG_Project_TEST1.vwDimCountry ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
Col1 VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
Col2 DECIMAL(5,0) NOT NULL,
Col3 BYTEINT NOT NULL,
Col4 BYTEINT NOT NULL,
EXT_TS TIMESTAMP(6))
PRIMARY INDEX ( Continent );

GRANT SELECT
,INSERT
,UPDATE
,DELETE
ON Personnel TO Peterson, Jones
WITH GRANT OPTION;


error handling in teradata
http://developer.teradata.com/blog/mtmoura/2010/04/lets-talk-about-stored-procedures




SELECT
'DROP TABLE ' !! TRIM ( JAN.DB_Name ) !! '.' !! TRIM ( JAN.TBL_Name ) !! ';'
FROM (
select databasename,tablename from dbc.tables



CREATE TABLE PhoneUS
AS (
SELECT * FROM PhoneList WHERE country = 'US'
) WITH DATA;END ================== C:\Temp\Project\SQL\tera\DbForProject\scripts\Cauch_Data_to_Project.sql
START ===== C:\Temp\Project\SQL\tera\DbForProject\scripts\ConcatenateAll.cmd
echo empty the file

echo. >all.txt

echo for each log file in the current directory do contcatenate it
echo in the all.txt file
for /f %%i in ('dir /s /b /a-d') do echo START ===== %%i>>all.txt&type %%i>>all.txt&echo END ================== %%i>>all.txt

echo open the all.txt file
all.txtEND ================== C:\Temp\Project\SQL\tera\DbForProject\scripts\ConcatenateAll.cmd
START ===== C:\Temp\Project\SQL\tera\DbForProject\scripts\GenerateDropAllTables.sql
-- select * from sys.tables
SELECT 'drop table ' !! TableName !! '; ' FROM dbc.tables
WHERE DataBaseName = 'DbForProject'
--and table_name=
--AND TABLEKIND='T'

;
END ================== C:\Temp\Project\SQL\tera\DbForProject\scripts\GenerateDropAllTables.sql
START ===== C:\Temp\Project\SQL\tera\DbForProject\scripts\GenerateDropAllViews.sql

-- GENERATE DROP ALL VIEWS
SELECT 'Drop View DbForProject.' !! TableName !! '; ' FROM dbc.tables
WHERE DataBaseName = 'DbForProject'
--and table_name=
AND TABLEKIND='V'

END ================== C:\Temp\Project\SQL\tera\DbForProject\scripts\GenerateDropAllViews.sql
START ===== C:\Temp\Project\SQL\tera\DbForProject\scripts\GenerateFilesForAllTablesAndOrViews.sql


-- START GENERATE FILES FOR ALL TaBLES OR VIEWS
SELECT 'echo .>' !! TableName !! '.CreateTable.sql' FROM dbc.tables
WHERE DataBaseName = 'DbSource'
--and table_name=
AND TABLEKIND='v'

ORDER BY TableName

;
-- END
END ================== C:\Temp\Project\SQL\tera\DbForProject\scripts\GenerateFilesForAllTablesAndOrViews.sql
START ===== C:\Temp\Project\SQL\tera\DbForProject\scripts\GenerateGetTablesRowCounts.sql
-- START GENERATE SELECT COUNT FOR ALL TABLES
SELECT 'Select '''||TRIM(tablename)||','',count(*) from '||TRIM(databasename)||'.'||TRIM(tablename)||';'
FROM dbc.tables
WHERE databasename='DbForProject '

;
-- END GENERATE SELECT COUNT FOR ALL TABLES ; END ================== C:\Temp\Project\SQL\tera\DbForProject\scripts\GenerateGetTablesRowCounts.sql
START ===== C:\Temp\Project\SQL\tera\DbForProject\scripts\GenerateGrantSelectToTablesAndViewsToAUser.sql
-- START GENERATE GRANT SELECT TO TABLES AND VIEWS TO A USER
SELECT 'GRANT SELECT ON DbForProject.' !! TableName
!! ' TO userName ; ' FROM dbc.tables
WHERE DataBaseName = 'DbForProject'
--and table_name=
-- AND TABLEKIND='T'
ORDER BY TableName
;
-- END GENERATE GRANT SELECT TO TABLES AND VIEWS TO A USER
END ================== C:\Temp\Project\SQL\tera\DbForProject\scripts\GenerateGrantSelectToTablesAndViewsToAUser.sql
START ===== C:\Temp\Project\SQL\tera\DbForProject\scripts\GetAllColumnsAsListFromATable.sql
-- START GET ALL COLUMNS OF A TABLE
SELECT ColumnName !! ',' FROM dbc.COLUMNS
WHERE tablename = 'T_CONSUMER_2'
AND databasename = 'DbForProject'


;

-- END GET ALL COLUMNS OF A TABLE
END ================== C:\Temp\Project\SQL\tera\DbForProject\scripts\GetAllColumnsAsListFromATable.sql