SQL Server: create table indexes from meta data

How to create a meta data driven table index routine using T-SQL.

1)  Metadata container


CREATE TABLE [dbo].[ETL_INDEXES](
[TABLE_NAME] [varchar](100) NOT NULL,
[INDEX_TYPE] [varchar](2) NOT NULL,
[INDEX_NAME] [varchar](100) NOT NULL,
[DROP_FL] [bit] NOT NULL,
[CREATE_FL] [bit] NOT NULL,
[REBUILD_FL] [bit] NOT NULL,
[COLUMNS] [varchar](max) NOT NULL
) ON [PRIMARY]

2)  Create T SQL routine to loop through meta data table based on passed table variable


CREATE PROCEDURE [dbo].[sp_loop_create_table_indexes]
@table_name varchar(100)
AS

/*
@table_name: target table

*/
-->This stored procedure will read meta data stored in etl_indexes
--and perform creates where flagged

--*declare working variables
DECLARE @cursor CURSOR
DECLARE @vTABLE_NAME varchar(100)
DECLARE @vINDEX_TYPE varchar(2)
DECLARE @vINDEX_NAME varchar(100)
DECLARE @vDROP_FL bit
DECLARE @vCREATE_FL bit
DECLARE @vREBUILD_FL bit
DECLARE @vCOLUMNS varchar(max)

SET @cursor  = CURSOR FOR
Select distinct TABLE_NAME
, INDEX_TYPE
, INDEX_NAME
, DROP_FL
, CREATE_FL
, REBUILD_FL
, COLUMNS
from operations.dbo.ETL_INDEXES where TABLE_NAME=@table_name
OPEN @cursor

FETCH NEXT FROM @cursor INTO  @vTABLE_NAME ,@vINDEX_TYPE ,@vINDEX_NAME
 ,@vDROP_FL ,@vCREATE_FL ,@vREBUILD_FL  ,@vCOLUMNS

WHILE @@FETCH_STATUS <> -1
BEGIN

exec dbo.sp_create_table_indexes @vTABLE_NAME ,@vINDEX_TYPE ,@vINDEX_NAME
 ,@vDROP_FL ,@vCREATE_FL ,@vREBUILD_FL  ,@vCOLUMNS

FETCH NEXT FROM @cursor INTO @vTABLE_NAME ,@vINDEX_TYPE ,@vINDEX_NAME
 ,@vDROP_FL ,@vCREATE_FL ,@vREBUILD_FL  ,@vCOLUMNS
END
CLOSE @cursor  
DEALLOCATE @cursor
GO

3) Create T SQL routine to create indexes (sub routine to previous step)


CREATE PROCEDURE [dbo].[sp_create_table_indexes]
@table_name varchar(100)
,@index_type varchar(2)
,@index_name varchar(100)
,@drop_fl bit
,@create_fl bit
,@rebuild_fl bit
,@columns varchar(max)

AS

/*
@table_name: target table name
@index_type: IX (nonclustered), CX (clustered), UX (unique nonclustered),
PK (primary key constraint, unique clustered)
**NOTE: PK's can only be applied to non-null columns, there can be only
one clustered index, by default PK's are clustered with this script
@index_name: Name of table index
@drop_fl: triggers index drop
@create_fl: triggers index creation
@rebuild_fl: triggers index rebuild
@columns: list of columns to index
*/

-->This stored procedure executes a create index based on passed variables

--*declare working variables
declare @PK varchar(4000)
declare @IX varchar(4000)
declare @CX varchar(4000)
declare @UX varchar(4000)

IF @index_type='PK' BEGIN
set @PK='
ALTER TABLE [dbo].['+@table_name+'] ADD  CONSTRAINT ['+@index_name+'] PRIMARY KEY CLUSTERED
('+@columns+') WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
'
exec (@PK)
END

IF @index_type ='IX' BEGIN
SET @IX='
CREATE NONCLUSTERED INDEX ['+@index_name+'] ON [dbo].['+@table_name+']
('+@columns+')WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
'
exec (@IX)
END

IF @index_type ='UX' BEGIN
SET @UX='
CREATE UNIQUE NONCLUSTERED INDEX ['+@index_name+'] ON [dbo].['+@table_name+']
('+@columns+')WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
'
exec (@UX)
END

IF @index_type ='CX' BEGIN
SET @CX='
CREATE CLUSTERED INDEX ['+@index_name+'] ON [dbo].['+@table_name+']
('+@columns+')WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
'
exec (@CX)
END

4)  Create T SQL drop index procedure (this is sub routine to steps above)


CREATE PROCEDURE [dbo].[sp_drop_table_indexes]
@table_name varchar(100)
,@index_type varchar(2)
,@index_name varchar(100)
,@drop_fl bit
,@create_fl bit
,@rebuild_fl bit
,@columns varchar(max)

AS

/*
@table_name: target table name
@index_type: IX (nonclustered), CX (clustered), UX (unique nonclustered),
PK (primary key constraint, unique clustered)
@index_name: Name of table index
@drop_fl: triggers index drop
@create_fl: triggers index creation
@rebuild_fl: triggers index rebuild
@columns: list of columns to index
*/

-->This stored procedure executes a drop index based on passed variables

--*declare working variables
declare @PK varchar(4000)
declare @I varchar(4000)

IF @index_type='PK' BEGIN
set @PK='
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''[dbo].['+@table_name+']'') AND name = N'''+@index_name+''')
ALTER TABLE [dbo].['+@table_name+'] DROP CONSTRAINT ['+@index_name+']
'
exec (@PK)
END

IF @index_type IN ('IX','UX','CX') BEGIN
SET @I='
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''[dbo].['+@table_name+']'') AND name = N'''+@index_name+''')
DROP INDEX ['+@index_name+'] ON [dbo].['+@table_name+'] WITH ( ONLINE = OFF )'
exec (@I)
END

Informatica: dynamic filelist generation from metadata

Purpose: How to build dynamic filelist for Informatica.
Required:  File and Source metadata

Step1:  Create Informatica mapping.
Step2:  Drop source(s) in mapping that will drive unique filename, source name, filelist name.  One example would be building a filelist of files to load that have not been logged (loaded in ETL system) or run but logged a failure (re-run due to failure).
Step3: I suggest using an aggregator post joiner (if used) to get a list of unique filenames, source names, filelist names.
Step4: Add a sort transform to put data in order by filelist name
Step5: Add function transform to generate output columns.
Needed: full file path (from variable) appended to filename (ex: $$landing_dir||'/'||FILENAME).
Add 3 ports.
vNEW_FILENAME_IND (var): IIF(FILELIST_NAME = vPREV_FILELIST_NAME,0,1)
vPREV_FILELIST_NAME (var): FILELIST_NAME 
NEW_FILENAME_IND (output port): vNEW_FILENAME_IND
Step6: Add transaction control transform
Map the filename+full_path port, New Filename Ind port, Filelist name port
For the TX control condition: IIF(NEW_FILENAME_IND=1,TC_COMMIT_BEFORE,TC_CONTINUE_TRANSACTION)
Step7:  Map to Flat file target
Filelist name port and Filename+path port

Unix shell: preps meta load to a logging repository. collects file meta.

#!/bin/bash/

#ADMIN_fileprep_sh

#preps meta load to a logging repository.  collects file meta.

#Set variables
vPath=/source_directory
nPipe="|"
nVar="##_"

#Set file perms
#chmod -f 666 $vPath/ADMIN_filelist_prep
chmod -f 666 $vPath/ADMIN_filelist_rename

#Rename files and insert into Admin_filelist_prep
while read line
do
mv $vPath/$line $vPath/$nVar`date +"%Y%m%d%H%M%S"`_$line
echo "$nVar""`date +"%Y%m%d%H%M%S"`_$line">>$vPath/ADMIN_filelist_prep
done<$vPath/ADMIN_filelist_rename

