Here I have worked on Powershell script to create output in file from t-sql query. For this I have used Invoke-Sqlcmd command. In starting this command was not worked. To enable this command we need to run following:
add-pssnapin sqlserverprovidersnapin100
add-pssnapin sqlservercmdletsnapin100
Following are the working example, you just need to copy paste into your .ps1 file and change query part as per your requirement.
-Query "select case when GROUPING(schemaName)=0 then schemaName else 'Total (MB)' end as [Schema Name],COUNT(dataSize) as [Number Table], SUM(indexCount) as [Number Index], cast(SUM(isnull(dataSize,0))/1024 as decimal(10,2)) as [Table Size(MB)], cast(SUM(ISNULL(indexSize,0))/1024 as decimal(10,2)) as [Index Size(MB)]
from table1
where CONVERT(VARCHAR(10),createDate,111) = '2012/05/10' and databaseName='AdventureWorks'group by schemaName with ROLLUP"
-ServerInstance "Neeraj\Neeraj" -Database "master" | Format-Table >> C:\Users\Neeraj~1\Desktop\Work\Project\Powershell\Results\DatabaseObjectCountsandSizes.txt
Suggestions are always welcome.
Thanks
