Monitor Database Size Location through query and mailer

Hi friends! One of my friend asked me to help him creating a mailer that gives information about all databases of a server. I tried and created this-

----------------------------------------------------------------------

Use master
Go

Create Table #temp
(
    DatabaseName sysname,
    LogicalName sysname,
    physical_name varchar(500),
    size decimal (18,2),
    FreeSpace decimal (18,2),
 [DBState] varchar(500)
)   
Exec sp_msforeachdb '
Use [?];
Insert Into #temp (DatabaseName, LogicalName, physical_name, Size, FreeSpace,[DBState])
    Select DB_NAME() AS [DatabaseName], Name as LogicalName,  physical_name,
    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) as nvarchar) Size,
    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) -
    Cast(FILEPROPERTY(name, ''SpaceUsed'') * 8.0/1024.0 as decimal(18,2)) as nvarchar) As FreeSpace,
 state_desc as [DBState]
    From sys.database_files
'
Select ROW_NUMBER() Over(Order by DatabaseName) RowNo, * From #temp

---------mailer
Declare @MailSubject varchar(1000)='Database Monitor Info for Servers '+Isnull(Cast(@@ServerName as varchar(500)),'')
Declare @MailBodyHeader varchar(3000)='Hi,<br><br>Please find the details of databases-<br><br>
  <TABLE BORDER="0" CELLPADDING="0" CELLSPACING="0" WIDTH="100%" style="border-top:1px solid black; border-left:1px solid black;"><TBODY>
          <TR><TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#2f75b5" ALIGN="CENTER" colspan="7"><FONT FACE="Tahoma" SIZE="3" WIDTH="100PX" Color="white"><B>Database Monitor Info</B></FONT></TD></TR>
          <TR><TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#2f75b5" ALIGN="CENTER" colspan="7"><FONT FACE="Tahoma" SIZE="2" WIDTH="100PX" Color="white"><B>Status </B></FONT></TD></TR>
          <TR><TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>RowNo</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>DatabaseName</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>LogicalName</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>physical_name</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>size</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>FreeSpace</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>DBState</B></FONT></TD>
           </TR>'
Declare @MailBody varchar(max)
Select @MailBody= Isnull(@MailBody,'')+'<TR><TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>'+Isnull(Cast(ROW_NUMBER() Over(Order by DatabaseName) as varchar(10)),'')+'</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="Left"><FONT FACE="Tahoma" SIZE="2"><B>'+Isnull(Cast(DatabaseName as varchar(500)),'')+'</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="Left"><FONT FACE="Tahoma" SIZE="2">'+Isnull(Cast(LogicalName as varchar(500)),'')+'</FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="Left"><FONT FACE="Tahoma" SIZE="2">'+Isnull(Cast(physical_name as varchar(2000)),'')+'</FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="Center"><FONT FACE="Tahoma" SIZE="2">'+Isnull(Cast(size as varchar(50)),'')+'</FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="Center"><FONT FACE="Tahoma" SIZE="2">'+Isnull(Cast(FreeSpace as varchar(50)),'')+'</FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="Left"><FONT FACE="Tahoma" SIZE="2">'+Isnull(Cast(DBState as varchar(50)),'') +'</FONT></TD>
           </TR>'
     From #temp