#Loop for permissions
while read line
do
chmod -f 666 $vPath/$line
done<$vPath/ADMIN_filelist_prep

#Loop for dos2unix conversions, uncomment if desired
#while read line
#do
#isitdos='cat $vPath/$line|head -1| od -c|grep "\r"|wc -l'
#if [$isitdos > 0]
#then
#dos2unix -k $vPath/$line
#fi
#done<$vPath/ADMIN_filelist_prep

#Create filestats
>$vPath/ADMIN_filestats

#Loop for stats
while read line
do
     nCount=`wc -l<$vPath/$line`
     nBytes=`wc -c<$vPath/$line`
     nDate=`date -r $vPath/$line +%F`
     echo "$line""$nPipe""$nCount""$nPipe""$nBytes""$nPipe""$nDate">>$vPath/ADMIN_filestats
done<$vPath/ADMIN_filelist_prep

#Loop for Bytes
#while read line
#do
#     nBytes=`wc -c<$vPath/$line`
#     echo "$nBytes" "$line">>$vPath/ADMIN_filebytes
#done<$vPath/ADMIN_filelist_prep

#Loop for Dates
#while read line
#do
#     nDate=`date -r $vPath/$line +%F`
#     echo "$line" "$nDate" >>$vPath/ADMIN_filedates
#done<$vPath/ADMIN_filelist_prep

#Adjust permissions for files
#chmod -f 666 $vPath/ADMIN_filestats
#chmod -f 666 $vPath/ADMIN_filebytes
#chmod -f 666 $vPath/ADMIN_filedates

#Move files to prep folder
#while read line
#do
#mv $vPath/$line $vPath/prep/$line
#done<$vPath/ADMIN_filelist_prep

Unix shell: moves file from working dir to target dir. works from working file.

#!/bin/bash/sh

#moves file from working dir to target dir.  works from working file
#ADMIN_filemove_sh

#Declare Variables

vPath=/start_dir

vDest=/end_dir

#Change perms on file

#chmod 666 $vPath/ADMIN_filelist_move

#Move

while read line

do

mv $vPath/$line $vDest/$line

done<$vPath/ADMIN_filelist_move

Unix shell: scans working directory for files to process

#!/bin/bash/
#scans working directory for files to process
#ADMIN_filelist_sh

#Set path variable
vPath=/directory_to_scan

#Remove ADMIN_filelist
rm -f $vPath/ADMIN_filelist
rm -f $vPath/ADMIN_filelist_prep
rm -f $vPath/ADMIN_filelist_rename
rm -f $vPath/ADMIN_filestats
rm -f $vPath/ADMIN_filelist_move


#Rename file in current directory with spaces
find $vPath/. -name "* *"|while read file
do
     #vFile=$vPath/$file
     mv "$file" "`echo "$file"|sed 's/ /##/g'`"
done

#Create ADMIN_filelist
find $vPath/. -maxdepth 1 -type f -printf %f\\n>>$vPath/ADMIN_filelist

#Adjust file permissions
#chmod 666 $vPath/ADMIN_filelist

SQL Server: archive target table

/*purpose:   archive passed target table with date time stamp.  original table is copied as a shell
this is referred to as a table swap*/


ALTER procedure [dbo].[sp_archive_target_table]
@target varchar(100)
--set @target='table_name'
AS
--=====================
declare @CREATE varchar(4000)
declare @RENAME_ORIG varchar(4000)
declare @RENAME_NEW varchar(4000)
declare @append varchar(16)
--========================
set @append=CONVERT(varchar(8),getdate(),112)+REPLACE(CONVERT(VARCHAR(8),GETDATE(),114),':','')
--========================
set @CREATE='
select top 0 *
into '+@target+'_new
from '+@target+''
exec (@CREATE)
--========================
set @RENAME_ORIG='
sp_rename '''+@target+''', '''+@target+''+'_'+''+@append+'''
'
exec (@RENAME_ORIG)

set @RENAME_NEW='
sp_rename '''+@target+'_new'', '''+@target+'''
'
exec (@RENAME_NEW)
--=======================