About Me

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 for 11 years, from January 2009 to 2020.

Saturday, June 22, 2013

This function simply formats an integer with commas to separate thousands, if necessary. 

This object is referenced by various other objects in this blog, including view vwTable which will be presented soon.

USE Admin
IF Object_ID('dbo.udfFormatInteger') Is Not Null
      DROP FUNCTION dbo.udfFormatInteger
go

CREATE FUNCTION dbo.udfFormatInteger
      (@In bigint)
RETURNS varchar(20)
AS
/*    DATE              AUTHOR            REMARKS    
      6/22/13           PPaiva            Initial creation.
     
      DESCRIPTION
            Formats a given integer with commas.
                  Examples:
                              IN                OUT
                              123               123
                            12345            12,345
                           -54321           -54,321

      USAGE
            SELECT dbo.udfFormatInteger(1234567890)
            SELECT dbo.udfFormatInteger(12345)
            SELECT dbo.udfFormatInteger(-54321)
            SELECT dbo.udfFormatInteger(0)
*/

BEGIN
      DECLARE @Out varchar(20),
                  @sIn varchar(20),
                  @Balance varchar(20),
                  @CurrTextLen smallint,
                  @IsNegative bit

      IF @In < 0
            BEGIN
                  SET @IsNegative = 1
                  SET @In = Abs(@In)
            END

      SET @sIn = Convert(varchar, @In)
      SET @CurrTextLen = Len(@sIn)
      SET @Out = ''
      SET @Balance = @sIn

      IF @CurrTextLen > 3
            BEGIN
                  WHILE 1 = 1
                        BEGIN
                              SET @Out = ',' + Right(@Balance, 3) + @Out
                              SET @Balance = Substring(@sIn, 1, @CurrTextLen - 3)

                              SET @CurrTextLen = Len(@Balance)
                              IF @CurrTextLen > 3
                                    CONTINUE
                              ELSE
                                    BEGIN
                                          SET @Out = @Balance + @Out
                                          BREAK
                                    END  
                        END
            END

      ELSE
            SET @Out = @sIn

      IF @IsNegative = 1
            SET @Out = '-' + @Out

      RETURN @Out

END






Thursday, May 2, 2013

CreateVwDictCol

This proc creates view vwDictCol which is a column dictionary.  You could also use INFORMATION_SCHEMA.Columns.  This has the advantage of being instance-wide (all databases) rather than for only one database.  This view encompasses tables, views, functions, and procedures.

This object is referenced by view vwTable which will be presented soon.

USE Admin
IF Object_ID('dbo.CreateVwDictCol') Is Not Null
      DROP PROC dbo.CreateVwDictCol
go

CREATE  PROC dbo.CreateVwDictCol
      @ShowSql bit = 0,
      @ShowSysObjects bit = 0
AS
/*    DATE        AUTHOR            REMARKS
      5/1/13            PPaiva            Intial creation.

      DESCRIPTION
            Creates a view in the Admin database to see a column
                  dictionary of tables and other objects for all
                  databases in this server.
            System objects are excluded.
            Coded to use Convert(varchar, ServerProperty('ServerName'))
                  rather than @@ServerName since the latter can be erroneous.

      USAGE
            Exec Admin..CreateVwDictCol
            Exec Admin..CreateVwDictCol 1
            Exec Admin..CreateVwDictCol 1, 1

      DEBUG
            SELECT *
            FROM sys.databases

            SELECT *
            FROM Admin.dbo.vwDictCol
            WHERE DB = 'MyDB'

            -- Summary of Object Types
            SELECT ObjType, Count(*) Qty
            FROM Admin.dbo.vwDictCol
            GROUP BY ObjType
            ORDER BY 1

            -- Summary of Object Types per DB
            SELECT DB, ObjType, Count(*) Qty
            FROM Admin.dbo.vwDictCol
            GROUP BY DB, ObjType
            ORDER BY 1, 2

            -- Summary of rows/DB
            SELECT DB, Count(*) Qty
            FROM Admin.dbo.vwDictCol
            GROUP BY DB
            ORDER BY 1

            -- All views
            SELECT *
            FROM Admin.dbo.vwDictCol
            WHERE ObjType = 'View'
           

*/
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.vwDictCol'') Is Not Null
      DROP VIEW dbo.vwDictCol'

IF @ShowSql = 1
      Print @s
Exec(@s)
     
Print ''

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

      DESCRIPTION
            Provides a dictionary of columns for
                  tables
                  views
                  functions (columns for tables, parameters for scalar)
                  procedures (parameter)
            This view is auto-generated via execution of
                  Admin.dbo.CreateVwDictCol. 
            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.CreateVwDictCol
           

      -- Sample of view      
      SELECT TOP 100 *
      FROM Admin.dbo.vwDictCol

      -- Summary of Object Types on this instance
      SELECT ObjType, Count(*) Qty
      FROM Admin.dbo.vwDictCol
      GROUP BY ObjType
      ORDER BY 1

      -- Summary of Object Types per DB
      SELECT DB, ObjType, Count(*) Qty
      FROM Admin.dbo.vwDictCol
      GROUP BY DB, ObjType
      ORDER BY 1, 2

      -- Most objects per DB
      SELECT DB, Count(*) Qty
      FROM Admin.dbo.vwDictCol
      GROUP BY DB
      ORDER BY 2 desc

*/
'

