We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

List of iterations of a table

ONYX
ONYX asked
on
Medium Priority
267 Views
Last Modified: 2012-06-21
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
Comment
Watch Question

Commented:
SELECT COUNT(*) FROM sysobjects where type = 'U' and name like 'ARMATTER%'
SELECT COUNT(*) FROM sysobjects where type = 'U' and name like 'GLBudget%'

Commented:
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

Commented:
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))

Author

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.
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Fantastic! Thank you so much. That's exactly what I was looking for and your explanation was exceptional. I do appreciate it.

Author

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!

Commented:
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

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.