Dynamic record counting
-
Before someone else complains (not you)
I'd still do it set based on dts :
Select col1, col2, coln ....
then using a recordset
for each oField in myrs.Fields()
insert into stats (tableName, ColName, CountT) values FieldTblName.value, oField.Name, oField.Value
next
Still have a set based query at then end of this to fetch the data and all work is done on the server.
-
No complaints at all, but certainly very interested in the suggested solutions. My own SQL programming skill is, compared to what I am reading, sub-standard so I am trying and learning at the same time. (I am a consultant cum business analyst!)
The approach by sql92 is definitely in the direction I am trying to develop a solution. I create a table that contains tablename, fieldname, count of non-null for all tables in a database and then appends into it the required detail. I don't know if the approach suggested by Remi will work as each table can have a different number of columns (up to over 200 in one case).
Something in the order like this (work in progress still!!:blush
Insert dbo.MgtCustomer_Master_FillFactors
(Table_Name ,
Field_Name ,
Field_Counter)
Select C.TABLE_NAME, C.COLUMN_NAME, Counter = Count(C.COLUMN_NAME)
from Information_Schema.COLUMNS C inner join
Information_Schema.TABLES T ON C.TABLE_NAME = T.TABLE_NAME
where T.TABLE_TYPE = 'BASE TABLE'
ORDER BY C.TABLE_NAME, C.COLUMN_NAME
-
Yes it does. If you read correctly I have a : for each field in myrs.Fields()
This will work if you have 0 to X nullable columns and it will also do only 1 table scan per table, instead of one per table per nullable column. Also it gives you the chance to reinsert the data into a normalized table with the for each.
-
Sorry Remi,
I read your suggestions a couple of times but I think I reached the limit of my SQL skills. I have not worked with record sets yet (cursors was already pushing the envelope for me!). I am not understanding the suggestions you are making and I have tried various permutations. You say
"Yes it does. If you read correctly I have a : for each field in myrs.Fields()"
I did not see the : you refer to in any of the code you have posted, so I am not sure where the reference is to. Also, if you could point me to some reading material on record sets and such I will try to resolve (and learn) this. I do not want you (or anybody else for this matter to solve all of my problems!
).Regards
Ron
-
Will be simpler for me to do the package... give me a few hours, I have something else to do first.
-
Hi Remi,
Sorry but supper interfered here.!
Thank you very much for your offer. I really appreciate it. I will dissect it also so I can unerstand what you are doing.
Ron
-
This is a multi part package
First execute this in QA :
IF Object_id('ListTableColumns_NotNulls') > 0
DROP FUNCTION ListTableColumns_NotNulls
GO
IF Object_id('TablesColumnsNulls') > 0
DROP TABLE TablesColumnsNulls
GO
IF Object_id('TablesColumnsNulls_Insert') > 0
DROP PROCEDURE TablesColumnsNulls_Insert
GO
CREATE FUNCTION dbo.ListTableColumns_NotNulls (@TableID as int)
RETURNS varchar(8000)
AS
BEGIN
Declare @Items as varchar(8000)
SET @Items = ''
SELECT
@Items = @Items + 'COUNT([' + C.Name + ']) [' + C.Name + '],'
FROM dbo.SysColumns C
WHERE C.id = @TableID
AND OBJECTPROPERTY(@TableID, 'IsTable') = 1
AND C.IsNullAble = 1
AND XType NOT IN (34,35)
ORDER BY C.Name
SET @Items = LEFT(@Items, ABS(DATALENGTH(@Items) - 1))
RETURN @Items
END
GO
CREATE TABLE TablesColumnsNulls
(
TableName sysname not null,
DateLog datetime not null default (GetDate()),
ColumnName sysname not null,
QtyNonNulls int not null,
primary key clustered (DateLog, TableName, ColumnName)
)
GO
CREATE PROCEDURE TablesColumnsNulls_Insert @TableName as sysname, @ColumnName as sysname, @QtyNonNulls as int
AS
SET NOCOUNT ON
Insert into dbo.TablesColumnsNulls (TableName, ColumnName, QtyNonNulls) values (@TableName, @ColumnName, @QtyNonNulls)
SET NOCOUNT OFF
GO
Then save this to a bas file and import it as a dts task :
'****************************************************************
'Microsoft SQL Server 2000
'Visual Basic file generated for DTS Package
'File Name: C:\Documents and Settings\remi\Bureau\ForEach.bas
'Package Name: Nouveau lot
'Package Description:
'Generated Date: 2005-07-04
'Generated Time: 16:36:27
'****************************************************************
Option Explicit
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Private Sub Main()
set goPackage = goPackageOld
goPackage.Name = "Nouveau lot"
goPackage.WriteCompletionStatusToNTEventLog = False
goPackage.FailOnError = False
goPackage.PackagePriorityClass = 2
goPackage.MaxConcurrentSteps = 4
goPackage.LineageOptions = 0
goPackage.UseTransaction = True
goPackage.TransactionIsolationLevel = 4096
goPackage.AutoCommitTransaction = True
goPackage.RepositoryMetadataOptions = 0
goPackage.UseOLEDBServiceComponents = True
goPackage.LogToSQLServer = False
goPackage.LogServerFlags = 0
goPackage.FailPackageOnLogFailure = False
goPackage.ExplicitGlobalVariables = False
goPackage.PackageType = 0
'---------------------------------------------------------------------------
' create package steps information
'---------------------------------------------------------------------------
Dim oStep as DTS.Step2
Dim oPrecConstraint as DTS.PrecedenceConstraint
'------------- a new step defined below
Set oStep = goPackage.Steps.New
oStep.Name = "DTSStep_DTSActiveScriptTask_1"
oStep.Description = "AX_Loop"
oStep.ExecutionStatus = 4
oStep.TaskName = "DTSTask_DTSActiveScriptTask_1"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False
goPackage.Steps.Add oStep
Set oStep = Nothing
'---------------------------------------------------------------------------
' create package tasks information
'---------------------------------------------------------------------------
'------------- call Task_Sub1 for task DTSTask_DTSActiveScriptTask_1 (AX_Loop)
Call Task_Sub1( goPackage 
'---------------------------------------------------------------------------
' Save or execute package
'---------------------------------------------------------------------------
goPackage.Execute
tracePackageError goPackage
goPackage.Uninitialize
'to save a package instead of executing it, comment out the executing package lines above and uncomment the saving package line
set goPackage = Nothing
set goPackageOld = Nothing
End Sub
'-----------------------------------------------------------------------------
' error reporting using step.GetExecutionErrorInfo after execution
'-----------------------------------------------------------------------------
Public Sub tracePackageError(oPackage As DTS.Package)
Dim ErrorCode As Long
Dim ErrorSource As String
Dim ErrorDescription As String
Dim ErrorHelpFile As String
Dim ErrorHelpContext As Long
Dim ErrorIDofInterfaceWithError As String
Dim i As Integer
For i = 1 To oPackage.Steps.Count
If oPackage.Steps(i).ExecutionResult = DTSStepExecResult_Failure Then
oPackage.Steps(i).GetExecutionErrorInfo ErrorCode, ErrorSource, ErrorDescription, _
ErrorHelpFile, ErrorHelpContext, ErrorIDofInterfaceWithError
MsgBox oPackage.Steps(i).Name & " failed" & vbCrLf & ErrorSource & vbCrLf & ErrorDescription
End If
Next i
End Sub
'------------- define Task_Sub1 for task DTSTask_DTSActiveScriptTask_1 (AX_Loop)
Public Sub Task_Sub1(ByVal goPackage As Object)
Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup
Dim oCustomTask1 As DTS.ActiveScriptTask
Set oTask = goPackage.Tasks.New("DTSActiveScriptTask")
oTask.Name = "DTSTask_DTSActiveScriptTask_1"
Set oCustomTask1 = oTask.CustomTask
oCustomTask1.Name = "DTSTask_DTSActiveScriptTask_1"
oCustomTask1.Description = "AX_Loop"
oCustomTask1.ActiveXScript = "Function Main()" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " Dim MyRsCols" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " Dim MyRsCount" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " Dim MyCn" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " Dim OColumns" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " Dim OTable" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " Dim oField" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " Dim sTable" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " Dim iCount" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " SET MyCn = CreateObject (""ADODB.Connection"")" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " SET MyRsCols = CreateObject (""ADODB.RecordSet"")" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " SET MyRsCount = CreateObject (""ADODB.RecordSet"")" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " MyCn.Open (""Provider=sqloledb;Data Source=SERVEUR4;Initial Catalog=Documentation;Integrated Security=SSPI;"")" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " MyRsCols.Open ""Select O.Name, NULLIF(dbo.ListTableColumns_NotNulls(O.id), '') as NullColumns from dbo.SysObjects O WHERE O.XType = 'U' order by O.Name"", MyCn, 1, 3" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " if not MyRsCols.EOF THEN" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " SET OTable = MyRsCols.Fields (""Name"")" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " SET OColumns = MyRsCols.Fields (""NullColumns"")" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " WHILE NOT MyRsCols.EOF" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " if not ISNULL(OColumns.value) THEN" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " MyRsCount.Open ""Select '"" & OTable.Value & ""', "" & OColumns.Value & "" FROM dbo."" & OTable.value, MyCn, 1, 3" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " sTable = """"" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " iCount = 0" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " For each oField in MyRsCount.Fields" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " if iCount = 0 then" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " sTable = MyRsCount.Fields(0).Value" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " else" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " MyCn.Execute ""EXEC dbo.TablesColumnsNulls_Insert '"" & sTable & ""', '"" & OField.Name & ""', "" & oField.Value" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " end if" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " iCount = iCount + 1" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " next" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " MyRsCount.Close" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " END IF" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " MyRsCols.MoveNext" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " WEND" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " END IF" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " MyRsCols.Close" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " MyCn.Close" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " SET MyRsCount = NOTHING" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " SET MyRsCols = NOTHING" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " SET MyCn = NOTHING" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & " Main = DTSTaskExecResult_Success" & vbCrLf
oCustomTask1.ActiveXScript = oCustomTask1.ActiveXScript & "End Function"
oCustomTask1.FunctionName = "Main"
oCustomTask1.ScriptLanguage = "VBScript"
oCustomTask1.AddGlobalVariables = True
goPackage.Tasks.Add oTask
Set oCustomTask1 = Nothing
Set oTask = Nothing
End Sub
execute the package and assuming you have no error :
Run this
Select * from dbo.TablesColumnsNulls
to see the number of non nulls rows per column. You'll be able to join these results with the previous count(*) you generated to get all the stats you need. You could also probabely merge the 2 scrips together. Please note that I have not included a fail-safe in case you have a table with too many nullable columns (script will fail somehow). The script can handle 200+ columns of 15 characters each. If you're able to bust that you seriously need to rethink your design
. -
WOW!
No wonder I didn't understand. I am going to be studying this and try to emulate bits and pieces into some other routines I am working on.
Thanks a million!
Ron
-
NP, it seems bigger than it really is. The package has only 1 step that runs in less than a sec on my pc (very few columns allow nulls and the tables aren't very big).
-
Well, after several attempts with Remi's code I still don't seem to be able to get it working. Somewhere in the code is a problem (I think it is in the line with the emoticon :
Call Task_Sub1( goPackage
I tried replacing it but to no avail.
I went back to see if I could solve it the slow and round-about way again, but T-SQL seems to have a few more surprises for me in store.
I have two code snippets the first (Attempt 1) gives me the error:
Server: Msg 137, Level 15, State 2, Line 9
Must declare the variable '@TableName'.
The second (Attempt 2) returns the right value! (11) Why?
/*--------------------------------This is attempt 1 ----------------------------*/
DECLARE @TableName sysname,
@FieldName sysname,
@FieldCounter BigInt
Set @TableName = 'BAF'
Set @FieldName = 'EMAIL'
UPDATE MgtCustomer_Master_FillFactors SET FieldCounter = (Select FieldCounter = Count(RTRIM(LTRIM(@FieldName))) From @TableName
WHERE LEN(RTRIM(LTRIM(@FieldName))) <> 0) From MgtCustomer_Master_FillFactors
Where MgtCustomer_Master_FillFactors.TableName = @TableName And MgtCustomer_Master_FillFactors.FieldName = @FieldName
/*--------------------------------This is attempt 2 ----------------------------*/
DECLARE @TableName sysname,
@FieldName sysname,
@FieldCounter BigInt
Set @TableName = 'BAF'
Set @FieldName = 'EMAIL'
UPDATE MgtCustomer_Master_FillFactors SET FieldCounter = (Select FieldCounter = Count(RTRIM(LTRIM(EMAIL))) From BAF
WHERE LEN(RTRIM(LTRIM(EMAIL))) <> 0) From MgtCustomer_Master_FillFactors
Where MgtCustomer_Master_FillFactors.TableName = @TableName And MgtCustomer_Master_FillFactors.FieldName = @FieldName
To me the two should be the same.
Help (again please)!
-
Yes replace the emoticon with ")".
I don't remember that last part beeing part of my code... what's that supposed to do?
-
Hi Remi,
Thanks. The last part is not part of your code. Just me trying again to make progress my way (and failing).
R
-
You can't do select * from @TableName. This would be dynamic sql, or requires a table variable.
-
Also avoid cursor....
, more set oriented declare @tablename sysname
set @tablename = ''
while (1=1)
begin
select @tablename=min(name)
from sysobjects
where type = 'U' and @tablename<name
if @tablename is null break
exec ('Insert dbo.MgtTable_Records (Table_Name ,No_Records) Select '''
+ @tablename +
''',Count(*) As Counter From '
+ @tablename )
end
-
Hi Jorge,
I like the approach, but it didn't work right out of the box, so I adapted it a bit. After reading the article that Remi pointed me to I decided to change my approach so I am investigating sets as well now. I don't think that the sets based approach is as quick though but I am not too worried about speed at this stage and will optimise once I know everything is working. I am still struggling with the counting on non-null records though as the DTS approach from Remi just won't compile. Here is my 'adjusted' bit of code based on what you sent me. It might help others out there
DECLARE @objName SYSNAME
SET @objName = ''
WHILE @objName IS NOT NULL
BEGIN
SELECT @objName = MIN(NAME)
FROM sysobjects
WHERE type = 'U' and NAME > @objName
IF @objName IS NOT null
BEGIN
EXEC ('Insert dbo.MgtTable_Records (TableName ,RecordCounter) Select '''
+ @objName +
''',Count(*) As Counter From '
+ @objName )
END
END
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply