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.

Monday, December 30, 2013

vwRowCount

USE Admin
GO


IF object_id('dbo.vwRowCount') Is Not Null
      DROP VIEW dbo.vwRowCount
go

Create View dbo.vwRowCount
AS
/*    DATE        AUTHOR            REMARKS
      1/16/12           PPaiva            Initial creation.

      SELECT *
      FROM vwRowCount
      ORDER BY Rows desc

      SELECT *
      FROM vwRowCount
      ORDER BY TableName

      DEPENDENCY
            dbo.udfGetColumnList()

*/
WITH cteCols
AS    (     SELECT object_id, Count(*) NumCols
            FROM sys.columns
            GROUP BY Object_ID
            )
SELECT  Convert(varchar, ServerProperty('ServerName')) as Server,
            DB_NAME() AS DB,
            SCHEMA_NAME(t.schema_id) AS SchemaName,
            t.name AS TableName,
                  dbo.udfFormatInteger(i.Rows) RowsFMT,
                  col.NumCols,
                  dbo.udfGetColumnList(t.name, 0) ColList,
                  t.create_date CreateDate,
                  t.modify_date ModifyDate,
                  'SELECT * FROM ' + DB_NAME() + '.[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' SqlSELECT,
                  dbo.udfGetColumnList(t.name, 1) ColListBRACKETS,
                  'SELECT TOP 500 * FROM ' + DB_Name() + '.[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' SqlSELECT500,
            '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' AS FullTableName,
            i.Rows,
            t.object_id
FROM sys.tables t
JOIN sys.sysindexes i
      ON  t.object_id = i.id
      AND i.indid < 2         -- 0 = heap, 1 = has clustered index, >1 is non-clustered index
JOIN cteCols col
      ON col.object_id = t.object_id 
  AND t.name <> 'sysdiagrams'






Thursday, November 14, 2013

To gain control of a SQL instance if the user is an Admin on the machine but not in SQL Server

1.        Stop SQL Server service if it is running. 
2.        Set the start up parameter to “-m”.  Do this in the General tab of the service.  Remember any pre-existing start-up parameters so you can restore to the original configuration later.
  



3.        Start service.

4.       At a command prompt:

sqlcmd
create login Paul WITH PASSORD = 'hello!1' 
go

sp_addsrvrolemember 'Paul', 'sysadmin'
go

Note:  SQL has default strong password policy

5.        Put back to normal user mode
remove -m

stop service
start service

Tuesday, November 12, 2013

DropObjectsInSchema

This proc comes in handy when you have to purge all the objects in your database, so you can then run a creation script.  It will tell you how many objects it dropped.


Use Admin
GO
IF Object_ID('dbo.DropObjectsInSchema') Is Not Null
      DROP PROC dbo.DropObjectsInSchema
go

CREATE PROC dbo.DropObjectsInSchema
      @SchemaName varchar(50),
      @ObjectType varchar(50),
      @Debug bit = 0
AS
/*    DATE              AUTHOR            REMARKS
      11/11/13          PPaiva            Initial creation.

      DESCRIPTION
            Drops all objects of type @ObjectType in a given schema.

      USAGE
            Exec DropObjectsInSchema 'dbo', 'FK', 1
            Exec DropObjectsInSchema 'dbo', 'Table', 1
            Exec DropObjectsInSchema 'dbo', 'View', 1
            Exec DropObjectsInSchema 'dbo', 'Proc', 1
            Exec DropObjectsInSchema 'dbo', 'Function', 1

            DropObjectsInSchema 'dbo', 'FK', 1
            DropObjectsInSchema 'dbo', 'FK'

            DropObjectsInSchema 'dbo', 'Table', 1
            DropObjectsInSchema 'dbo', 'Table'

            DropObjectsInSchema 'dbo', 'View', 1
            DropObjectsInSchema 'dbo', 'View'

            DropObjectsInSchema 'dbo', 'Proc', 1
            DropObjectsInSchema 'dbo', 'Proc'

            DropObjectsInSchema 'dbo', 'Function', 1
            DropObjectsInSchema 'dbo', 'Function'


      DEBUG
            SELECT *
            FROM sys.schemas
            WHERE Name Not Like 'db_%'

            SELECT type_desc, Count(*) Qty
            FROM sys.objects
            GROUP BY type_desc
            ORDER BY 1

            SELECT *
            FROM sys.objects
            WHERE type_desc <> 'system_table'

            SELECT *
            FROM vwDictCol

*/
SET NOCOUNT ON

DECLARE @Name varchar(100),
            @MaxName varchar(100),
            @Sql varchar(1000),
            @Num int,
            @Msg varchar(100)

IF @ObjectType Not In ( 'FK', 'Table', 'View', 'Proc', 'Function')
      BEGIN
            SET @Msg = '@ObjectType must be one of these:  FK, Table, View, Proc, Function'
            Print @Msg
            RaisError (@Msg, 16, 1)

            RETURN
      END

CREATE TABLE #t(
      ID int NOT NULL IDENTITY(1, 1),
      Name sysname,
      Sql varchar(2000)
      )

IF @ObjectType = 'FK'
      INSERT INTO #t (Name, Sql)
            SELECT  f.Name,
                        'ALTER TABLE [' + SCHEMA_NAME(o.schema_id) + '].[' + object_Name(f.parent_object_id) + ']'
                              + ' DROP CONSTRAINT ['
                              + f.name + ']' Sql                       
            FROM sys.foreign_keys f
            JOIN sys.objects o     
                  ON  o.object_id = f.parent_object_id
            WHERE SCHEMA_NAME(o.schema_id) = @SchemaName
            ORDER BY o.Name

ELSE IF @ObjectType = 'Table'
      INSERT INTO #t (Name, Sql)
            SELECT  o.Name,
                        'DROP TABLE [' + SCHEMA_NAME(o.schema_id) + '].[' + o.name + ']' Sql                       
            FROM sys.objects o     
            WHERE type_desc = 'user_table'
              AND SCHEMA_NAME(o.schema_id) = @SchemaName
              AND o.Name Not In ('sysdiagrams', 'Flag', 'logEvent', 'History')
              AND o.Name Not Like 'rsa%'
            ORDER BY o.Name

ELSE IF @ObjectType = 'View'
      INSERT INTO #t (Name, Sql)
            SELECT  o.Name,
                        'DROP VIEW [' + SCHEMA_NAME(o.schema_id) + '].[' + o.name + ']' Sql                       
            FROM sys.objects o     
            WHERE type_desc = 'View'
              AND SCHEMA_NAME(o.schema_id) = @SchemaName
              AND o.Name Not In ('vwDictCol', 'vwIndexRaw', 'vwRowCount', 'vwTrigger')
            ORDER BY o.Name

ELSE IF @ObjectType = 'Proc'
      INSERT INTO #t (Name, Sql)
            SELECT  o.Name,
                        'DROP PROC [' + SCHEMA_NAME(o.schema_id) + '].[' + o.name + ']' Sql                       
            FROM sys.objects o     
            WHERE type_desc Like '%procedure%'
              AND SCHEMA_NAME(o.schema_id) = @SchemaName
              AND o.Name Not Like 'sp%diagram%'
              AND o.name Not In ( 'DropObjectsInSchema', 'WriteLogEvent')
            ORDER BY o.Name

ELSE IF @ObjectType = 'Function'
      INSERT INTO #t (Name, Sql)
            SELECT  o.Name,
                        'DROP FUNCTION [' + SCHEMA_NAME(o.schema_id) + '].[' + o.name + ']' Sql                    
            FROM sys.objects o     
            WHERE type_desc Like '%function%'
              AND SCHEMA_NAME(o.schema_id) = @SchemaName
              AND o.Name Not In ('fn_diagramobjects', 'udfFormatInteger', 'udfGetColumnList', 'udfGetEntityRoleId', 'udfFormatTimeWithColons')
            ORDER BY o.Name


