CreateVwIndexPhysicalStats

This view shows most of the information necessary for determining if you should REBUILD (formerly DBCC Reindex) or REORGANIZE (formerly DBCC IndexDefrag) your indexes.  This view is a dependency of vwIndexPhysicalStatsTableSize. 

As always, the database-centric views presented in this blog are written to UNION all the databases in the instance, allowing you to do maintenance on the whole server instance rather than on one database at a time.

USE Admin

IF Object_ID('dbo.CreateVwIndexPhysicalStats') Is Not Null

      DROP PROC dbo.CreateVwIndexPhysicalStats

go

CREATE PROC dbo.CreateVwIndexPhysicalStats

      @Mode varchar(20) = 'Limited',

      @ShowSql bit = 0,

      @ShowSysObjects bit = 0

AS

/*    DATE        AUTHOR            REMARKS

      3/3/13            PPaiva            Intial creation.

     

      DESCRIPTION

            Creates a view in the Admin database to see

                  fragmentation stats 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..CreateVwIndexPhysicalStats

            Exec Admin..CreateVwIndexPhysicalStats 'Limited'

            Exec Admin..CreateVwIndexPhysicalStats 'Limited', 1

            Exec Admin..CreateVwIndexPhysicalStats 'Limited', 1, 1

           

            Exec Admin..CreateVwIndexPhysicalStats 'Sampled'

            Exec Admin..CreateVwIndexPhysicalStats 'Sampled', 1

            Exec Admin..CreateVwIndexPhysicalStats 'Sampled', 1, 1

           

            Exec Admin..CreateVwIndexPhysicalStats 'Detailed'

            Exec Admin..CreateVwIndexPhysicalStats 'Detailed', 1

            Exec Admin..CreateVwIndexPhysicalStats 'Detailed', 1, 1

           

      DEBUG

            SELECT *

            FROM vwIndexPhysicalStats

            WHERE DB = 'Admin'           

            ORDER BY 1, 2, 3, 4

            -- Cannot rebuild

            SELECT *

            FROM vwIndexPhysicalStats

            WHERE DB = 'Admin'           

              AND (AllowPgLocks = 0 OR IsDisabled = 1)

            ORDER BY 1, 2, 3, 4

            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.vwIndexPhysicalStats'') Is Not Null

      DROP VIEW dbo.vwIndexPhysicalStats'

IF @ShowSql = 1

      Print @s

Exec(@s)

     

Print ''

SET @s = 'CREATE VIEW vwIndexPhysicalStats

AS

/*    DATE        AUTHOR            REMARKS

      ' + Convert(varchar(10), GetDate(), 01) + '     PPaiva            Initial creation.

      DESCRIPTION

            Provides a list of physical stats of all indexes in all databases

                  on this instance.

            This view is auto-generated via executing

                  Admin.dbo.CreateVwIndexPhysicalStats. 

            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.CreateVwIndexPhysicalStats

           

      -- Sample of view      

      SELECT TOP 100 *

      FROM Admin.dbo.vwIndexPhysicalStats

      ORDER BY PercFrag desc

*/

'

-- 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.name COLLATE SQL_Latin1_General_CP1_CI_AS IndexName,

            Convert(numeric(5,1), dmv.avg_fragmentation_in_percent) AS PercFrag,

            dmv.fragment_count AS TotalFrags,

            Convert(numeric(5,1), dmv.avg_fragment_size_in_pages) AS PagesPerFrag,

            dmv.page_count AS NumPages,        

            dmv.partition_number PartNum,

            dmv.index_type_desc IndexType,

            dmv.alloc_unit_type_desc AllocDesc,

            i.is_unique IsUniq,

            i.is_primary_key IsPK,

            i.is_unique_constraint IsUniqCon,

            dmv.index_depth IndexDepth,

            dmv.index_level IndexLevel,

            i.is_disabled IsDisabled,

            i.allow_page_locks AllowPgLocks,

            i.allow_row_locks AllowRowLocks,

            dmv.object_id,

            dmv.database_id,

            ''' + @sNow + ''' ViewCreateDate

FROM sys.dm_db_index_physical_stats(db_id(''' + @DB + '''), Null, Null, Null, ''' + @Mode + ''') dmv

JOIN [' + @DB + '].sys.indexes i

      ON  i.object_id = dmv.object_id

      AND i.index_id = dmv.index_id

JOIN [' + @DB + '].sys.objects o

      ON o.object_id = dmv.object_id

JOIN [' + @DB + '].sys.schemas s

      ON s.schema_id = o.schema_id

'

                                   

            IF @DB <> @MaxDB

                  SET @s = @s + '    UNION ALL '

                 

      END

IF @ShowSql = 1

      BEGIN

            Print @s

      END

Exec (@s)

GetIP

Get IP address from T-SQL. 

Requires xp_cmdshell to be enabled.  If this is a problem for your environment, consider enabling it, run the proc, then disable it.

