February 11, 2009 at 9:08 am
I have inherited a data mart with wide rows and we will soon migrate to 2008. Some tables are so wide that only 2 rows fit into one page.
I am convinced that I can split those tables into subject (sub-)tables. The easiest way to do that is to take the SP's that our customers use for their reports and find out what tables and columns they use:
SELECT e.referenced_schema_name,
e.referenced_entity_name ,
isnull(e.referenced_minor_name,''),
o.type_desc,
c.DATA_TYPE,
c.CHARACTER_MAXIMUM_LENGTH,
c.NUMERIC_PRECISION,
c.NUMERIC_PRECISION_RADIX,
c.NUMERIC_SCALE,
c.ORDINAL_POSITION
FROM sys.dm_sql_referenced_entities ('dbo.sp1', 'OBJECT') e
left outer join sys.objects o
on e.referenced_id=o.object_id
left outer join INFORMATION_SCHEMA.COLUMNS c
on c.TABLE_SCHEMA=e.referenced_schema_name
and c.TABLE_NAME=e.referenced_entity_name
and c.COLUMN_NAME=e.referenced_minor_name;
Does that trick easily.
Now the problem. we use views for several reasons and what I can get is the base tables names and their column names.
select * from information_schema.view_column_usage
where view_name='v1'
What I also get is the column names for the views:
select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='v1'
Where I am at loss is the missing link to associate the column name of the view to the object name and column name of the underlying view of table.
I am stuck but I am sure that there MUST be a way to link those two domains together.
Any help and if it is only ideas thar could help me go further would be great!
February 11, 2009 at 11:30 am
To build the bogus example to document my problem:
create table t1 ( c1 int, c2 int, c3 varchar(12));
create table /*guentis.*/ t2 ( c1 int, c2 int, c3 varchar(12));
insert into t1 values (1,1,'1:1');
insert into t1 values (2,1,'2:1');
insert into t1 values (3,1,'3:1');
insert into t1 values (4,1,'4.1');
insert into /* guentis.*/ t2 values (1,2,'1:2');
insert into /* guentis.*/ t2 values (2,2,'2:2');
insert into /* guentis.*/ t2 values (3,2,'3:2');
insert into /* guentis.*/ t2 values (4,2,'4.2');
create view v1 (
t1c1 , t2c1
, t1c2 , t2c2
, t1c3 , t2c3
) as
select
a.c1 , b.c1 ,
a.c2 , b.c2 ,
a.c3 , b.c3
from t1 as a
join /*guentis.*/t2 as b
on a.c1=b.c1
;
create procedure sp1 @k1 int,@r1 int output
as
begin
select @r1=SUM(a.X)
from (
select c.t1c2+d.t2c2 as x
from v1 as c
join v1 as d
on c.t1c1=d.t2c1
) as a
end
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply