export sql agent job result to .csv file

As a perfomance optimization, I did index rebuild on database.

I used ALTER TABLE ALL ON @table REBUILD WITH (FILLFACTOR = @fillfactor).

I would like to schedule this as a agent job.

Now my requirement as:

1. I need to export the index stats to csv file before rebuilding index

2. rebuild the index

3. stats of index export to csv file after rebuilding index.

I palced the rebuild index script in agent job and I did manually executed the index stats query to see the status before and after agent job.

how do I need to automate the process of

1. Index stats before rebuilding
2. rebuilding
3. stats after rebuiling.

I am using this script for stats.

DECLARE @DATABASE VARCHAR(10)= 'AdventureWorksDW2008' SELECT OBJECT_NAME(IPS.OBJECT_ID) AS [TABLENAME], SI.NAME AS [INDEXNAME], IPS.INDEX_TYPE_DESC, IPS.AVG_FRAGMENTATION_IN_PERCENT, IPS.AVG_FRAGMENT_SIZE_IN_PAGES, IPS.AVG_PAGE_SPACE_USED_IN_PERCENT, IPS.RECORD_COUNT, IPS.GHOST_RECORD_COUNT, IPS.FRAGMENT_COUNT, IPS.AVG_FRAGMENT_SIZE_IN_PAGES FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(N''+ @DATABASE +''), NULL, NULL, NULL , 'DETAILED') IPS JOIN SYS.TABLES ST WITH (NOLOCK) ON IPS.OBJECT_ID = ST.OBJECT_ID JOIN SYS.INDEXES SI WITH (NOLOCK) ON IPS.OBJECT_ID = SI.OBJECT_ID AND IPS.INDEX_ID = SI.INDEX_ID WHERE ST.IS_MS_SHIPPED = 0 ORDER BY 1,5

and for rebuilding.

ALTER TABLE ALL ON @table REBUILD WITH (FILLFACTOR = @fillfactor).

thanks prav

Hmm, if you put your script sql into a proc called sp_IndexStats you could then write a second proc that looks something like:

EXEC master..xp_cmdshell 'osql.exe -S YourServerName -U sa -P yourpassword -Q "EXEC     sp_IndexStats" -o "C:\mypath\IndexBefore.csv"'
GO
ALTER TABLE ALL ON @table REBUILD WITH (FILLFACTOR = @fillfactor)
GO
EXEC master..xp_cmdshell 'osql.exe -S YourServerName -U sa -P yourpassword -Q "EXEC sp_IndexStats" -o "C:\mypath\IndexAfter.csv"'