List of iterations of a table

We have jobs that run that create a separate table every time they're run for a snapshot in time type of report. What I want to know, is how can i get a list of all these tables. For example: There might a table called ARMatter. The next time the job is run to capture the snapshot, it's called ARMatter1, then ARMatter2, ARMatter3 and so on. There could be 100 versions of such a table. And, we have many other tables like this (for example; GLBudget, GLBudget1, GLBudget2 etc). So I know I can get a list of tables from sysobjects, but I want to know how many tables exist for ARMatter and GLBudget. is there a query I can run against sysobjects that will tell me there there is a table called ARMatter and there is a count of 35 instances like it (ie. ARMatter35)...that way I know that the scheduled job has been 35 times. So the query results would be like:

Task Name   Number of Runs
ARMatter      35
GLBudget     14
etc
etc
ONYXAsked:
Who is Participating?
 
ralmadaConnect With a Mentor Commented:

Sure, basically the query is listing all records from sysobjects, containing table objects (type = 'U') grouped by name. But the grouping is done on the part of the name that do not have any numbers. To eliminate the numbers I'm using:
a) the LEFT function. More info here:
http://msdn.microsoft.com/en-us/library/ms177601(SQL.90).aspx 
b) I'm using a CASE statement to check whether the name of the table has any number or not. If it does then numbers are deleted. If don't it will consider the full name as is.
c) To check if there is any number in the name of the table I'm using the PATINDEX function and the pattern used is (%[0-9]%). It basically returns the position of the first number occurrence within the table name. More info on the patindex function here:
 http://msdn.microsoft.com/en-us/library/ms188395(SQL.90).aspx
So for instance, if you have the following records in sysobjects
table
table1
table2
table3

it will list it as
name          Count
table            4

What you can also do is sorting it like this:

select left(name, (case when patindex('%[0-9]%', name) = 0 then len(name) else patindex('%[0-9]%', name)-1 end)) as tablename, count(*) as counts from sysobjects
where type = 'U'
group by left(name, (case when patindex('%[0-9]%', name) = 0 then len(name) else patindex('%[0-9]%', name)-1 end)) 
order by 2 DESC

Open in new window

0
 
reb73Commented:
SELECT COUNT(*) FROM sysobjects where type = 'U' and name like 'ARMATTER%'
SELECT COUNT(*) FROM sysobjects where type = 'U' and name like 'GLBudget%'
0
 
ralmadaCommented:
Hello ONYX,

you can try this:

select left(name, (case when patindex('%[0-9]%', name) = 0 then len(name) else patindex('%[0-9]%', name)-1 end)), count(*) from sysobjects
group by left(name, (case when patindex('%[0-9]%', name) = 0 then len(name) else patindex('%[0-9]%', name)-1 end))


Regards,

ralmada
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
ralmadaCommented:
missed the filter for table objects

select left(name, (case when patindex('%[0-9]%', name) = 0 then len(name) else patindex('%[0-9]%', name)-1 end)), count(*) from sysobjects
where type = 'U'
group by left(name, (case when patindex('%[0-9]%', name) = 0 then len(name) else patindex('%[0-9]%', name)-1 end))
0
 
ONYXAuthor Commented:
Thanks for your response reb73, however, I think ralmada is on the right track. So my question to ralmada is can you explain what your query is doing? I believe this is what I'm looking for. It looks as though it's looking for a pattern of some sort. Is that correct? If you would be so kind as to write out an explanation of the query, I would be most grateful. Thank you so much.
0
 
ONYXAuthor Commented:
Fantastic! Thank you so much. That's exactly what I was looking for and your explanation was exceptional. I do appreciate it.
0
 
ONYXAuthor Commented:
Ralmada not only delivered the solution I was looking for, he also explained the query very well, in simple terms that I could understand! Well done!
0
 
ralmadaCommented:
Now if you want to filter it by just the two tables mentioned above, then you can do something like this
select left(name, (case when patindex('%[0-9]%', name) = 0 then len(name) else patindex('%[0-9]%', name)-1 end)) as tablename, count(*) as counts from sysobjects
where type = 'U' and (name like 'ARMatter%' or name like 'GLBudget%')
group by left(name, (case when patindex('%[0-9]%', name) = 0 then len(name) else patindex('%[0-9]%', name)-1 end)) 
order by 2 DESC

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.