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
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
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))
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.
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
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/
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:
Business Accounts
Answer for Membership
by: reb73Posted on 2009-02-09 at 15:53:47ID: 23595924
SELECT COUNT(*) FROM sysobjects where type = 'U' and name like 'ARMATTER%'
SELECT COUNT(*) FROM sysobjects where type = 'U' and name like 'GLBudget%'