Show All Indexes And Statistics

A simple script to show all the indexes and statistics.

-- Displays list of all indexes and statistics.
DECLARE @sTableName sysname
SET @sTableName = 'YourTableName'

SELECT OBJECT_NAME(id)                    AS 'Table Name',

       CASE 
            WHEN name IS NULL THEN   '< Heap Table >'
            ELSE                      name
            END                           AS 'I or S Name',
            
       CASE INDEXPROPERTY(id, name, 'IsStatistics')
            WHEN 1 THEN 'Statistic'
            ELSE
               CASE INDEXPROPERTY(id, name, 'indexid')
               WHEN 0 THEN '-'
               ELSE 'Index'
               END            
            END                           AS 'I or S ?',

       CASE INDEXPROPERTY(id, name, 'IsStatistics')
            WHEN 1 THEN '-' 
            ELSE
               CASE INDEXPROPERTY(id, name, 'indexid')
               WHEN 0 THEN '-'
               ELSE        CAST(INDEXPROPERTY(id, name, 'indexid') AS sysname)
               END
            END                           AS 'Index ID',
      
       CASE INDEXPROPERTY(id, name, 'IsStatistics')
            WHEN 0 THEN 
                        CASE INDEXPROPERTY(id, name, 'indexid')
                           WHEN 0 THEN '-'
                           WHEN 1 THEN 'Clustered'
                           ELSE        'Non-Clustered'
                        END
            ELSE '-'
            END                           AS 'Index Type',
            
       CASE INDEXPROPERTY(id, name, 'IsUnique')
            WHEN 1 THEN 'Unique'
            ELSE        '-'
            END                           AS 'Unique?'

  FROM sysindexes
 WHERE INDEXPROPERTY(id, name, 'IsHypothetical') = 0
-- AND id = OBJECT_ID(@sTableName)                           -- Restrict by table.
-- AND INDEXPROPERTY(id, name, 'IsStatistics')   = 0         -- 0 = Index, 1 = Statistic
-- AND INDEXPROPERTY(id, name, 'IsUnique')       = 0         -- Restrict by index type.
 ORDER BY 'Table Name',
          'Index Type'

List All Database Objects By Type and Owner

This script lists all the objects in the current database.

-- ListOwnersForAllObjects.sql
-- SQL Server 2000 and later.
SELECT
   NAME               AS Object,
   USER_NAME(uid)     AS Owner,
   CASE (xtype)
      WHEN 'AF' THEN 'Aggregate function (CLR)'
      WHEN 'C'  THEN 'CHECK constraint' 
      WHEN 'D'  THEN 'DEFAULT (constraint or stand-alone)'
      WHEN 'F'  THEN 'FOREIGN KEY constraint'
      WHEN 'PK' THEN 'PRIMARY KEY constraint'
      WHEN 'P'  THEN 'SQL stored procedure'
      WHEN 'PC' THEN 'Assembly (CLR) stored procedure'
      WHEN 'FN' THEN 'SQL scalar function'
      WHEN 'FS' THEN 'Assembly (CLR) scalar function'
      WHEN 'FT' THEN 'Assembly (CLR) table-valued function'
      WHEN 'R'  THEN 'Rule (old-style, stand-alone)'
      WHEN 'RF' THEN 'Replication-filter-procedure'
      WHEN 'S'  THEN 'System base table'
      WHEN 'SN' THEN 'Synonym'
      WHEN 'SQ' THEN 'Service queue'
      WHEN 'TA' THEN 'Assembly (CLR) DML trigger'
      WHEN 'TR' THEN 'SQL DML trigger '
      WHEN 'IF' THEN 'SQL inline table-valued function'
      WHEN 'TF' THEN 'SQL table-valued-function'
      WHEN 'U'  THEN 'Table (user-defined)'
      WHEN 'UQ' THEN 'UNIQUE constraint'
      WHEN 'V'  THEN 'View'
      WHEN 'X'  THEN 'Extended stored procedure'
      WHEN 'IT' THEN 'Internal table'
   END            AS 'Object Type'
  FROM sysobjects
 WHERE USER_NAME(uid) NOT IN ('sys', 'INFORMATION_SCHEMA')
 ORDER BY 'Object Type', Owner, Object

SERVERPROPERTY('EditionID') BOL Value Wrong

I'm trying to write a UDF that returns version information, and to make a long story short, I was executing:

SELECT SERVERPROPERTY('EditionID')

...and getting back a value of -133711905. In BOL (http://msdn.microsoft.com/en-us/library/ms174396.aspx ), the table for 'EditionID' is:

-1253826760 = Desktop 
-1592396055 = Express 
-1534726760 = Standard 
 1333529388 = Workgroup 
 1804890536 = Enterprise 
 -323382091 = Personal 
-2117995310 = Developer 
  610778273 = Enterprise Evaluation 
 1044790755 = Windows Embedded SQL
 4161255391 = Express with Advanced Services

Base data type: int

Hmmm... my value doesn't appear? After an embarressing amount of bit twiddling, I finally noticed that the last value in the table, 4161255391, and the value I was getting, -133711905, were actually the same: 0xF807B7DF. Okay, I mused, it's a signed/unsigned issue - but SQL Server doesn't have any unsigned 32-bit integers. That's when I noticed that 4161255391 was an awful big number to be a 32-bit signed integer (a.k.a., INT). So, this is a "documentation bug": the table should use the signed -133711905 value, not the unsigned 4161255391. Maybe this will help someone else.