Query with changing table names

We have a web tracking program that came with our firewall that writes it's
data to a MSDE database.
Unfortunately it writes each day's data to a different table. It names them
connection_events_2005_10_20 then connection_events_2005_10_21 etc... I need
to create a report by the w from all of these tables. Is there away to
query all of the tables that start with "connection_events_ " at the same
time? Is there a different way to deal with this?
I an trying to do this through an Access 2003 .ADP. I posted to that user
group and was refered here.
Thanks in advance for your suggestions.
SteveYou could set up a job to create a new view each day that would select from
the "ellusive" tables.
Tables names must be listed explicitly - wildcards are not applicable.
Basically, what you need is a view like in this example:
select <columns>
from <table_name>_2005-10-27
union all
select <columns>
from <table_name>_2005-10-28
union all
select <columns>
from <table_name>_2005-10-29
...
ML|||>> it writes each day's data to a different table. It names them connection
_events_2005_10_20 then connection_events_2005_10_21 etc... <<
You have just re-discovered 1950's magnetic tape processsing! You
almost mimicked the IBM naming conventions which gave tape labels the
markers 'yyddd' that we used when we did not have RDBMS!
Steve, you reallllllllllly need to stop programming and catch up with
RDBMS technology. You have missed the basics of RDBMS.
You keep the same data in the same table, period. A table is a set of
ALL --repeat ALL-- of those data elements. This is founjdations, not
rocket science. In RDBMS, you have a column (NOT a field like in the
1950's) that gives a duration, then you create VIEWs or derived tables
or subqueries.|||> You keep the same data in the same table, period.
well we have been using partitioning and UNION ALL views for quite a
while, not because we like doing it, but because it really boosts
performance. The performance price of following the advice to "keep the
same data in the same table" would be so huge - anyone actually doing
so might be fired on the spot...
Makes sense?|||I agree with your observations, however the program was written by the
firewall manufacturer so I have no control over it. I was surprised to see
how they setup this up but they aren't going to make changes for little 'ol
me. Their reports are lacking to say the least, so that's why I'm stuck with
this problem. Since I can't fix their program, are there any suggestions on
how to create a query that can pull the data from these tables together so
it's useful?
Thanks
Steve
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1130449917.894438.155260@.z14g2000cwz.googlegroups.com...
> You have just re-discovered 1950's magnetic tape processsing! You
> almost mimicked the IBM naming conventions which gave tape labels the
> markers 'yyddd' that we used when we did not have RDBMS!
> Steve, you reallllllllllly need to stop programming and catch up with
> RDBMS technology. You have missed the basics of RDBMS.
> You keep the same data in the same table, period. A table is a set of
> ALL --repeat ALL-- of those data elements. This is founjdations, not
> rocket science. In RDBMS, you have a column (NOT a field like in the
> 1950's) that gives a duration, then you create VIEWs or derived tables
> or subqueries.
>|||I have hacked a script togethor that might do what you want. Let me
know what you think. I used a couple of previous posts on this group to
help. I am a bit of a newbie myself. I have tested the script as far as
the creation of the UNION SQL but not the overall procedure.
Celtic_Kiwi
/* Script begins */
CREATE PROCEDURE upLogViewCreate
AS
declare @.tablename varchar(32)
declare @.sql varchar(4000)
declare tnames_cursor cursor
for
select name
from sysobjects
where type = 'U'
and left(name,3) = '200'
open tnames_cursor
fetch next from tnames_cursor into @.tablename
set @.sql = ''
while (@.@.fetch_status <> -1)
begin
if (@.@.fetch_status <> -2)
begin
if @.sql = ''
begin
set @.sql = 'select * from ' + @.tablename
end
else
begin
set @.sql = @.sql + ' union select * from ' + @.tablename
end
end
fetch next from tnames_cursor into @.tablename
end
CLOSE tnames_cursor
DEALLOCATE tnames_cursor
if ( (exists
(select *
from dbo.sysobjects
where id = object_id(N'[dbo].[vwLogView]')
and OBJECTPROPERTY(id, N'IsView') = 1
)
and
(@.sql<>'')
)
drop view [dbo].[qryNotesCandiac]
EXEC('CREATE VIEW dbo.vwLogView
AS ' + @.sql )|||>> we have been using partitioning and UNION ALL views for quite a while, no
t because we like doing it, but because it really boosts performance <<
No, we did it until SQL engines did a good job of implemenation that we
did not have to. Have you seen what DB2 does to detect a DW (fact and
dimension tables) ?
That is why we do not ever confuse implementation with logical models.
A partitioned table is not like multiple table at all. You are still
thinking that logical = physical because you are priogramming as if
this was the 1960's.|||You can do this with dynamic sql it goes like this;
steps;
1. select all tables from sysobjects table one after another
2. put that in to variable and use union query in dynamic sql
3. use exec(that query string) or sp_executesql
4. you have all rows in one view/derived table
Regards
R.D
--Knowledge gets doubled when shared
"Steve Roberts" wrote:

> We have a web tracking program that came with our firewall that writes it'
s
> data to a MSDE database.
> Unfortunately it writes each day's data to a different table. It names the
m
> connection_events_2005_10_20 then connection_events_2005_10_21 etc... I ne
ed
> to create a report by the w from all of these tables. Is there away to
> query all of the tables that start with "connection_events_ " at the same
> time? Is there a different way to deal with this?
> I an trying to do this through an Access 2003 .ADP. I posted to that user
> group and was refered here.
> Thanks in advance for your suggestions.
> Steve
>
>|||>> The performance price of following the advice to "keep the same data in
It depends on the specific schema/DBMS/physical implementation and the
performance price cannot be generalized. However the integrity issues
involved in doing so can be generalized. For some details, google for
"Principle of Orthogonal Design"
Anith|||Could you try creating views in advance of the firewall creating the tables?
then point all the views at the same table and have it log into a single
place?
IIRC, MSDE comes with sql agent and you could schedule a job to create the
views 7 days in advance, and destroy them once they're "old".
"Steve Roberts" <Stever@.Discussiongroups.com> wrote in message
news:OMwmRM12FHA.3296@.TK2MSFTNGP09.phx.gbl...
> I agree with your observations, however the program was written by the
> firewall manufacturer so I have no control over it. I was surprised to see
> how they setup this up but they aren't going to make changes for little
'ol
> me. Their reports are lacking to say the least, so that's why I'm stuck
with
> this problem. Since I can't fix their program, are there any suggestions
on
> how to create a query that can pull the data from these tables together so
> it's useful?
> Thanks
> Steve
>
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1130449917.894438.155260@.z14g2000cwz.googlegroups.com...
<<
>

Query to show column dependencies

I am trying to write a query to show column dependencies. I want to write a
query that will show me everything effected by changing a column in a table.
I want something where I can give it a table and column name and it will sho
w
me which tables/views and the columns that are dependent on that column I
want to change. I have been able to find the dependent columns, but I canno
t
tell which fields they are dependent upon. I also need it to show all
dependencies. For instance a column in table "A" is referenced in a column
view "X", then that column in view "X" is referenced in view "Y". Please
help!!! Here is the query I have so far.
----
--
declare @.tbl_nme as varchar(50)
declare @.col_nme as varchar(50)
set @.tbl_nme='V030_SCRMSCTM'
set @.col_nme= 'SCTM_SYS_DATE'
select
obj.name as obj_nm
, col.name as col_nm
, depobj.name as dep_obj_nm
, CASE depobj.type
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default'
WHEN 'F' THEN 'FOREIGN KEY'
WHEN 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'In-lined table-function'
WHEN 'K' THEN 'PRIMARY KEY'
WHEN 'L' THEN 'Log'
WHEN 'P' THEN 'Stored procedure'
WHEN 'R' THEN 'Rule'
WHEN 'RF' THEN 'Replication filter stored procedure'
WHEN 'S' THEN 'System table'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'Trigger'
WHEN 'U' THEN 'User table'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
END as dep_obj_type
, null as dep_col_nm
from sysobjects obj
join syscolumns col on obj.id = col.id
left join (sysdepends dep join sysobjects depobj on depobj.id = dep.id)
on obj.id = dep.depid
and col.colid = dep.depnumber
where obj.name = @.tbl_nme
and col.name = @.col_nme
order by
obj.name,depobj.name, col_name(depid, dep.depnumber)
----
--
Jasontry this.. there is some problem with this clause...
col.colid = dep.depnumber
check it out.. anyways.. try this
declare @.tbl_nme as varchar(50)
declare @.col_nme as varchar(50)
declare @.level int
set @.level = 1
set @.tbl_nme='V030_SCRMSCTM'
set @.col_nme= 'SCTM_SYS_DATE'
select
obj.name as obj_nm
, col.name as col_nm
, depobj.name as dep_obj_nm
, CASE depobj.type
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default'
WHEN 'F' THEN 'FOREIGN KEY'
WHEN 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'In-lined table-function'
WHEN 'K' THEN 'PRIMARY KEY'
WHEN 'L' THEN 'Log'
WHEN 'P' THEN 'Stored procedure'
WHEN 'R' THEN 'Rule'
WHEN 'RF' THEN 'Replication filter stored procedure'
WHEN 'S' THEN 'System table'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'Trigger'
WHEN 'U' THEN 'User table'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
END as dep_obj_type
, null as dep_col_nm
, @.level as level
into #temp
from sysobjects obj
join syscolumns col on obj.id = col.id
left join (sysdepends dep join sysobjects depobj on depobj.id = dep.id)
on obj.id = dep.depid
and col.colid = dep.depnumber
where obj.name = @.tbl_nme
and col.name = @.col_nme
while (@.@.rowcount > 0)
begin
set @.level = @.level + 1
insert into #temp
select
obj.name as obj_nm
, col.name as col_nm
, depobj.name as dep_obj_nm
, CASE depobj.type
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default'
WHEN 'F' THEN 'FOREIGN KEY'
WHEN 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'In-lined table-function'
WHEN 'K' THEN 'PRIMARY KEY'
WHEN 'L' THEN 'Log'
WHEN 'P' THEN 'Stored procedure'
WHEN 'R' THEN 'Rule'
WHEN 'RF' THEN 'Replication filter stored procedure'
WHEN 'S' THEN 'System table'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'Trigger'
WHEN 'U' THEN 'User table'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
END as dep_obj_type
, null as dep_col_nm
, @.level as level
from sysobjects obj
join syscolumns col on obj.id = col.id
left join (sysdepends dep join sysobjects depobj on depobj.id = dep.id)
on obj.id = dep.depid
and col.colid = dep.depnumber
where exists(select 1 from #temp a where obj.name = a.dep_obj_nm and
col.name = a.dep_col_nm and level = @.level - 1 and dep_col_nm is not null)
end
select * from #temp
drop table #temp|||Sorry, I couldn't quite follow what you were trying to do here with the
'level' column. I still didn't see anything with the column names.
I think I am a little closer now with this, but it is still not quite right.
I am getting everything with this query, but it is linking it with every
column in the dependent table/view, not just the actual dependent columns.
========================================
=============
declare @.tbl_nme as varchar(50)
declare @.col_nme as varchar(50)
set @.tbl_nme='SCTM'
set @.col_nme= 'test_shop_dt_today'
select --distinct
obj.name as obj_nm
, col.name as col_nm
, depobj.name as dep_obj_nm
, CASE depobj.type
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default'
WHEN 'F' THEN 'FOREIGN KEY'
WHEN 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'In-lined table-function'
WHEN 'K' THEN 'PRIMARY KEY'
WHEN 'L' THEN 'Log'
WHEN 'P' THEN 'Stored procedure'
WHEN 'R' THEN 'Rule'
WHEN 'RF' THEN 'Replication filter stored procedure'
WHEN 'S' THEN 'System table'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'Trigger'
WHEN 'U' THEN 'User table'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
END as dep_obj_type
, col_name(dep2.depid,dep2.depnumber) as dep_col_nm
from sysobjects obj
join syscolumns col on obj.id = col.id
left join (
(sysdepends dep join sysobjects depobj on depobj.id = dep.id)
join sysdepends dep2 on dep.id = dep2.depid
)
on obj.id = dep.depid
and col.colid = dep.depnumber
where obj.name = @.tbl_nme
and col.name = @.col_nme
========================================
=============
--
Jason
"Omnibuzz" wrote:

> try this.. there is some problem with this clause...
> col.colid = dep.depnumber
> check it out.. anyways.. try this
> declare @.tbl_nme as varchar(50)
> declare @.col_nme as varchar(50)
> declare @.level int
> set @.level = 1
> set @.tbl_nme='V030_SCRMSCTM'
> set @.col_nme= 'SCTM_SYS_DATE'
>
> select
> obj.name as obj_nm
> , col.name as col_nm
> , depobj.name as dep_obj_nm
> , CASE depobj.type
> WHEN 'C' THEN 'CHECK constraint'
> WHEN 'D' THEN 'Default'
> WHEN 'F' THEN 'FOREIGN KEY'
> WHEN 'FN' THEN 'Scalar function'
> WHEN 'IF' THEN 'In-lined table-function'
> WHEN 'K' THEN 'PRIMARY KEY'
> WHEN 'L' THEN 'Log'
> WHEN 'P' THEN 'Stored procedure'
> WHEN 'R' THEN 'Rule'
> WHEN 'RF' THEN 'Replication filter stored procedure'
> WHEN 'S' THEN 'System table'
> WHEN 'TF' THEN 'Table function'
> WHEN 'TR' THEN 'Trigger'
> WHEN 'U' THEN 'User table'
> WHEN 'V' THEN 'View'
> WHEN 'X' THEN 'Extended stored procedure'
> END as dep_obj_type
> , null as dep_col_nm
> , @.level as level
> into #temp
> from sysobjects obj
> join syscolumns col on obj.id = col.id
> left join (sysdepends dep join sysobjects depobj on depobj.id = dep.i
d)
> on obj.id = dep.depid
> and col.colid = dep.depnumber
> where obj.name = @.tbl_nme
> and col.name = @.col_nme
>
> while (@.@.rowcount > 0)
> begin
> set @.level = @.level + 1
> insert into #temp
> select
> obj.name as obj_nm
> , col.name as col_nm
> , depobj.name as dep_obj_nm
> , CASE depobj.type
> WHEN 'C' THEN 'CHECK constraint'
> WHEN 'D' THEN 'Default'
> WHEN 'F' THEN 'FOREIGN KEY'
> WHEN 'FN' THEN 'Scalar function'
> WHEN 'IF' THEN 'In-lined table-function'
> WHEN 'K' THEN 'PRIMARY KEY'
> WHEN 'L' THEN 'Log'
> WHEN 'P' THEN 'Stored procedure'
> WHEN 'R' THEN 'Rule'
> WHEN 'RF' THEN 'Replication filter stored procedure'
> WHEN 'S' THEN 'System table'
> WHEN 'TF' THEN 'Table function'
> WHEN 'TR' THEN 'Trigger'
> WHEN 'U' THEN 'User table'
> WHEN 'V' THEN 'View'
> WHEN 'X' THEN 'Extended stored procedure'
> END as dep_obj_type
> , null as dep_col_nm
> , @.level as level
> from sysobjects obj
> join syscolumns col on obj.id = col.id
> left join (sysdepends dep join sysobjects depobj on depobj.id = dep.i
d)
> on obj.id = dep.depid
> and col.colid = dep.depnumber
> where exists(select 1 from #temp a where obj.name = a.dep_obj_nm and
> col.name = a.dep_col_nm and level = @.level - 1 and dep_col_nm is not null)
> end
> select * from #temp
> drop table #temp
>|||What I had written will give you nested dependencies and shows you the level
it is nested with respect to the input table...
Ex: table 1 col1 --> view 1 col1 --> SP1
this will show that SP1 is also dependent on table1.. do I make sense?
I have modfied it..
check it and let me know if its fine..
declare @.tbl_nme as varchar(50)
declare @.col_nme as varchar(50)
declare @.level int
set @.level = 1
set @.tbl_nme='cpt56000'
set @.col_nme= 'o_crp'
select
obj.name as obj_nm
, col.name as col_nm
, depobj.name as dep_obj_nm
, CASE depobj.type
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default'
WHEN 'F' THEN 'FOREIGN KEY'
WHEN 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'In-lined table-function'
WHEN 'K' THEN 'PRIMARY KEY'
WHEN 'L' THEN 'Log'
WHEN 'P' THEN 'Stored procedure'
WHEN 'R' THEN 'Rule'
WHEN 'RF' THEN 'Replication filter stored procedure'
WHEN 'S' THEN 'System table'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'Trigger'
WHEN 'U' THEN 'User table'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
END as dep_obj_type
, col_name(dep.depid,dep.depnumber) as dep_col_nm
, @.level as level
into #temp
from sysobjects obj
join syscolumns col on obj.id = col.id
left join (sysdepends dep join sysobjects depobj on depobj.id = dep.id)
on obj.id = dep.depid
and col.colid = dep.depnumber
where obj.name = @.tbl_nme
and col.name = @.col_nme
while (@.@.rowcount > 0)
begin
set @.level = @.level + 1
insert into #temp
select
obj.name as obj_nm
, col.name as col_nm
, depobj.name as dep_obj_nm
, CASE depobj.type
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default'
WHEN 'F' THEN 'FOREIGN KEY'
WHEN 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'In-lined table-function'
WHEN 'K' THEN 'PRIMARY KEY'
WHEN 'L' THEN 'Log'
WHEN 'P' THEN 'Stored procedure'
WHEN 'R' THEN 'Rule'
WHEN 'RF' THEN 'Replication filter stored procedure'
WHEN 'S' THEN 'System table'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'Trigger'
WHEN 'U' THEN 'User table'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
END as dep_obj_type
, null as dep_col_nm
, @.level as level
from sysobjects obj
join syscolumns col on obj.id = col.id
left join (sysdepends dep join sysobjects depobj on depobj.id = dep.id)
on obj.id = dep.depid
and col.colid = dep.depnumber
where exists(select 1 from #temp a where obj.name = a.dep_obj_nm and
col.name = a.dep_col_nm and level = @.level - 1 and dep_col_nm is not null)
end
select * from #temp
drop table #temp|||It works except for the dep_col_nm field. It is giving the column name from
the source table, not the dependent table. That seems to be the show stoppe
r.
Thanks,
Jason
"Omnibuzz" wrote:

> What I had written will give you nested dependencies and shows you the lev
el
> it is nested with respect to the input table...
> Ex: table 1 col1 --> view 1 col1 --> SP1
> this will show that SP1 is also dependent on table1.. do I make sense?
> I have modfied it..
> check it and let me know if its fine..
>
> declare @.tbl_nme as varchar(50)
> declare @.col_nme as varchar(50)
> declare @.level int
> set @.level = 1
> set @.tbl_nme='cpt56000'
> set @.col_nme= 'o_crp'
>
> select
> obj.name as obj_nm
> , col.name as col_nm
> , depobj.name as dep_obj_nm
> , CASE depobj.type
> WHEN 'C' THEN 'CHECK constraint'
> WHEN 'D' THEN 'Default'
> WHEN 'F' THEN 'FOREIGN KEY'
> WHEN 'FN' THEN 'Scalar function'
> WHEN 'IF' THEN 'In-lined table-function'
> WHEN 'K' THEN 'PRIMARY KEY'
> WHEN 'L' THEN 'Log'
> WHEN 'P' THEN 'Stored procedure'
> WHEN 'R' THEN 'Rule'
> WHEN 'RF' THEN 'Replication filter stored procedure'
> WHEN 'S' THEN 'System table'
> WHEN 'TF' THEN 'Table function'
> WHEN 'TR' THEN 'Trigger'
> WHEN 'U' THEN 'User table'
> WHEN 'V' THEN 'View'
> WHEN 'X' THEN 'Extended stored procedure'
> END as dep_obj_type
> , col_name(dep.depid,dep.depnumber) as dep_col_nm
> , @.level as level
> into #temp
> from sysobjects obj
> join syscolumns col on obj.id = col.id
> left join (sysdepends dep join sysobjects depobj on depobj.id = dep.i
d)
> on obj.id = dep.depid
> and col.colid = dep.depnumber
> where obj.name = @.tbl_nme
> and col.name = @.col_nme
>
> while (@.@.rowcount > 0)
> begin
> set @.level = @.level + 1
> insert into #temp
> select
> obj.name as obj_nm
> , col.name as col_nm
> , depobj.name as dep_obj_nm
> , CASE depobj.type
> WHEN 'C' THEN 'CHECK constraint'
> WHEN 'D' THEN 'Default'
> WHEN 'F' THEN 'FOREIGN KEY'
> WHEN 'FN' THEN 'Scalar function'
> WHEN 'IF' THEN 'In-lined table-function'
> WHEN 'K' THEN 'PRIMARY KEY'
> WHEN 'L' THEN 'Log'
> WHEN 'P' THEN 'Stored procedure'
> WHEN 'R' THEN 'Rule'
> WHEN 'RF' THEN 'Replication filter stored procedure'
> WHEN 'S' THEN 'System table'
> WHEN 'TF' THEN 'Table function'
> WHEN 'TR' THEN 'Trigger'
> WHEN 'U' THEN 'User table'
> WHEN 'V' THEN 'View'
> WHEN 'X' THEN 'Extended stored procedure'
> END as dep_obj_type
> , null as dep_col_nm
> , @.level as level
> from sysobjects obj
> join syscolumns col on obj.id = col.id
> left join (sysdepends dep join sysobjects depobj on depobj.id = dep.i
d)
> on obj.id = dep.depid
> and col.colid = dep.depnumber
> where exists(select 1 from #temp a where obj.name = a.dep_obj_nm and
> col.name = a.dep_col_nm and level = @.level - 1 and dep_col_nm is not null)
> end
> select * from #temp
> drop table #temp
>|||Thats right. Thats why I said look into this statement
and col.colid = dep.depnumber
from my knowledge, depnumber gives the dependent procedure number..
will anyways look into it today.. we will find a solution for this :)
"JasonDWilson" wrote:
> It works except for the dep_col_nm field. It is giving the column name fr
om
> the source table, not the dependent table. That seems to be the show stop
per.
> Thanks,
> --
> Jason
>
> "Omnibuzz" wrote:
>|||Hi Jason,
I feel we cannot get the column level dependecy. To my knowledge, none
of the system tables has this information. The best we can get about
dependency is by using sp_depends for object level dependecy. Hope this help
s.