Script for Unused Indexes

DECLARE
@DATABASENAME VARCHAR(2000),
@DBID INT,
@SQL VARCHAR(4000),
@STATUS SQL_VARIANT

IF OBJECT_ID ('TEMPDB..##TBLDB') IS NOT NULL

DROP TABLE TEMPDB..##TBLDB
/* CREATING A TEMP TABLE*/

CREATE TABLE ##TBLDB(TABLENAME VARCHAR(200), INDEXNAME VARCHAR(200), INDEXID INT,DATABASENAME VARCHAR(200))

/* CURSIN THROUGH ALL THE DATABASES ON THE SERVER*/

DECLARE CURSOR_DATABASES CURSOR FOR

      SELECT
            NAME, DATABASE_ID
      FROM
            SYS.DATABASES
      WHERE
            NAME NOT IN ('MASTER','MODEL','MSDB','TEMPDB')

OPEN CURSOR_DATABASES
FETCH NEXT FROM CURSOR_DATABASES INTO @DATABASENAME,@DBID

WHILE @@FETCH_STATUS = 0
BEGIN
      SELECT @Status = DatabasePropertyEX(@DATABASENAME, 'Status')
      IF @STATUS = 'ONLINE'
     
      /*THIS SCRIPT GETS A LIST OF ALL THE INDEXES THAT ARE NOT BEING USED*/
      BEGIN
            SET @SQL = 'INSERT INTO ##TBLDB
                  SELECT
                  O.NAME AS TABLENAME,
                  INDEXNAME = I.NAME,
                  I.INDEX_ID,
                  DATABASENAME = '''+ @DATABASENAME +
             ''' FROM '+ @DATABASENAME + '.SYS.INDEXES I
                  INNER JOIN ' + @DATABASENAME  +
                  '.SYS.OBJECTS O
                  ON I.OBJECT_ID = O.OBJECT_ID
                  LEFT JOIN
                  SYS.DM_DB_INDEX_USAGE_STATS S
                  ON S.OBJECT_ID = I.OBJECT_ID
                  AND I.INDEX_ID = S.INDEX_ID
            WHERE
                  O.TYPE_DESC = ''USER_TABLE''
                  AND I.NAME IS NOT NULL
                  AND I.NAME <> ''pk_dtproperties''
                  AND ISNULL(S.USER_SCANS, 0) = 0
                  AND   ISNULL(S.USER_SEEKS, 0) = 0
                  AND ISNULL(S.SYSTEM_SCANS, 0) = 0
                  AND DATABASE_ID = '+ CONVERT(VARCHAR(100),@DBID) + '
            ORDER BY O.NAME,
                  I.INDEX_ID,
                  INDEXNAME ASC'

            EXEC (@SQL)
      END
      FETCH NEXT FROM CURSOR_DATABASES INTO @DATABASENAME,@DBID
END
CLOSE CURSOR_DATABASES
DEALLOCATE CURSOR_DATABASES

SELECT * FROM ##TBLDB

DROP TABLE ##TBLDB

Comments

Post a Comment

Popular posts from this blog

SysDictField object not initialized. - Views

One of my Colleague had this issue.. I’m using a View (with some calculated fields), as the source for another View (summed fields, so I’m working with “total” records). This 2 nd View continues to have this error message: SysDictField object not initialized. Stack trace (C)\Classes\SysTableBrowser\AddField - line 32 (C)\Classes\SysTableBrowser\showAutoFields - line 95 (C)\Classes\SysTableBrowser\run - line 67 Solution: Go to the Field Groups and delete the fields that are in the Autoreport and delete the fields that are Autoreport and not in the Field List of the View.
Read more

How to filter data for the last few weeks?

I got a task where I had to get the data for the last few weeks excluding the current week. The week begins on a Sunday and ends on a Saturday. Here is the code: { Date dateFrom; Date dateTo; date fd; ; fd = dateStartWk(systemDateGet()); // get the First day of the Week dateTo = fd - 2; // Subtracted 2 since the first day is a Monday dateFrom = dateTo - ((5 * 7) -1); // shud be 3*7 - 1 as ToDate value is also Included info(strFmt("%1-%2-%3", fd, dateTo, dateFrom)); }
Read more

Get the value of Month or YY in SysComputedCOlumns in AX views

Here is the Code: public static server str WFinvoiceDateMM() {     str sDate =         SysComputedColumn::returnfield(             tableStr (WFSyscoInvoicesV),             queryDataSourceStr (WFSyscoInvoicesQ, CustInvoiceJour),             fieldStr (CustInvoiceJour, InvoiceDate)             );     return 'Month(' + SysComputedColumn::fromUtcDateToDate(sDate) + ')' ; }
Read more