Set  @MAILBODY= @MailBodyHeader +@MAILBODY+'</Table>'
IF((Select Count(1) from #temp)>0)
Begin
EXEC MSDB.DBO.SP_SEND_DBMAIL 
     @RECIPIENTS ='vimal.lohani@gmail.com',
     --@COPY_RECIPIENTS = 'vimal.lohani@gmail.com',                                   
     @BODY = @MailBody,
     @SUBJECT = @MailSubject,
     @BODY_FORMAT = 'HTML',
     @PROFILE_NAME ='dbamail' --Your profile name
End
---------mailer enddrop table #temp

Monitor Windows Server having SQL Services Agent Services Reporting Services

Hi guys! Yesterday my boss asked me to build a tool to monitor Windows servers and Sql server services. I searched on Google and find few codes, i modified the code changed powershell concept and made below code.

In this post I shall tell you about how you monitor your all servers. The only thing you need is SQL Server which runs with an account having admin privilege on the network, or you can monitor those systems, the account having admin rights.

Lets start-

Create a database and two tables-

----------------------------------------------------------------------

Use master
Go
Create Database DBAMonitor
GO
Use DBAMonitor
GO 

CREATE TABLE [dbo].[tbl_ServerInfo](
 [Id] [int] identity(1,1) Not NULL,
 [WindowsServerName] [varchar](100) Not NULL,
 [SQLinstanceName] [varchar](100)  NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[tbl_ServiceInfo](
 [SQLServiceName] [varchar](500) NULL,
 [servicestatus] [varchar](100) NULL,
 [windowservername] [varchar](500) NULL,
 [startmode] [varchar](100) NULL,
 [UpdateDate] [datetime] NULL DEFAULT GETDATE(),
 [startname] [varchar](1000) NULL,
 [InstanceName] [varchar](1000) NULL,
 [ServerID] INT NULL
) ON [PRIMARY]

----------------------------------------------------------------------

Please add your server details on tbl_serverInfo you want to monitor-

----------------------------------------------------------------------

--For example

insert into [tbl_ServerInfo](windowsServerName,SQLinstanceName) values ('Proddb','Dashboard')
insert into [tbl_ServerInfo](windowsServerName,SQLinstanceName) values ('192.168.0.512','sql2008r2')
insert into [tbl_ServerInfo](windowsServerName,SQLinstanceName) values ('192.168.0.500','')
insert into [tbl_ServerInfo](windowsServerName,SQLinstanceName) values ('192.168.0.510','')

--Checking details

----------------------------------------------------------------------

Now we need to run below script, but make sure xp_cmdshell is enabled. To enable it, please run below code-

----------------------------------------------------------------------

sp_configure 'Show Advanced Options',1
go
reconfigure
go
sp_configure 'xp_cmdshell',1
go   
reconfigure
go

----------------------------------------------------------------------

Now run the script and look the power-

----------------------------------------------------------------------

set NoCount on
--Truncate table DBAMonitor..[tbl_ServiceInfo]
-- Variable to store windows server name
DECLARE @server_name varchar(100)
DECLARE @SQLInstance_name varchar(100)
DECLARE @SrvrID int
-- table to store PowerShell script output
CREATE TABLE #ServiceInfoData
(line varchar(max) null)
-- Declaring cursor to fetch windows server name
DECLARE server_cursor CURSOR FOR 
select distinct LTRIM(rtrim(windowsservername)) as windowsServerName,SQLInstanceName,Id from DBAMonitor..[tbl_ServerInfo]
OPEN server_cursor
FETCH NEXT FROM server_cursor 
INTO @server_name, @SqlInstance_Name,@SrvrID
WHILE @@FETCH_STATUS = 0
BEGIN
declare @svrName varchar(255)
declare @sql varchar(800)
set @svrName = @server_name
-- Preparing PowerShell Dynamic Statement
set @sql = 'powershell.exe -c "get-wmiobject -Class win32_service -Computername ' + QUOTENAME(Isnull(@svrName,''),'''') + ' | where {$_.name -like ''MSSQLServer'' -OR $_.name -like ''MSSQL$' + Isnull(@SqlInstance_Name,'') + ''' -OR $_.name -like ''SQLSERVERAGENT'' -OR $_.name -like ''ReportServer$' + Isnull(@SqlInstance_Name,'') + ''' -OR $_.name -like ''SQLAgent$' + Isnull(@SqlInstance_Name,'') + ''' } | select-object  Name,state,systemname,startmode,startname | %{$_.name+''|''+$_.state+''%''+$_.systemname+''*''+$_.startmode+''@''+$_.startname+''!''} "'
-- Inserting PowerShell Output to temporary table
--print @sql
insert #ServiceInfoData 
EXEC xp_cmdshell @sql
-- Deleting the rows which contains error or has not sufficient data
delete from #ServiceInfoData  where len(line) < 30
update #ServiceInfoData set line = line + '!' where line not like '%!%'
IF (SELECT COUNT(*) FROM #ServiceInfoData where line like '%Get-Wmi%') = 0
begin
insert into DBAMonitor..[tbl_ServiceInfo](SQLServiceName,servicestatus,windowservername,startmode,startname,InstanceName,ServerId)
select  rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as SQLServiceName 
,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1, (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) ))) as ServiceStatus
--,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1, (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) ))) as WindowsServerName 
,@server_name
,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('*',line)+1, (CHARINDEX('@',line) -1)-CHARINDEX('*',line)) ))) as startmode
,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('@',line)+1, (CHARINDEX('!',line) -1)-CHARINDEX('@',line)) ))) as startname
,@SQLInstance_name
,@SrvrID
from #ServiceInfoData
where line is not null and LEN(line) > 30
end
-- Clearing output table
truncate table #ServiceInfoData
-- Next windows record
FETCH NEXT FROM server_cursor 
INTO @server_name,@SQLInstance_name,@SrvrID
END 
CLOSE server_cursor;
DEALLOCATE server_cursor;
-- dropping the temporary table
drop table #ServiceInfoData
Select 
Srvr.windowsServerName
,Srvr.SQLinstanceName 
,sevi.SQLServiceName
,sevi.startname [ServiceAccount]
,sevi.servicestatus
,sevi.UpdateDate [StatusTime]
,Case when sevi.serverid is null Then 'Server is down or Access denied!!' Else 'Check status!!' End [Remarks] 
From DBAMonitor..[tbl_ServerInfo] srvr
Left Join DBAMonitor..[tbl_ServiceInfo] sevi on srvr.ID=sevi.Serverid

---------------------------------------------------------

For better results, Create a job and run the above script from sql agent.

You can also create mailer for same to notify.

The below code is to notify if any service is stopped or any server is down.

----------------------------------------------------------------------

Declare @MailSubject varchar(1000)='Monitor info for Windows Servers and Sql Services'
Declare @MailBodyHeader varchar(3000)='Hi,<br><br>Please find the details of servers or services, which are not working perfectly-<br><br>
  <TABLE BORDER="0" CELLPADDING="0" CELLSPACING="0" WIDTH="100%" style="border-top:1px solid black; border-left:1px solid black;"><TBODY>
          <TR><TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#2f75b5" ALIGN="CENTER" colspan="7"><FONT FACE="Tahoma" SIZE="3" WIDTH="100PX" Color="white"><B>Server Monitor Info</B></FONT></TD></TR>
          <TR><TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#2f75b5" ALIGN="CENTER" colspan="7"><FONT FACE="Tahoma" SIZE="2" WIDTH="100PX" Color="white"><B>Status </B></FONT></TD></TR>
          <TR><TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>Windows Server Name</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>Sql Instance Name</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>Sql Service Name</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>Service Account</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>Service Status</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>Status Time</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>Remarks</B></FONT></TD>
           </TR>'
Declare @MailBody varchar(max)
Select @MailBody= Isnull(@MailBody,'')+'<TR><TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>'+Isnull(Srvr.windowsServerName,'')+'</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>'+Isnull(Srvr.SQLinstanceName,'')+'</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2">'+Isnull(sevi.SQLServiceName,'')+'</FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2">'+Isnull(sevi.startname,'')+'</FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2">'+Isnull(sevi.servicestatus,'')+'</FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2">'+Isnull(Cast(sevi.UpdateDate as varchar(30)),'')+'</FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2">'+Case when sevi.serverid is null Then 'Server is down or Access denied!!' Else 'Check status!!' End +'</FONT></TD>
           </TR>'
     From DBAMonitor..[tbl_ServerInfo] srvr
     Left Join DBAMonitor..[tbl_ServiceInfo] sevi on srvr.ID=sevi.Serverid
     Where servicestatus<>'Running' or servicestatus is null

Set  @MAILBODY= @MailBodyHeader +@MAILBODY+'</Table>'
IF((Select Count(1) from DBAMonitor..[tbl_ServerInfo] srvr Left Join DBAMonitor..[tbl_ServiceInfo] sevi on srvr.ID=sevi.Serverid Where servicestatus<>'Running' or servicestatus is null)>0)
Begin
EXEC MSDB.DBO.SP_SEND_DBMAIL 
     @RECIPIENTS ='vimal.lohani@gmail.com',
     @COPY_RECIPIENTS = 'vimal.lohani@gmail.com',                                   
     @BODY = @MailBody,
     @SUBJECT = @MailSubject,
     @BODY_FORMAT = 'HTML',
     @PROFILE_NAME ='dbamail' --Your profile name
End

----------------------------------------------------------------------

You can also schedule above script through agent.

Reference

MSSqlTips

Find a value in all columns of all tables

Declare @SearchString varchar(50) ='kishor.b'--<--Define your search string here

Declare  @ResultsFound TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchString + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE       TABLE_TYPE = 'BASE TABLE'
            AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM    INFORMATION_SCHEMA.COLUMNS
            WHERE       TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND TABLE_NAME  = PARSENAME(@TableName, 1)
                AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                AND QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL
        BEGIN
            INSERT INTO @ResultsFound
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END 
END

SELECT ColumnName, ColumnValue FROM @ResultsFound