NOTE:  if there is more than one IP address, the output will indicate that:

                10.1.10.92 (IPs found: 2)

In this event, you will need to add some code to differentiate which one you need.

Use Admin

go

IF Object_id('dbo.GetIP') Is Not Null

      DROP PROC dbo.GetIP

go

CREATE PROC [dbo].[GetIP]

      @IP varchar(30) OUTPUT,

      @Debug bit = 0

AS

/*    DATE        AUTHOR            REMARKS

      2/15/13           PPaiva            Initial creation.

     

      USAGE

            -- Get IP

            DECLARE @ip varchar(30)

            Exec GetIP @ip OUTPUT

            SELECT @ip IP

            -- Get IP with debug

            DECLARE @ip varchar(30)

            Exec GetIP @ip OUTPUT, 1

            SELECT @ip IP

     

      DEBUG

            --Exec sp_configure 'xp_cmdshell', 1

            --reconfigure

            SELECT *

            FROM ServerInfo

     

*/

SET NOCOUNT ON

DECLARE @s varchar(100),

            @NumIPs int

SET @s = 'ipconfig'

CREATE TABLE #ipconfig(

      ID int NOT NULL IDENTITY(1, 1) PRIMARY KEY,

      Col1 varchar(1000)

      )

INSERT INTO #ipconfig

Exec xp_cmdshell @s

DECLARE @PosColon int

IF @Debug = 1

      SELECT *

      FROM #ipconfig

     

DELETE

FROM #ipconfig

WHERE Col1 Not Like '%ipv4%'

   OR Col1 Is Null

SELECT @NumIPs = Count(*)

FROM #ipconfig

SELECT TOP 1 @PosColon = CharIndex(':', Reverse(Col1))

FROM #ipconfig

ORDER BY ID desc

SELECT TOP 1 @ip = LTrim(Right(Col1, @PosColon - 1))

FROM #ipconfig

ORDER BY ID desc

IF @NumIPs > 1

      SET @ip = @ip + ' (IPs found: ' + Convert(varchar, @NumIPs) + ')'

IF @Debug = 1

      SELECT @PosColon 'PosColon (reverse)'

     

      

FixServerName

Many like to use the global variable @@ServerName when you need to provide the instance name of your SQL Server.  However, if the machine has been renamed after SQL Server was installed, this variable will be incorrect – it will have the previous instance name of SQL Server.

ServerProperty('ServerName')) will always return the correct instance name.  But it is a bit clumsy to use because its datatype is sql_variant, so you have to first convert it to char or varchar.

This proc will identify and fix the problem.  A service stop/start is required after running the fix.

Use Admin

go

IF Object_ID('dbo.FixServerName') Is Not Null

      DROP PROC dbo.FixServerName

go

CREATE PROC dbo.FixServerName

      @Debug bit = 1

AS

/*    DATE        AUTHOR            REMARKS

      2/10/13           PPaiva            Initial creation.

     

      DESCRIPTION

            Determines if @@ServerName is the same as ServerProperty('ServerName').

                  If not, then the server has likely been renamed after SQL Server

                  was installed. 

                 

            To fix, run this with @Debug = 0, then stop/start the SQL Server service.

     

      USAGE

            FixServerName           -- Examine only, don't fix

            FixServerName 0         -- Fix

     

*/

DECLARE @ServerNameGlobalVar varchar(50),

            @ServerNameProperty varchar(50),

            @Msg varchar(1000)

SET @ServerNameGlobalVar = @@ServerName + 'h'

SET @ServerNameProperty = Convert(varchar(50), ServerProperty('ServerName'))

IF @ServerNameGlobalVar = @ServerNameProperty

      BEGIN

            SET @Msg = '@@ServerName has the correct value.  You can use @@ServerName whenever you need '

                        + Char(13) + 'to provide the SQL instance name.  Nothing to fix.'

                        + Char(13) + '                    @@ServerName = ' + @ServerNameGlobalVar

                        + Char(13) + '    ServerProperty(''ServerName'') = ' + @ServerNameProperty

            Print @Msg

      END

     

ELSE

      BEGIN

            IF @Debug = 1

                  BEGIN

                        SET @Msg = '@@ServerName does NOT have the correct value. Needs to be fixed.'

                                    + Char(13) + '                    @@ServerName = ' + @ServerNameGlobalVar

                                    + Char(13) + '    ServerProperty(''ServerName'') = ' + @ServerNameProperty

                                    + Char(13) + 'To fix, run the following, then stop/start the SQL Server service.'

                                    + Char(13) + '    Exec FixServerName 0'

                        Print @Msg

                  END                          

                 

            ELSE             

                  BEGIN

                        SET @Msg = 'Exec master.dbo.sp_dropserver ''' + @@ServerName + ''''

                        Print @Msg

                        Exec(@Msg)

                        SET @Msg = 'Exec master.dbo.sp_addserver ''' + @ServerNameProperty + ''', ''local'''

                        Print @Msg

                        Exec(@Msg)

                       

                        SET @Msg = 'Need to stop/start the SQL Server service, or reboot, for change to take effect.'

                        Print ''

                        Print @Msg

                  END

                 

      END

      

