Need to run MSSQL script through Talend

mks02 — Tue, 20 Jun 2017 08:00:19 GMT

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 !

Re: Need to run MSSQL script through Talend

vapukov — Tue, 20 Jun 2017 10:08:26 GMT

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

topic Need to run MSSQL script through Talend in Talend Studio

Need to run MSSQL script through Talend

Re: Need to run MSSQL script through Talend