Find List of Year, Month,Days On The Basis Of Month Between Two Dates

 Declare @StartDate date= '03/01/2015'
 Declare @EndDate date= '06/05/2015'
 
 select  
  year(dt) [Year], month(dt) [Month],  count(*) Days
  from 
  (
   select top (datediff(d, @StartDate, @EndDate)) dateadd(d,  row_number() over (order by (select null)), @StartDate) dt
   from sys.columns
  ) q
  group by year(dt), month(dt)
  order by [Year], [Month]

Drop And Recreate all indexes on all tables of a database :

How to use :

First execute both scripts (on SSMS) on your database and save the result. It will generate drop index  and create index for all tables on database.

After doing this, please execute drop indexes result and then execute create index result.

Please verify the result.

--First Script----------

---------Drop indexes start

DECLARE @SchemaName VARCHAR(256)
DECLARE @TableName VARCHAR(256)
DECLARE @IndexName VARCHAR(256)
DECLARE @TSQLDropIndex VARCHAR(MAX)

DECLARE CursorIndexes CURSOR
FOR
SELECT schema_name(t.schema_id)
 , t.NAME
 , i.NAME
FROM sys.indexes i
INNER JOIN sys.tables t ON t.object_id = i.object_id
WHERE i.type > 0
 AND t.is_ms_shipped = 0
 AND t.NAME <> 'sysdiagrams'
 AND (
  is_primary_key = 0
  AND is_unique_constraint = 0
  )

OPEN CursorIndexes

FETCH NEXT
FROM CursorIndexes
INTO @SchemaName
 , @TableName
 , @IndexName

WHILE @@fetch_status = 0
BEGIN
 SET @TSQLDropIndex = 'DROP INDEX ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + '.' + QUOTENAME(@IndexName)

 PRINT @TSQLDropIndex

 FETCH NEXT
 FROM CursorIndexes
 INTO @SchemaName
  , @TableName
  , @IndexName
END

CLOSE CursorIndexes

DEALLOCATE CursorIndexes
--------------------Drop indexes end--------------

PRINT '  '
PRINT '-------- DROP AND CREATE INDEXES BY VIMAL------------------'
PRINT '  '
GO

--Second Script----------

--------------------Create indexes start-----------
DECLARE @SchemaName VARCHAR(100)
DECLARE @TableName VARCHAR(256)
DECLARE @IndexName VARCHAR(256)
DECLARE @ColumnName VARCHAR(100)
DECLARE @is_unique VARCHAR(100)
DECLARE @IndexTypeDesc VARCHAR(100)
DECLARE @FileGroupName VARCHAR(100)
DECLARE @is_disabled VARCHAR(100)
DECLARE @IndexOptions VARCHAR(max)
DECLARE @IndexColumnId INT
DECLARE @IsDescendingKey INT
DECLARE @IsIncludedColumn INT
DECLARE @TSQLScripCreationIndex VARCHAR(max)
DECLARE @TSQLScripDisableIndex VARCHAR(max)

DECLARE CursorIndex CURSOR
FOR
SELECT schema_name(t.schema_id) [schema_name]
 , t.NAME
 , ix.NAME
 , CASE 
  WHEN ix.is_unique = 1
   THEN 'UNIQUE '
  ELSE ''
  END
 , ix.type_desc
 , CASE 
  WHEN ix.is_padded = 1
   THEN 'PAD_INDEX = ON, '
  ELSE 'PAD_INDEX = OFF, '
  END + CASE 
  WHEN ix.allow_page_locks = 1
   THEN 'ALLOW_PAGE_LOCKS = ON, '
  ELSE 'ALLOW_PAGE_LOCKS = OFF, '
  END + CASE 
  WHEN ix.allow_row_locks = 1
   THEN 'ALLOW_ROW_LOCKS = ON, '
  ELSE 'ALLOW_ROW_LOCKS = OFF, '
  END + CASE 
  WHEN INDEXPROPERTY(t.object_id, ix.NAME, 'IsStatistics') = 1
   THEN 'STATISTICS_NORECOMPUTE = ON, '
  ELSE 'STATISTICS_NORECOMPUTE = OFF, '
  END + CASE 
  WHEN ix.ignore_dup_key = 1
   THEN 'IGNORE_DUP_KEY = ON, '
  ELSE 'IGNORE_DUP_KEY = OFF, '
  END + 'SORT_IN_TEMPDB = OFF, FILLFACTOR =' + CASE 
  WHEN ix.fill_factor = 0
   THEN '100'
  ELSE CAST(ix.fill_factor AS VARCHAR(3))
  END AS IndexOptions
 , ix.is_disabled
 , FILEGROUP_NAME(ix.data_space_id) FileGroupName
