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.
