Four Stars

Need to run MSSQL script through Talend

Hi,


I need to fetch the list of indexes for MS SQL Table and then compare the indexes with some values. If a tables has 5 indexes then I need to rebuild all non-cluster Indexes except 1 index.
1) I need to do this through TALEND. How can I achieve this?

2) Also I have written a SQL script (.sql) for above index rebuild, now I need to execute this script through talend. How do i achieve?

 

Thanks in advance !


Regards,
Mohit
  • Data Integration
  • Exchange components
1 REPLY
Ten Stars

Re: Need to run MSSQL script through Talend

Talend is not a tools for DBA, SQL Server Agent could do this better, but You can use 

 

tMSSQLInput component for list of indexes, like

SELECT 
     TableName = t.name,
     IndexName = ind.name,
     IndexId = ind.index_id,
     ColumnId = ic.index_column_id,
     ColumnName = col.name,
     ind.*,
     ic.*,
     col.* 
FROM 
     sys.indexes ind 
INNER JOIN 
     sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 
INNER JOIN 
     sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 
INNER JOIN 
     sys.tables t ON ind.object_id = t.object_id 
WHERE 
	 t.name = 'YOU_TABLE_NAME' 
	 AND ind.is_primary_key = 0 
     AND ind.is_unique = 0 
     AND ind.is_unique_constraint = 0 
     AND t.is_ms_shipped = 0 
ORDER BY 
     t.name, ind.name, ind.index_id, ic.index_column_id;

source of code

 

You can also use tMSSQLRow component for any other commands like create index, update and etc

-----------