Knowledgy Consulting, LLC
"Atlanta's Premier Business Intelligence and Data Warehouse Consultants!"
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
