SQL Code

Written by Administrator

Thursday, 01 January 2009 11:04

cpColumnRowCounts

I created this stored procedure to help me when I was working on a project to normalize a flat file with over 1000 columns. I used the procedure to count rows within the flat file table. Columns that had repeating groups or a very low distinct row count were columns that needed to be seperated into their own table. I like using cp, for custom procedure, to prefix my stored procedure names. This way they appear at the top of the list, before any system procedures and most other procedures, in SQL Studio Manager.

CREATE PROCEDURE cpColumnRowCounts
(
@pTable VARCHAR(255) = NULL,
@Append CHAR(1) = 'Y'
, @CurrentDB VARCHAR(256) = NULL
)
-- counts the distinct rows and total rows for every column in a table in inserts the info
-- into table ColumnRowCounts
-- See end of proc for a view that provides stats on the column counts.
AS
DECLARE @iNrOfColumns INT -- # of columns within table
, @strSQL VARCHAR(4000) --dynamic SQL statement
, @strColumn VARCHAR(255)
, @iThisColumnNr INT --current column #
, @CountAllRows INT
, @CountDistinctRows INT
, @strInsert VARCHAR(2000)
, @Table VARCHAR(256)

DECLARE DBcur CURSOR fast_forward FOR
SELECT * FROM master.dbo.sysdatabases WHERE dbid BETWEEN 5 AND 6

DECLARE Tblcur CURSOR fast_forward FOR
SELECT Name FROM dbo.sysobjects WHERE xtype = 'u'

PRINT 'Counting table ' + @pTable

IF ( @append = 'N' )
BEGIN
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[ColumnRowCounts]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[ColumnRowCounts]

CREATE TABLE [dbo].[ColumnRowCounts] (
[PKID] [int] IDENTITY (1, 1) NOT NULL ,
[TableName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ColumnName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CountAllRows] [int] NULL ,
[CountDistinctRows] [int] NULL
) ON [PRIMARY]

-- initialize variables
SELECT @iThisColumnNr=1, @strSQL='SELECT ', @strColumn='',
@strInsert='INSERT INTO ColumnRowCounts '

-- get the # of columns for table
SELECT @iNrOfColumns = MAX(colorder) FROM SYSCOLUMNS
WHERE OBJECT_NAME(ID) = @pTable

OPEN DBcur
OPEN TblCur

FETCH next FROM DBcur INTO @CurrentDB

WHILE @@Fetch_status = 0 BEGIN -- Database loop
IF @table IS NULL
FETCH next FROM TblCur INTO @table BEGIN
IF @@Fetch_status = 0
WHILE ( @iThisColumnNr <= @iNrOfColumns ) BEGIN
-- get first column
SELECT @strColumn = [name] FROM SYSCOLUMNS
WHERE OBJECT_NAME(ID) = @pTable AND
colorder = @iThisColumnNr
SELECT @strSQL = 'SELECT ' + QUOTENAME(@pTable, '''') + ' TableName, ''' +
QUOTENAME(@strColumn) + ''' AS ColumnName, COUNT( ' +QUOTENAME(@strColumn)+ ' ) AS CountAllRows, COUNT( DISTINCT ' +QUOTENAME(@strColumn) + ' ) AS CountDistinctRows FROM ' +@pTable
--PRINT ( @strSQL )
EXEC ( @strInsert+@strSQL )
SELECT @iThisColumnNr = @iThisColumnNr + 1
END
-- 'USE ' + @CurrentDB + '
EXEC ( @strInsert +' SELECT ''' +@pTable+ ''' TableName, ''All Rows'' AS ColumnName, COUNT(*), COUNT(*) FROM ' + @pTable )

END -- -- COLUMN LOOP
FETCH next FROM DBcur INTO @CurrentDB
END -- Tbl loop
FETCH next FROM TblCur INTO @Table
END-- DB LOOP

CLOSE DBcur
CLOSE TblCur
DEALLOCATE DBcur
DEALLOCATE TblCur

ALTER TABLE [dbo].[ColumnRowCounts] WITH NOCHECK ADD
CONSTRAINT [PK_ColumnRowCounts] PRIMARY KEY CLUSTERED
(
[PKID]
) ON [PRIMARY]

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Detect DTS global variable values

DTS was very hard to get current values for global variables. This vb script can be added to any DTS package and will output these values.

Option Explicit

Const Log_MsgBox = True
Const Log_EventLog = True
Const Log_TextFile = True

Const Log_TextFile_Name = "C:\Temp\GlobalVariableDiagnostics.txt"
Const Log_TextFile_Mode = 2 ' ForWriting = 2 or ForAppending = 8

Dim m_sBuffer

Function Main()

Dim oPkg
Dim oWshNetwork
Dim oGlobalVariable

Dim sName
Dim sTypeName
Dim sValue
Dim iIndex

iIndex = 1

Set oPkg = DTSGlobalVariables.Parent
Set oWshNetwork = CreateObject("WScript.Network")

m_sBuffer = m_sBuffer & "Package Information" & vbCrLf & _
"Package Name: " & vbTab & oPkg.Name & vbCrLf & _
"Log Date Time: " & vbTab & Now() & vbCrLf & _
"Current Computer Name: " & vbTab & oWshNetwork.ComputerName & vbCrLf & _
vbCrLf & _
"Current User Domain: " & vbTab & oWshNetwork.UserDomain & vbCrLf & _
"Current User Name: " & vbTab & oWshNetwork.UserName & vbCrLf & _
vbCrLf

m_sBuffer = m_sBuffer & "Global Variables: " & DTSGlobalVariables.Count & vbCrLf & vbCrLf

For Each oGlobalVariable in DTSGlobalVariables
sName = oGlobalVariable.Name
sTypeName = GetTypeName(oGlobalVariable)

On Error Resume Next
sValue = CStr(oGlobalVariable.Value)
If Err.Number <> 0 Then
sValue = "<Invalid CStr Value>"
End If
On Error GoTo 0

LogVariable iIndex, sName, sTypeName, sValue

iIndex = iIndex + 1
Next

Set oPkg = Nothing

If Log_MsgBox Then
MsgBox m_sBuffer, vbOk, "GlobalVariable Diagnostics"
End if

If Log_EventLog Then
Const EventTypeInfo = 4
Dim oWshShell

Set oWshShell = CreateObject("WScript.Shell")
oWshShell.LogEvent EventTypeInfo, m_sBuffer
Set oWshShell = Nothing
End If

If Log_TextFile Then
Dim oFSO
Dim oFile

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFile = oFSO.OpenTextFile(Log_TextFile_Name, Log_TextFile_Mode, True)
oFile.Write m_sBuffer & vbCrLf
oFile.Close
Set oFile = Nothing
Set oFSO = Nothing
End If

Main = DTSTaskExecResult_Success
End Function

Function GetTypeName(ByVal pGlobalVariable)
Dim sTypeName
Dim vType

vType = VarType(pGlobalVariable.Value)

Select Case vType
Case 0 : sTypeName = "Empty"
Case 1 : sTypeName = "Null"
Case 2 : sTypeName = "Integer (Small)"
Case 3 : sTypeName = "Integer"
Case 4 : sTypeName = "Real (4 Byte)"
Case 5 : sTypeName = "Real (8 Byte)"
Case 6 : sTypeName = "Currency"
Case 7 : sTypeName = "Date"
Case 8 : sTypeName = "String (BSTR)"
Case 9 : sTypeName = "Dispatch"
Case 10 : sTypeName = "10 Unknown (ERROR)"
Case 11 : sTypeName = "Boolean"
Case 12 : sTypeName = "12 Unknown (VARIANT)"
Case 14 : sTypeName = "Decimal"
Case 16 : sTypeName = "Integer (1 Byte)"
Case 17 : sTypeName = "Unsigned Int (1 Byte)"
Case 18 : sTypeName = "Unsigned Int (2 Byte)"
Case 19 : sTypeName = "Unsigned Int (4 Byte)"
Case 20 : sTypeName = "20 Unknown (I8)"
Case 21 : sTypeName = "21 Unknown (UI8)"
Case 22 : sTypeName = "Int"
Case 23 : sTypeName = "Unsigned Int"
Case 8204 : sTypeName = "Array (Blank Name)"
Case Else : sTypeName = CStr(vType) & " Unknown"
End Select

GetTypeName = sTypeName
End Function

Sub LogVariable(ByVal iIndex, ByVal sName, ByVal sTypeName, ByVal sValue)
Dim sBuffer

sBuffer = "Index: " & vbTab & CStr(iIndex) & vbCrLf & _
"Name: " & vbTab & sName & vbCrLf & _
"Type: " & vbTab & sTypeName & vbCrLf & _
"Value: " & vbTab & sValue & vbCrLf & vbCrLf

m_sBuffer = m_sBuffer & sBuffer
End Sub

sp_GrantExec

This code will quickly grant execute rights to either an object or all objects for a particular user or all users in that database. I prefixed the procedure with sp_ because I wanted to be able to store it once in the Master database and be able to run it from any database.

CREATE PROCEDURE sp_GrantExec
@User varchar(255) = 'ALL',
@Object varchar(255) = 'ALL',
@Debug int = 1 -- 1 to execute grans 0 to print
-- Procedure grants execute rights on stored procedures to users
AS
DECLARE
@SqlStr varchar(4000),
@ObjOwner varchar(255),
@uid int

Declare ObjCur Cursor forward_only for
select name, uid
from sysobjects
-- procs only
where xtype = 'p'
-- object variable is all then all objects else object that was passed in
and name like
case @object when 'all' then '%'
else @Object end

open ObjCur
fetch next from ObjCur into @Object, @uid

while @@Fetch_status = 0
begin
Select @SqlStr = 'GRANT EXECUTE ON [' + user_name(@uid) + '].[' + @Object + '] TO ' + quotename( @user )
fetch next from ObjCur into @Object, @uid
If @debug = 1
exec ( @sqlStr )
else
print @SqlStr
end

close ObjCur
Deallocate ObjCur

Automatically defrag indexes with sp_IndexDefrag

This procedure automates checking for index fragmentation and defrags when below a threshold parameter. Logical Scan Fragmentation and, to a lesser extent, Extent Scan Fragmentation values give the best indication of a table's fragmentation level. Both these values should be as close to zero as possible (although a value from 0% through 10% may be acceptable). It should be noted
that the Extent Scan Fragmentation value will be high if the index spans multiple files. Both
methods of reducing fragmentation can be used to reduce these values. I prefixed the procedure with sp_ because I wanted to be able to store it once in the Master database and be able to run it from any database.

CREATE PROCEDURE dbo.sp_IndexDefrag
(
@pTableName VARCHAR(256) = '%'
, @pMaxFrag DECIMAL = 15.0 -- maximum fragmentation threshhold
, @pDebug BIT = 0 -- 0 will execute dynamic sql 1 will print to screen
)
AS
-- Purpose: Check index fragmentation and defrag when below threshold param
DECLARE
@execstr VARCHAR (255),
@objectid INT,
@indexid INT,
@frag DECIMAL,
@Server varchar(100),
@DBName varchar(100),
@DBID int,
@Run int

-- Declare cursor
DECLARE tables CURSOR FOR
SELECT u.NAME + '.' + o.name Name
FROM SYSOBJECTS o join sysusers u on o.uid = u.uid
WHERE o.XTYPE = 'U'
AND o.NAME NOT LIKE 'DTPROPERTIES'
AND o.NAME LIKE @pTableName

-- Create the table

IF not exists ( select 1 from Admin.dbo.sysobjects where name = 'IndexFragList' and xtype = 'u' )
CREATE TABLE Admin.dbo.IndexFragList (
PID int identity(1,1) primary key,
Server varchar(100),
DBName varchar(100),
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFragPercent DECIMAL,
ExtentFrag DECIMAL,
EventRun INT,
InOrder VARCHAR(6),
EntryDt SMALLDATETIME default getdate() )

IF object_id('tempdb..#FragList') is not null
DROP TABLE #FragList

CREATE TABLE #FragList (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)

-- Initialize variables
select @Server = @@ServerName,
@DBName = db_name(),
@DBID = DB_ID(DB_name())

select @Run = isnull( max( EventRun ), 0) + 1 from Admin.dbo.IndexFragList

-- Open the cursor
OPEN tables

-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @pTableName

WHILE @@FETCH_STATUS = 0
BEGIN
set @ExecStr = ''
-- Do the showcontig of all indexes of the table
SELECT @ExecStr ='DBCC SHOWCONTIG (''' + @pTableName + ''') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS'

INSERT INTO #FragList Exec ( @ExecStr )

IF @pDebug = 1
PRINT @ExecStr

FETCH NEXT FROM tables INTO @pTableName
END

-- Close and deallocate the cursor
CLOSE tables
-- DEALLOCATE tables

-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #FragList
WHERE LogicalFrag >= @pMaxFrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Open the cursor
OPEN indexes

-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @pTableName, @objectid, @indexid, @frag

set @ExecStr = ''

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT '-- Executing DBCC INDEXDEFRAG (' + @DBName + ', ' + RTRIM(@ptablename) + ', ' + RTRIM(@indexid) + ') -- fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%'

SELECT @execstr = 'DBCC INDEXDEFRAG (' + cast( @DBID as varchar(10))+ ', ' + RTRIM(@objectid) + ', ' + RTRIM(@indexid) + ')'

IF @pDebug = 0
EXEC (@ExecStr)
ELSE
PRINT @ExecStr

FETCH NEXT
FROM indexes
INTO @pTableName, @objectid, @indexid, @frag
END

-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes

If @pDebug = 0
insert into Admin.dbo.IndexFragList( Server, DBName, ObjectName, ObjectId, IndexName, IndexId, Lvl, CountPages, CountRows, MinRecSize, MaxRecSize, AvgRecSize, ForRecCount, Extents, ExtentSwitches, AvgFreeBytes, AvgPageDensity, ScanDensity, BestCount, ActualCount, LogicalFragPercent, ExtentFrag, EventRun, InOrder )
select @Server, @DBName, ObjectName, ObjectId, IndexName, IndexId, Lvl, CountPages, CountRows, MinRecSize, MaxRecSize, AvgRecSize, ForRecCount, Extents, ExtentSwitches, AvgFreeBytes, AvgPageDensity, ScanDensity, BestCount, ActualCount, LogicalFrag, ExtentFrag, @Run, 'Before'
from #FragList
where indexID > 0 and
LogicalFrag >= @pMaxFrag

-- check levels of frag ever defragging
truncate table #FragList

-- Open the cursor
OPEN tables

-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @pTableName

WHILE @@FETCH_STATUS = 0
BEGIN
set @ExecStr = ''
-- Do the showcontig of all indexes of the table
SELECT @ExecStr ='DBCC SHOWCONTIG (''' + @pTableName + ''') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS'

INSERT INTO #FragList Exec ( @ExecStr )

FETCH NEXT FROM tables INTO @pTableName
END

If @pDebug = 0
insert into Admin.dbo.IndexFragList( Server, DBName, ObjectName, ObjectId, IndexName, IndexId, Lvl, CountPages, CountRows, MinRecSize, MaxRecSize, AvgRecSize, ForRecCount, Extents, ExtentSwitches, AvgFreeBytes, AvgPageDensity, ScanDensity, BestCount, ActualCount, LogicalFragPercent, ExtentFrag, EventRun, InOrder )
select @Server, @DBName, f.ObjectName, f.ObjectId, f.IndexName, f.IndexId, f.Lvl, f.CountPages, f.CountRows, f.MinRecSize, f.MaxRecSize,
f.AvgRecSize, f.ForRecCount, f.Extents, f.ExtentSwitches, f.AvgFreeBytes, f.AvgPageDensity, f.ScanDensity, f.BestCount,
f.ActualCount, f.LogicalFrag, f.ExtentFrag, @Run, 'After'
from #FragList F join Admin.dbo.IndexFragList i on f.ObjectID = i.ObjectID and f.IndexID = i.IndexID
where f.indexID > 0
and i.EventRun = @Run

-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables

-- Delete the temporary table
DROP TABLE #FragList

select * from Admin.dbo.IndexFragList where EventRun = @Run order by ObjectName, IndexId, EventRun, InOrder desc

END -- sp_IndexDefrag

Last Updated ( Sunday, 04 January 2009 13:43 )