About Me

My photo
Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present.

Sunday, September 18, 2011

CreateVwIndexRaw

Creates a view that shows all indexes for all objects in all databases on this SQL instance.  System databases and objects are excluded by default, but you can optionally include them by setting @ShowSysObjects = 1.  This is helpful in ferreting out exactly what covering indexes exist for a given query, or for identifying duplicate indexes.  

There is no system procedure that shows any included columns for an index.  Note results of this system proc:

sp_helpindex employees



Info about included columns must be obtained from querying these two system views:

SELECT *
FROM sys.indexes

SELECT *
FROM sys.index_columns

After running CreateVwIndexRaw, this query will now show the included columns, for all objects in all databases in this SQL instance.

SELECT *
FROM Admin..vwIndexRaw
WHERE DB = 'Sopa'
  AND ObjName = 'Employees'



Use Admin
go

IF object_id('dbo.CreateVwIndexRaw') Is Not Null
      DROP Proc dbo.CreateVwIndexRaw
go

CREATE PROC dbo.CreateVwIndexRaw
      @ShowSql bit = 0,
      @ShowSysObjects bit = 0
AS
/*    DATE        AUTHOR            REMARKS
      9/18/11           PPaiva            Intial creation.


      DESCRIPTION
            Creates a view in the Admin database to see
                  all indexes for all objects in all databases
                  on this server.
            System objects are excluded by default;
                  use @ShowSysObjects = 1 to show them.
            Coded to use Convert(varchar, ServerProperty('ServerName'))
                  rather than @@ServerName since the latter can be erroneous.

      USAGE
            Exec Admin..CreateVwIndexRaw 1
            Exec Admin..CreateVwIndexRaw 1, 1

      DEBUG
            SELECT *
            FROM Admin.dbo.vwIndexRaw
            ORDER BY Server, DB, SchemaName, ObjName, IndexName, Ord

            SELECT *
            FROM Admin.dbo.vwIndexRaw
            WHERE Include = 1
            ORDER BY DB, ObjName, IndexName, Ord

            SELECT *
            FROM sys.databases

*/
SET NOCOUNT ON

DECLARE @s varchar(max),
            @DB varchar(100),
            @MaxDB varchar(100),
            @i int,
            @sNow varchar(16)

SET @sNow = Convert(varchar(16), GetDate(), 120)


SET @s = 'IF Object_ID(''dbo.vwIndexRaw'') Is Not Null
      DROP VIEW dbo.vwIndexRaw'

IF @ShowSql = 1
      Print @s

Exec(@s)
     
Print ''

SET @s = 'CREATE VIEW vwIndexRaw
AS
/*    DATE        AUTHOR            REMARKS
      ' + Convert(varchar(10), GetDate(), 01) + '     PPaiva            Initial creation.

      DESCRIPTION
            Provides a list of all indexes in all databases
                  on this instance.
            This view is auto-generated on a daily basis via
                  Admin.dbo.CreateVwIndex. 
            If a new database is added this view won''t show it unless
                  you run the refresh code below. 
            If a database is deleted this view will malfunction unless
                  you run the refresh code below. 
                 
      To REFRESH VIEW when new databases are added:
            Exec Admin.dbo.CreateVwIndex
           

      -- Sample of view      
      SELECT TOP 100 *
      FROM Admin.dbo.vwIndexRaw
      ORDER BY Server, dB, SchemaName, ObjName, IndexName, Ord


*/
'

-- Will refer to these system databases more than once,
-- so load into table for easy reference.
CREATE TABLE #SysDB(
      Name varchar(30) NOT NULL
      )
INSERT INTO #SysDB VALUES ('master')
INSERT INTO #SysDB VALUES ('tempdb')
INSERT INTO #SysDB VALUES ('model')
INSERT INTO #SysDB VALUES ('msdb')
INSERT INTO #SysDB VALUES ('Admin')
INSERT INTO #SysDB VALUES ('distribution')


SELECT name
INTO #DBs
FROM sys.databases
WHERE state_desc = 'online'

IF @ShowSysObjects = 0
      DELETE #DBs
      FROM #DBs d
      JOIN #SysDB s
            ON s.Name = d.name

SELECT  @MaxDB = Max(Name),
            @DB = '',
            @i = 0
FROM #DBs


DECLARE @IsSystemDB char(1)

