[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

List of iterations of a table

Posted on 2009-02-09
8
Medium Priority
?
251 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
0
Comment
Question by:ONYX
  • 4
  • 3
8 Comments
 
LVL 25

Expert Comment

by:reb73
ID: 23595924
SELECT COUNT(*) FROM sysobjects where type = 'U' and name like 'ARMATTER%'
SELECT COUNT(*) FROM sysobjects where type = 'U' and name like 'GLBudget%'
0
 
LVL 41

Expert Comment

by:ralmada
ID: 23596673
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
 
LVL 41

Expert Comment

by:ralmada
ID: 23596679
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 

Author Comment

by:ONYX
ID: 23602553
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
 
LVL 41

Accepted Solution

by:
ralmada earned 200 total points
ID: 23603687

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
 

Author Closing Comment

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

Author Comment

by:ONYX
ID: 23603844
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
 
LVL 41

Expert Comment

by:ralmada
ID: 23603856
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question