FROM sys.tables t
INNER JOIN sys.indexes ix ON t.object_id = ix.object_id
WHERE ix.type > 0
 AND ix.is_primary_key = 0
 AND ix.is_unique_constraint = 0 --and schema_name(tb.schema_id)= @SchemaName and tb.name=@TableName
 AND t.is_ms_shipped = 0
 AND t.NAME <> 'sysdiagrams'
ORDER BY schema_name(t.schema_id)
 , t.NAME
 , ix.NAME

OPEN CursorIndex

FETCH NEXT
FROM CursorIndex
INTO @SchemaName
 , @TableName
 , @IndexName
 , @is_unique
 , @IndexTypeDesc
 , @IndexOptions
 , @is_disabled
 , @FileGroupName

WHILE (@@fetch_status = 0)
BEGIN
 DECLARE @IndexColumns VARCHAR(max)
 DECLARE @IncludedColumns VARCHAR(max)

 SET @IndexColumns = ''
 SET @IncludedColumns = ''

 DECLARE CursorIndexColumn CURSOR
 FOR
 SELECT col.NAME
  , ixc.is_descending_key
  , ixc.is_included_column
 FROM sys.tables tb
 INNER JOIN sys.indexes ix ON tb.object_id = ix.object_id
 INNER JOIN sys.index_columns ixc ON ix.object_id = ixc.object_id
  AND ix.index_id = ixc.index_id
 INNER JOIN sys.columns col ON ixc.object_id = col.object_id
  AND ixc.column_id = col.column_id
 WHERE ix.type > 0
  AND (
   ix.is_primary_key = 0
   OR ix.is_unique_constraint = 0
   )
  AND schema_name(tb.schema_id) = @SchemaName
  AND tb.NAME = @TableName
  AND ix.NAME = @IndexName
 ORDER BY ixc.index_column_id

 OPEN CursorIndexColumn

 FETCH NEXT
 FROM CursorIndexColumn
 INTO @ColumnName
  , @IsDescendingKey
  , @IsIncludedColumn

 WHILE (@@fetch_status = 0)
 BEGIN
  IF @IsIncludedColumn = 0
   SET @IndexColumns = @IndexColumns + QUOTENAME(@ColumnName) + CASE 
     WHEN @IsDescendingKey = 1
      THEN ' DESC, '
     ELSE ' ASC, '
     END
  ELSE
   SET @IncludedColumns = @IncludedColumns + QUOTENAME(@ColumnName) + ', '

  FETCH NEXT
  FROM CursorIndexColumn
  INTO @ColumnName
   , @IsDescendingKey
   , @IsIncludedColumn
 END

 CLOSE CursorIndexColumn

 DEALLOCATE CursorIndexColumn

 SET @IndexColumns = substring(@IndexColumns, 1, len(@IndexColumns) - 1)
 SET @IncludedColumns = CASE 
   WHEN len(@IncludedColumns) > 0
    THEN substring(@IncludedColumns, 1, len(@IncludedColumns) - 1)
   ELSE ''
   END
 --print @IndexColumns
 --print @IncludedColumns
 SET @TSQLScripCreationIndex = ''
 SET @TSQLScripDisableIndex = ''
 SET @TSQLScripCreationIndex = 'CREATE ' + @is_unique + @IndexTypeDesc + ' INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + '(' + @IndexColumns + ') ' + CASE 
   WHEN len(@IncludedColumns) > 0
    THEN CHAR(13) + 'INCLUDE (' + @IncludedColumns + ')'
   ELSE ''
   END + CHAR(13) + 'WITH (' + @IndexOptions + ') ON ' + QUOTENAME(@FileGroupName) + ';'

 IF @is_disabled = 1
  SET @TSQLScripDisableIndex = CHAR(13) + 'ALTER INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' DISABLE;' + CHAR(13)

 PRINT @TSQLScripCreationIndex
 PRINT @TSQLScripDisableIndex

 FETCH NEXT
 FROM CursorIndex
 INTO @SchemaName
  , @TableName
  , @IndexName
  , @is_unique
  , @IndexTypeDesc
  , @IndexOptions
  , @is_disabled
  , @FileGroupName
END

CLOSE CursorIndex

DEALLOCATE CursorIndex
 ----------------------Create indexes end--------