We help IT Professionals succeed at work.

Compare and list imported table names to SQL table names with Row count

I have a project beyond my SQL skill level I need help with.

I have some number of temporary tables I import into SQL Server via Server Management Studio  where the table names begin with "temp".
I execute a script that transfers the tempTable records to SQL tables of the same name - less the "temp". For example, tempClient inserts records into dbo.Client.
Once that script executes, I'd like to verify records transferred correctly by running another script listing only those SQL tables and their row count.
The following script lists only the tempTables and row count but that's not what I'm looking for. I need the SQL tables and row count where the SQL table names are the same name as the tempTable names. Otherwise, I have to review hundreds of tables in the dB to locate and verify the row count of a few.

SELECT
t.name AS TABLENAME, p.[Rows]
FROM sys.tables t
    INNER JOIN sys.partitions p ON t.object_id = p.object_id
WHERE p.[rows]>0 AND t.name LIKE '%temp%'
GROUP BY
t.name, p.[rows]
ORDER BY t.name
GO
Comment
Watch Question

Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:

There are a lot of different ways to compare tables. A simple approach is:

with cteTbl as 
( select TableName=t.name, Rows=p.Rows
  from systables t join sys.partitions p on t.object_id = p.object_id )
select tmp.TableName, TmpRows=tmp.Rows, TblRows=notmp.Rows
from (select * from cteTbl where tablename  like '%temp%' and rows > 0) tmp
left join (select * from cteTbl where tablename not like '%temp%') notmp
on lower(tmp.tablename) = 'tmp' + lower(notmp.tablename)


Author

Commented:
This only returns error

Msg 208, Level 16, State 1, Line 5
Invalid object name 'systables'.

Author

Commented:
Actually, in looking at it further if I place a period in systables to sys.tables it does execute, however, it's returning the names and row count of the "temp" tables, not the sql tables with the same name (minus the "temp".
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
Sorry for the typo - yes, there is a dot missing.

To get the non-temp table name, just put that into the outermost SELECT:
with cteTbl as
( select TableName=t.name, Rows=p.Rows
  from systables t join sys.partitions p on t.object_id = p.object_id )
select notmp.TableName, TmpRows=tmp.Rows, TblRows=notmp.Rows
from (select * from cteTbl where tablename  like '%temp%' and rows > 0) tmp
left join (select * from cteTbl where tablename not like '%temp%') notmp
on lower(tmp.tablename) = 'tmp' + lower(notmp.tablename)

Open in new window

Author

Commented:
This code returns:
NULL      1304      NULL
NULL      834      NULL
NULL      2201      NULL
NULL      2201      NULL
NULL      2201      NULL
NULL      1473      NULL
NULL      1304      NULL
NULL      1273      NULL
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
That is "correct" (in being the result and unwanted), but you really should be able to find the issues yourself - very obvious ...

But first, your like '%temp%' is too unspecific. You should only look for 'temp%'. Correcting that and that my query tried to find 'tmp' instead of 'temp':
with cteTbl as
( select TableName=t.name, Rows=p.Rows
  from systables t join sys.partitions p on t.object_id = p.object_id )
select TableName=coalesce(notmp.TableName, tmp.TableName), TmpRows=tmp.Rows, TblRows=notmp.Rows
from (select * from cteTbl where tablename  like 'temp%' and rows > 0) tmp
left join (select * from cteTbl where tablename not like 'temp%') notmp
on lower(tmp.tablename) = 'temp' + lower(notmp.tablename)

Open in new window

I've also added the display of temp table names in case there is no corresponding non-temp table.

Author

Commented:
Always obvious to the skilled! lol

Works perfectly except could you show me where to insert a GROUP BY clause? As it is, I have several listings with the same table name instead of one.
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
Your original query didn't work regarding the grouping at all, that is why I skipped that.
The only reason I can see why you get more than one row per table is because you use partitions. The obvious way to cope with that is the following query, but I'm not certain the row count is correct.
with cteTbl as
( select TableName=t.name, Rows=sum(p.Rows)
  from systables t join sys.partitions p on t.object_id = p.object_id
  group by t.name )
select TableName=coalesce(notmp.TableName, tmp.TableName), TmpRows=tmp.Rows, TblRows=notmp.Rows
from (select * from cteTbl where tablename  like 'temp%' and rows > 0) tmp
left join (select * from cteTbl where tablename not like 'temp%') notmp
on lower(tmp.tablename) = 'temp' + lower(notmp.tablename)

Open in new window

Author

Commented:
I believe you are right and the row counts are no longer correct. Any suggestion on how get this right?
Here's what is returned:
    NAME                   tmpRows            tblRows
CaseMgmt                     1304                20864
CaseMgmtNotes                834                  5004
tempClient                      2201                  NULL
CliMas                              2201                  6603
CliPers                              2201                19809
tempEmerContacts      1473                    NULL
Meals                              1304                  9128
MealSchedule     1273        14003
"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
As suspected. added row counts are multiple of the real ones.
Replace SUM with MAX, that should work.

Author

Commented:
That solves it! Thanks you so much for figuring it out for me.