SELECT  @MaxName = Max(Name),
            @Name = '',
            @Num = Count(*)
FROM #t

SET @Msg = @ObjectType + 's found in schema ' + @SchemaName + ':  ' + Convert(varchar, @Num)

Print ''
Print @Msg

WHILE @Name < @MaxName
      BEGIN
            -- Get next Name
            SELECT @Name = Min(Name)
            FROM #t
            WHERE Name > @Name

            -- Get other attibutes
            SELECT @Sql = Sql
            FROM #t
            WHERE Name = @Name

            IF @Debug = 1
                  Print @Sql
            ELSE
                  Exec (@Sql)
      END







Friday, October 4, 2013

This object combines info from vwTable and vwIndexPhysicalStats.  

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

USE Admin
IF Object_ID('dbo.vwIndexPhysicalStatsTableSize') Is Not Null
      DROP VIEW dbo.vwIndexPhysicalStatsTableSize
go

create VIEW dbo.vwIndexPhysicalStatsTableSize
AS
/*    DATE        AUTHOR            REMARKS
      4/6/13            PPaiva            Initial creation.
     
      SELECT *
      FROM vwIndexPhysicalStatsTableSize
      WHERE DB = 'MyDB'
        AND PercFrag > 20

      SELECT *
      FROM vwIndexPhysicalStatsTableSize
      WHERE DB = 'MyDB'
      ORDER BY 1, 2, 3, 4, 5

      -- Cannot rebuild
      SELECT *
      FROM vwIndexPhysicalStatsTableSize
      WHERE DB = 'MyDB'
        AND (AllowPgLocks = 0 OR IsDisabled = 1)

     
*/
SELECT  s.Server,
            s.DB,
            s.SchemaName,
            s.ObjName,
            s.IndexName,
            s.PercFrag,
            s.TotalFrags,
            s.PagesPerFrag,
            dbo.udfFormatInteger(s.NumPages) NumPagesFMT,

            t.RowsFmt,
            t.DataMB,
            t.IndexMB IndexMBTotal,
            t.FileGroup,
            t.NumPartitions,
            t.NumCols,
           
            s.PartNum,
            s.IndexType,
            s.AllocDesc,
            s.IsUniq,
            s.IsPK,
            s.IsUniqCon,
            s.IndexDepth,
            s.IndexLevel,
            s.AllowPgLocks,
            s.IsDisabled,
            s.NumPages,
            t.Rows,
            s.object_id,
            s.database_id,
            s.ViewCreateDate
FROM vwIndexPhysicalStats s
LEFT JOIN vwTable t
      ON t.DB = s.DB
      AND t.object_id = s.object_id




Wednesday, September 11, 2013

This is my favorite view on the blog.  It contains is an instance-wide list of tables, complete with table size in number of rows and number of bytes.

It does have many dependencies, all of which can be found on this blog.
                vwPartitionRaw
                vwDictCol
                SpaceUsed
                vwObjectFileGroup
         udfPadLeft()
                udfFormatInteger()

This object is referenced by various other objects in this blog.

USE Admin
IF Object_ID('dbo.vwTable') Is Not Null
      DROP VIEW dbo.vwTable
go

CREATE VIEW [dbo].[vwTable]
AS
/*    DATE        AUTHOR            REMARKS
      9/11/13           PPaiva            Initial creation.


      SELECT *
      FROM Admin.dbo.vwTable
      ORDER BY Rows desc

      SELECT TOP 100 *
      FROM Admin.dbo.vwTable
      WHERE DB = 'MyDB'
      ORDER BY 1, 2, 3, 4

*/

-- Get rows/table
WITH cteTP
AS (  SELECT Server, DB, SchemaName, Tablename, object_id, Sum(Rows) Rows, Count(*) NumPartitions
            FROM dbo.vwPartitionRaw
            GROUP BY  Server, DB, Tablename, SchemaName, object_id
      ),

-- Tables with number of columns
cteDC
AS (  SELECT  Server,
                        DB,
                        SchemaName,
                        ObjName TableName,
                        IsSystemDB,
                        ObjCreateDate,
                        ObjModifyDate,
                        object_id,
                        Count(*) NumCols,
                        Min(ViewCreateDate) ViewCreateDate
            FROM dbo.vwDictCol
            WHERE ObjType = 'Table'
            GROUP BY Server,
                        DB,
                        SchemaName,
                        ObjName,
                        IsSystemDB,
                        ObjCreateDate,
                        ObjModifyDate,
                        object_id
      ),
-- Calc BytesPerRow          
cteRowSize
AS (  SELECT Server, DB, object_id, Sum(Length) as BytesPerRow
            FROM dbo.vwDictCol
            WHERE ObjType = 'table'
            GROUP BY Server, DB, object_id
      ),

-- Contains
cteSpaceUsed
AS (  SELECT Server, DB, object_id, DataMB, IndexMB, TotalMB, ReservedMB, PopDate SpaceUsedPopDate
                  --SizeMB, Reserved, data, index_size
            FROM dbo.SpaceUsed
      ),
     
cteFileGroup
AS    (     SELECT Server, DB, ObjectID, FileGroup
            FROM dbo.vwObjectFileGroup
      )
-- Final query         
SELECT  tp.Server,
            tp.DB,
            fg.FileGroup,
            tp.SchemaName,
            tp.TableName,          
            Admin.dbo.udfPadLeft(
                                    Admin.dbo.udfFormatInteger(tp.Rows),
                                    15,
                                    ' '
                                    ) RowsFmt,
            Admin.dbo.udfFormatInteger(su.TotalMB)TotalMBFmt,
            tp.NumPartitions,
            dc.NumCols,
            dc.ObjCreateDate,
            dc.ObjModifyDate,
            su.DataMB,
            su.IndexMB,
            su.ReservedMB,
            tp.Rows,
            su.TotalMB,
            rs.BytesPerRow,
            Convert(decimal(10, 1), (rs.BytesPerRow * tp.Rows / 1000000000.0)) DataGBProjectedMax,
            dc.object_id,
            IsSystemDB,
            'Use [' + tp.db + '];  Exec sp_help ''[' + tp.SchemaName + '].[' + tp.TableName + ']''' spHelp,
            'Use [' + tp.db + '];  Exec sp_helpindex ''[' + tp.SchemaName + '].[' + tp.TableName + ']''' spHelpIndex,
            'TRUNCATE TABLE [' + tp.DB + '].[' + tp.SchemaName + '].[' + tp.TableName + ']' TruncateScript,
            'DROP TABLE [' + tp.DB + '].[' + tp.SchemaName + '].[' + tp.TableName + ']' DropScript,
            su.SpaceUsedPopDate,
            dc.ViewCreateDate vwDictColCreateDate
FROM cteTP tp
LEFT JOIN cteDC dc
      ON  tp.Server = dc.Server
      AND tp.DB = dc.DB
      AND tp.object_id = dc.object_id           -- object_id is unique only within a database
LEFT JOIN cteRowSize rs
      ON  rs.Server = tp.Server
      AND rs.DB = tp.DB
      AND rs.object_id = tp.object_id
LEFT JOIN cteSpaceUsed su
      ON  su.Server = tp.Server
      AND su.DB = tp.DB
      AND su.object_id = tp.object_id
LEFT JOIN cteFileGroup fg
      ON  fg.ObjectID = dc.Object_ID
      AND tp.Server = fg.Server
      AND tp.DB = fg.DB