WHILE @DB < @MaxDB
      BEGIN
            SET @i = @i + 1

            SELECT @DB = Min(Name)
            FROM #DBs
            WHERE Name > @DB
           
            IF @DB In (SELECT Name FROM #SysDB)
                  SET @IsSystemDB = '1'
            ELSE
                  SET @IsSystemDB = '0'


            SET @s = @s + '
SELECT  '

            SET @s = @s + 'Convert(varchar, ServerProperty(''ServerName'')) as Server,
            ''' + @DB + ''' as DB,
            s.name COLLATE SQL_Latin1_General_CP1_CI_AS SchemaName,
            o.name COLLATE SQL_Latin1_General_CP1_CI_AS ObjName,
            i.is_primary_key PK,
            i.is_unique Uniq,
            i.name COLLATE SQL_Latin1_General_CP1_CI_AS IndexName,
            ic.key_ordinal Ord,
            c.name COLLATE SQL_Latin1_General_CP1_CI_AS ColName,
            CASE WHEN ic.is_descending_key = 0
                        THEN ''asc''
                        ELSE ''desc''
                  END Sort,
            i.type_desc Type,
            ic.is_included_column Include,
            i.is_unique_constraint UniqCons,
            i.fill_factor FF,
            ic.column_id ColID,
            i.object_id,
            c.column_id,
            o.create_date ObjCreateDate,
            o.modify_date ObjModifyDate,
            ' + @IsSystemDB + ' IsSystemDB,
            ''' + @sNow + ''' ViewCreateDate
FROM [' + @DB + '].sys.indexes i
JOIN [' + @DB + '].sys.index_columns ic
      ON  ic.object_id = i.object_id
      AND ic.index_id = i.index_id
JOIN [' + @DB + '].sys.columns c
      ON  c.object_id = ic.object_id
      AND c.column_id = ic.column_id
JOIN [' + @DB + '].sys.objects o
      ON o.object_id = i.object_id
JOIN [' + @DB + '].sys.schemas s
      ON s.schema_id = o.schema_id
'

IF @ShowSysObjects = 0
      SET @s = @s + 'WHERE o.type <> ''s''
  AND s.name <> ''sys''
'

                                   
            IF @DB <> @MaxDB
                  SET @s = @s + '    UNION ALL '
                 

      END



IF @ShowSql = 1
      BEGIN
            Print @s
      END

Exec (@s)

Friday, September 16, 2011

Customized Keyboard Shortcuts in SSMS

The past few posts have presented some useful code that you might want run readily, at your fingertips.  SQL Server Management Studio (SSMS) can be configured to launch a proc for certain keystrokes.

Go to Tools – Options, then drill down to Environment – Keyboard.  Note that there are some pre-programmed shortcuts which cannot be changed:

Alt+F1   sp_help
Ctrl+1    sp_who
Ctrl+2    sp_lock

In this image, note that I have programmed Ctrl+3.



This proc simply calls out various queries so that can be invoked from the keyboard shortcuts.  I have my keyboard set for these additional shortcuts:

Ctrl+3    vwWho2
Ctrl+4    vwWho3
Ctrl+5    vwWhoActiveSummary
Ctrl+9    vwJobHistory
Ctrl+0    vwJob





After programming the shortcuts, you will have to open a new query window for them to take effect.  You will notice that the Messages tab of the query results pane shows some text that can be copied/pasted into a new window, in case you wish to further refine your query.

Use Admin
go

IF object_id('dbo.KeyboardShortcut') Is Not Null
      DROP PROC KeyboardShortcut
go

CREATE PROC dbo.KeyboardShortcut
      @Code char(5)
AS
/*    DATE        AUTHOR            REMARKS
      9/15/11           PPaiva            Initial creation.

      DESCRIPTION
            Simply selects from a given view.  This is so
                  that a shortcut   can be made to this proc, via SSMC.

      USAGE
            Exec Admin.dbo.KeyboardShortcut 3
            Exec Admin.dbo.KeyboardShortcut 4
            Exec Admin.dbo.KeyboardShortcut 5
            Exec Admin.dbo.KeyboardShortcut 9
            Exec Admin.dbo.KeyboardShortcut 0

*/
SET NOCOUNT ON

IF @Code = '3'    -- Ctrl-3 (shortcut for SSMC)
      SELECT *
      FROM Admin.dbo.vwWho2
      ORDER BY last_batch desc

IF @Code = '4'    -- Ctrl-4
      SELECT *
      FROM Admin.dbo.vwWho3
      ORDER BY last_batch desc
     
IF @Code = '5'    -- Ctrl-5
      SELECT *
      FROM Admin.dbo.vwWhoActiveSummary
      ORDER BY blocked desc, spid

IF @Code In('J', '0')   -- Ctrl-0
      SELECT *
      FROM Admin..vwJob
      ORDER BY NextRun desc, NextRunDateTime

IF @Code In('9')  -- Ctrl-9
      SELECT *
      FROM Admin..vwJobHistory
      ORDER BY RunDateTime desc




IF @Code In ('3', '4', '5')
      Print '
-- Same as Ctrl-3
SELECT *
FROM Admin.dbo.vwWho2
--WHERE LogiName = ''COCREATE\Paul''
--WHERE HostName = ''
--WHERE spid =
ORDER BY last_batch desc

-- Same as Ctrl-4
SELECT *
FROM Admin.dbo.vwWho3
--WHERE LogiName = ''COCREATE\Paul''
--WHERE HostName = ''
--WHERE spid =
ORDER BY last_batch desc

-- Same as Ctrl-5
SELECT *
FROM Admin.dbo.vwWhoActive
--WHERE LogiName = ''COCREATE\Paul''
--WHERE HostName = ''
--WHERE spid =
ORDER BY blocked desc, spid

-- Ctrl 5 (optional sort)
SELECT *
FROM Admin.dbo.vwWhoActive
--WHERE LogiName = ''COCREATE\Paul''
--WHERE HostName = ''
--WHERE spid =
ORDER BY DB, LogiName, Qty desc
'

IF @Code In ('9', '0', 'J')
      Print '-- Ctrl-0
SELECT *
FROM Admin..vwJob
ORDER BY NextRun desc, NextRunDateTime
--ORDER BY Name

-- Ctrl-9
SELECT *
FROM Admin..vwJobHistory
--WHERE Status = ''Failed''
ORDER BY RunDateTime desc
'