Missing Index on Date Field (Performance Tuning)

Sql2K:

Can a procedure be made for

For each database
     For each table
           Find the very first column name with data type smalldatetime or datetime

                If found
                   check if index has been created on that column
       
                If created
                    Ignore
                Else
                    Report the column and other information

Reporting Style


DatabaseName TableName ColumnName  NumberofRowsIntheTable
-----------------  ------------- ----------------  -------------------------------
     


MateenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
Here is the code to check all tables in any single db (using the approx. row count from sysindexes; if you need to, we can change it to a true COUNT(*) from the table, but, depending on the table sizes, that could take *much* longer to run):


SELECT LEFT(OBJECT_NAME([TableId]), 80) AS [Table Name],
      COL_NAME([TableId], [ColId]) AS [Column Name],
      [Approx. Row Count]
FROM (
      SELECT col.id AS [TableId],
          MIN(col.colid) AS [ColId],
          (SELECT rowcnt
           FROM sysindexes WITH (NOLOCK)
           WHERE id = col.id
           AND indid IN (0,1)) AS [Approx. Row Count]
      FROM syscolumns col WITH (NOLOCK)
      INNER JOIN sysobjects obj WITH (NOLOCK) ON col.id = obj.id
      INNER JOIN systypes typ WITH (NOLOCK) ON col.xtype = typ.xtype
      WHERE obj.xtype = 'U' --user table
        AND typ.name IN ('datetime', 'smalldatetime') --only dates
        AND NOT EXISTS (
                SELECT 1
                FROM sysindexkeys ixk WITH (NOLOCK)
                WHERE ixk.id = col.id
                  AND ixk.colid = col.colid )
      GROUP BY col.id
) AS derived

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
And to run all dbs at once, something like this:


EXEC sp_MSForEachDb '
IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') --<<-- add others if desired
BEGIN
USE ?
SELECT LEFT(DB_NAME(), 50) AS [Database Name],
    LEFT(OBJECT_NAME([TableId]), 80) AS [Table Name],
      COL_NAME([TableId], [ColId]) AS [Column Name],
      [Approx. Row Count]
FROM (
      SELECT col.id AS [TableId],
          MIN(col.colid) AS [ColId],
          (SELECT rowcnt
           FROM sysindexes WITH (NOLOCK)
           WHERE id = col.id
           AND indid IN (0,1)) AS [Approx. Row Count]
      FROM syscolumns col WITH (NOLOCK)
      INNER JOIN sysobjects obj WITH (NOLOCK) ON col.id = obj.id
      INNER JOIN systypes typ WITH (NOLOCK) ON col.xtype = typ.xtype
      WHERE obj.xtype = ''U'' --user table
        AND typ.name IN (''datetime'', ''smalldatetime'') --only dates
        AND NOT EXISTS (
                SELECT 1
                FROM sysindexkeys ixk WITH (NOLOCK)
                WHERE ixk.id = col.id
                  AND ixk.colid = col.colid )
      GROUP BY col.id
) AS derived
END
'
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.