SELECT  *
INTO #DBs
FROM sys.databases
WHERE state_desc = 'ONLINE'

 
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 ('master', 'tempdb', 'model', 'msdb', 'Admin', 'distribution')
                  SET @IsSystemDB = '1'
            ELSE
                  SET @IsSystemDB = '0'


            SET @s = @s + '
SELECT  '

            SET @s = @s + 'Convert(varchar, ServerProperty(''ServerName'')) as Server,
            ''' + @DB + ''' as DB,
            ot.DescShort ObjType,
            s.name COLLATE SQL_Latin1_General_CP1_CI_AS SchemaName,
            o.Name COLLATE SQL_Latin1_General_CP1_CI_AS ObjName,
            c.Name COLLATE SQL_Latin1_General_CP1_CI_AS ColName,
            c.Is_Computed IsCalc,
            c.Is_Identity IsIdentity,
            c.is_nullable IsNullable,
            c.column_id ColID,
            t.Name COLLATE SQL_Latin1_General_CP1_CI_AS Datatype,
            c.max_length Length,
            CASE WHEN t.Name COLLATE SQL_Latin1_General_CP1_CI_AS In (''char'', ''varchar'', ''nchar'', ''nvarchar'')
                        THEN t.Name COLLATE SQL_Latin1_General_CP1_CI_AS + ''('' + Convert(varchar, c.max_Length) + '')''
                   ELSE t.Name COLLATE SQL_Latin1_General_CP1_CI_AS
                  END   CodeDatatype,
            o.object_id,
            o.create_date ObjCreateDate,
            o.modify_date ObjModifyDate,
            ' + @IsSystemDB + ' IsSystemDB,
            ''' + @sNow + ''' ViewCreateDate
FROM [' + @DB + '].sys.objects o
LEFT JOIN [' + @DB + '].sys.columns c
      ON c.object_id = o.object_id
JOIN [' + @DB + '].sys.schemas s
      ON s.schema_id = o.schema_id
LEFT JOIN [' + @DB + '].sys.types t
      ON t.user_type_id = c.system_type_id
JOIN Admin.dbo.infraObjectType ot
      ON ot.xType = o.Type COLLATE SQL_Latin1_General_CP1_CI_AS
'

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

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

      END



IF @ShowSql = 1
      BEGIN
            Print @s
      END

Exec (@s)



Thursday, April 4, 2013

CreateInfraObjectType

This proc creates table infraObjectType which is is then JOINed with various queries where a list of object types are needed.  I find it much cleaner to put this into a table that can be referenced, rather than putting all the information into a verbose CASE statement.  The contents of this table come from BOL (book online).

This object is referenced by view vwDictCol which will be presented soon.


USE Admin
IF Object_ID('dbo.CreateInfraObjectType') Is Not Null
      DROP PROC dbo.CreateInfraObjectType
go

CREATE PROC dbo.CreateInfraObjectType
      @Debug bit = 0
AS
/*    DATE        AUTHOR            REMARKS
      4/4/13            PPaiva            Initial creation.
     
     
      DESCRIPTION
            Creates and populates table infraObjectType.
           
      USAGE
            CreateInfraObjectType 1
     
      DEBUG
            SELECT *
            FROM infraObjectType

*/
SET NOCOUNT ON

IF Object_ID('dbo.infraObjectType') Is Not Null
      DROP TABLE dbo.infraObjectType

CREATE TABLE dbo.infraObjectType (
      xType varchar(10) NOT NULL CONSTRAINT pk_infraObjectType PRIMARY KEY CLUSTERED,
      Type varchar(2) NOT NULL ,
      Description varchar(50) NOT NULL,
      DescShort varchar(50) NOT NULL
      )

INSERT INTO infraObjectType VALUES ('C', 'C', 'CHECK constraint', 'CheckCon')
INSERT INTO infraObjectType VALUES ('D', 'D', 'Default or DEFAULT constraint', 'DefaultCon')
INSERT INTO infraObjectType VALUES ('F', 'F', 'FOREIGN KEY constraint', 'FK')
INSERT INTO infraObjectType VALUES ('L', 'L', 'Log', 'Log')
INSERT INTO infraObjectType VALUES ('FN', 'FN', 'Scalar function', 'FunctionScalar')
INSERT INTO infraObjectType VALUES ('IF', 'IF', 'Inlined table-function', 'FunctionTable')
INSERT INTO infraObjectType VALUES ('P', 'P', 'Stored procedure', 'Proc')
INSERT INTO infraObjectType VALUES ('PK', 'K', 'PRIMARY KEY constraint', 'PK')
INSERT INTO infraObjectType VALUES ('(Not Used)', 'R', 'Rule', 'Rule')
INSERT INTO infraObjectType VALUES ('RF', 'FF', 'Replication filter stored procedure', 'RepFilterProc')
INSERT INTO infraObjectType VALUES ('S', 'S', 'System table', 'SysTable')
INSERT INTO infraObjectType VALUES ('TF', 'TF', 'Table function', 'FunctionTable')
INSERT INTO infraObjectType VALUES ('TR', 'TR', 'Trigger', 'Trigger')
INSERT INTO infraObjectType VALUES ('U', 'U', 'User table', 'Table')
INSERT INTO infraObjectType VALUES ('UQ', 'K', 'UNIQUE constraint', 'UniqCon')
INSERT INTO infraObjectType VALUES ('V', 'V', 'View', 'View')
INSERT INTO infraObjectType VALUES ('X', 'X', 'Extended stored procedure', 'ExProc')

IF @Debug = 1
      SELECT *
      FROM infraObjectType




Sunday, March 3, 2013

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)