SearchInCode

Need to search for a string contained in a proc, view, function, or trigger?  You can use sys.syscomments and JOIN to sysobjects, but it is nice to do all the JOINing beforehand and simply use a nice object dedicated to searching.

USE Admin

GO

IF Object_ID('dbo.SearchInCode') Is Not Null

      DROP PROC dbo.SearchInCode

go

CREATE PROC dbo.SearchInCode

                        @DB varchar(50),

                        @Sort bit = 1,

                        @SearchText1 varchar(200),

                        @SearchText2 varchar(200) = Null,

                        @SearchText3 varchar(200) = Null,

                        @ShowSql bit = 0

AS

/*    DATE        AUTHOR            REMARKS

      1/8/13           PPaiva            Initial creation.

     

     

      DESCRIPTION

            For a given database, lists any procs, functions,

                  triggers or views that contain @SearchText1,

                  AND optionally @SearchText2,

                  AND optionally @SearchText3. 

      USAGE

            Exec Admin..SearchInCode 'Admin', 1, 'File'

            Exec Admin..SearchInCode 'Sopa', 1, 'EmployeeID'

            Exec Admin..SearchInCode 'Sopa', 1, 'EmployeeID', 'log'

            Exec Admin..SearchInCode 'Sopa', 1, 'EmployeeID', 'log', 'Activity', 1

*/

SET NOCOUNT ON

IF Object_ID('tempdb.dbo.##SearchInCode') Is Not Null

      DROP TABLE ##SearchInCode

CREATE TABLE ##SearchInCode(

      ID int NOT NULL IDENTITY(1, 1) PRIMARY KEY,

      Server varchar(100),

      DB varchar(200),

      ObjType varchar(50),

      SchemaName sysname,

      ObjName sysname,

      Text varchar(max),

      FullName varchar(300),

      SearchForTextRunDate datetime NOT NULL DEFAULT GetDate()

      )

DECLARE @Sql varchar(2000)

SET @Sql =

'SELECT ''' + @@ServerName + ''' Server,

            ''' + @DB + ''' DB,

            ot.Description ObjType,

            s.Name SchemaName,

            o.Name ObjName,

            c.Text,

            ''[' + @DB + '].['' +  s.name + ''].['' + o.name + '']'' FullName

FROM [' + @DB + '].dbo.syscomments c

JOIN [' + @DB + '].sys.objects o

      ON c.ID = o.object_id

LEFT JOIN Admin.dbo.infraObjectType ot

      ON ot.Type = o.Type COLLATE SQL_Latin1_General_CP1_CI_AS

JOIN [' + @DB + '].sys.schemas s

      ON s.schema_id = o.schema_id

WHERE 1 = 1'

IF @SearchText2 Is Null AND @SearchText3 Is Null

      SET @Sql = @Sql + '

  AND c.Text Like ''%' + @SearchText1 + '%'''

ELSE IF @SearchText3 Is Null

      SET @Sql = @Sql + '

  AND c.Text Like ''%' + @SearchText1 + '%''

  AND c.Text Like ''%' + @SearchText2 + '%'''

ELSE

      SET @Sql = @Sql + '

  AND c.Text Like ''%' + @SearchText1 + '%''

  AND c.Text Like ''%' + @SearchText2 + '%''

  AND c.Text Like ''%' + @SearchText3 + '%'''

IF @Sort = 1

      SET @Sql = @Sql + '

ORDER BY ot.Description, Substring(o.name, 1, 1)'

IF @ShowSql = 1

      Print @Sql

INSERT INTO ##SearchInCode (Server, DB, SchemaName, ObjType, ObjName, Text, FullName)

      Exec(@Sql)

Print '

SELECT *

FROM ##SearchInCode

ORDER BY 1

'

SELECT *

FROM ##SearchInCode

ORDER BY 1

vwLogInfoSummary - show Virtual Log Files per database

In the last post on Virtual Log Files, a procedure was given, PopLogInfo, to populate table LogInfo. 

Here, a view is presented to make best use of that table, to see how many VLFs a given database has.

IF object_id('dbo.vwLogInfoSummary') Is Not Null

      DROP VIEW dbo.vwLogInfoSummary

GO

CREATE VIEW vwLogInfoSummary

AS

/*    DATE        AUTHOR            REMARKS    

      12/15/12    PPaiva            Initial creation.

     

      SELECT *

      FROM LogInfo

     

      SELECT *

      FROM vwLogInfoSummary

      ORDER BY NumVLFs desc

*/

SELECT      Server,

                  DB,

                  FileID,

                  Status,

                  StatusDesc,

                  COUNT(*) NumVLFs,

                  InsertDate

      FROM LogInfo

      WHERE StatusDesc = 'Reusable'

      GROUP BY Server, DB, FileID, Status, StatusDesc, InsertDate