How can I know the total time (in miliseconds) for my SQL Server to answer a
query. I want to issue a query using an Query Analyzer and Measure the
Servers total response time. Thanks in advance!
On Wed, 2 Mar 2005 23:51:02 -0800, Borikoy wrote:
>How can I know the total time (in miliseconds) for my SQL Server to answer a
>query. I want to issue a query using an Query Analyzer and Measure the
>Servers total response time. Thanks in advance!
Hi Borikoy,
The simplest form is
SELECT CURRENT_TIMESTAMP
-- Insert your query here
SELECT CURRENT_TIMESTAMP
Slightly more advanced would be
DECLARE @.start datetime, @.end datetime
SET @.start = CURRENT_TIMESTAMP
-- Insert your query here
SET @.end = CURRENT_TIMESTAMP
SELECT DATEDIFF(ms, @.start, @.end)
In both cases, the time used to push the rows to the client is included.
For a clean measurement of time taken to process the query, I usually
change all SELECT queries to SELECT INTO queries (so that the ooutput
goes in a temp table instead of over a possibly slow network, or is
slowed by display processing).
Another way to get information about time taken is:
SET STATISTICS TIME ON
-- Insert your query here
SET STATISTICS TIME OFF
Check out the description of SET STATISTICS TIME in Books Online. And
you might wish to read up on SET STATISTICS IO as well.
Whatever way you use, you won't get accurate comparisons if you run
queries with data in the cache. Often, the first execution of a query
takes longer because data needs to be brought into cache from hard disk;
subsequent invocations go lots faster because the data is still in
cache, so the slow hard disk access is not necessary anymore. To clear
out the data cache and the procedure cache as well (so you won't reuse
old execution plans), run the following statements before doing your
tests:
CHECKPOINT -- Write dirty pages to disk
DBCC DROPCLEANBUFFERS -- Flush all non-dirty pages from cache
DBCC FREEPROCCACHE -- Remove all cached execution plans
Running the above statements on a production database will severely
impact the performance of your running processes - but I assume that you
don't intend to run performance tests on a live production database
anyway!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
sql
Showing posts with label aquery. Show all posts
Showing posts with label aquery. Show all posts
Wednesday, March 21, 2012
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.
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.
Subscribe to:
Posts (Atom)