Dynamic record counting
-
I hope somebody can help me with a little bit of code I am trying to write. This code is to go through all the table in a database and count the number of records in each table. The name of the table that is counted and the result are to be written to a seperate data table I created for this purpose. The table's name is MgtTable_Records with two fields: Table_name and No_records.
I am trying to use a cursor to create a list of the table names in a database (this bit works) and then use the list of names so created to loop through all the tables, count the records and write the result to the destination table. I get the following consistent error:
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '1'.
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'AAMIGRATE'.
This is repeated for every table in the cursor. It sees the table name as a column name. I tried CAST and a different variable declared as a NVARCHAR but the result is the same.
What am I forgetting or what am I doing wrong?
/*------------ The bit I am trying to develop ----------------*/
DECLARE tables_cursor CURSOR
FOR
SELECT name FROM sysobjects WHERE type = 'U' Order By name ASC
OPEN tables_cursor
DECLARE @tablename sysname
FETCH NEXT FROM tables_cursor INTO @tablename
WHILE (@@FETCH_STATUS = 0)
BEGIN
EXEC ('Insert dbo.MgtTable_Records (Table_Name ,No_Records)
Select ' + @tablename + ',Count(*) As Counter From ' + @tablename )
FETCH NEXT FROM tables_cursor INTO @tablename
END
DEALLOCATE tables_cursor
-
First of all :
Select ''' + @tablename + ''',Count(*) As Counter From ' + @tablename
Second of all :
Select O.name, MAX(I.RowCnt) as RowCnt from dbo.SysObjects O inner join dbo.SysIndexes I on O.id = I.id and O.XType = 'U' and I.Indid < 2 group by O.Name order by O.Name
Note that this offers only a estimate of the row count (accurate most of the time, but not garanteed).
-
You need to include the single quotes for the tablename data in the insert like this
EXEC ('Insert dbo.MgtTable_Records (Table_Name ,No_Records)
Select ''' + @tablename + ''',Count(*) As Counter From ' + @tablename )
Far away is close at hand in the images of elsewhere.
Anon. -
Hi David/Remi,
The additional quotes did the trick! Thanks very much
Ron
-
Did you try the set based approach?
-
Bet not Remi
Like the solution though
But aren't you teaching bad habits, using system tables, tut tut
No need to castigate the dynamic sql though, been there, done that
Far away is close at hand in the images of elsewhere.
Anon. -
Sure show me how to do this with the information_Schema views
.There are exceptions to the dynamic sql rule
. -
Thanks David/Remi,
No, I didn't try the set approach as accuracy is required. This particular script is the first of a few more.
I am trying to create dynamic scripts for a data quality task I have to do for one of my clients
Next is to count all non-null fields in a data table... Dynamically...!
Regards
Ron
-
Count the number of non null fields per table?
or count the number of rows where the data is not null?
-
for questions A :
Select O.Name, C.name from dbo.SysObjects O inner join dbo.SysColumns C on O.id = C.id and O.XType = 'U' and C.IsNullable = 0 order by O.Name, C.Name
-
Hi Remi,
Clarification required, I did not express myself clearly there! I need to know the so-called field fill factor. I therefore need to know per field the number of non-null occurences. This I need to report on for data quality purposes. One of my customers has a lot of contact data that is incomplete and they would like to know on a monthly basis how the data is being updated. Obviously the field fill factor is only one aspect of data quality being checked.
Hope that I clarified that one
Ron
-
Before someone else complains :
Select C.TABLE_NAME, C.COLUMN_NAME from Information_Schema.COLUMNS C inner join Information_Schema.TABLES T ON C.TABLE_NAME = T.TABLE_NAME where C.IS_NULLABLE = 'No' AND T.TABLE_TYPE = 'BASE TABLE' ORDER BY C.TABLE_NAME, C.COLUMN_NAME
-
Just change the 'No' to yes in the previous query to get the nullable columns (all others will have 100% so no need to count them).
Then the query would look something like this :
Select 'TableName' as TblName, count(NullCol1) as Col1, count(NullCol2) as Col2... from dbo.TableName
-
Before someone else complains Not me Remi
I wasn't complaining, honest 
Ronald, depends on how you want the results but I bet that there would be too many columns for one query but you could....
Generate dynamic sql to create a global temp table containing an int column for each column name using Remi's code (either one!
)Loop through each column name and update temp table accordingly
Far away is close at hand in the images of elsewhere.
Anon. -
U might use this as start for a dynamic sql
create table a (c1 int)
insert into a select 1
union all select 2
union all select 3
union all select 4
union all select null
go
select count(*) as rowcnt
, count(c1) as non_null
, count(*)-count(c1) as null_markers
, (count(*)*1.0-count(c1))/count(c1) percentage_null_markers
from a
drop table a
rowcnt non_null null_markers percentage_null_markers
----------- ----------- ------------ ---------------------------
5 4 1 .250000000000
(1 row(s) affected)
Warning: Null value is eliminated by an aggregate or other SET operation.
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply