Finding all references to a column

I have a task to where I need to move a column from one table to
another. I want to be sure I update any view, stored procedure,
trigger, etc. that references the column. I simply want a query that
will report the related objects and then I will update them manually
but before I go and try and figure out how to do this by querying the
sys tables is there an sp_sproc that will do this?[posted and mailed, please reply in news]

rnewman (newmanr19@.yahoo.com) writes:
> I have a task to where I need to move a column from one table to
> another. I want to be sure I update any view, stored procedure,
> trigger, etc. that references the column. I simply want a query that
> will report the related objects and then I will update them manually
> but before I go and try and figure out how to do this by querying the
> sys tables is there an sp_sproc that will do this?

The best way is to build the database from scripts, with the column
reomved, and then look through all errors you get.

You can also run this query:

select object_name(id)
from sysdepends
where depid = object_id('tbl')
and col_name(depid, depnumber) = 'col'
order by 1

However, this may not be reliable. If you can be confident that all
procedures abd views have been created/altered after the table was
created, this will work. But if you have dropped the table and replaced
with a new version, or you loaded stored procedures before you created
the table, the dependency information will be incomplete.

Note: while the sysdepends tables is documented in Books Online,
the usage of the depnumber as column id is undocumented, and may
be subject to change without notice.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

find out tirggers in a DB

Hi,
I've a DB not designed by me. I found that there is a trigger updating
table B when I update table A. I want to disable this trigger but I
don't know the name. Can someone help me? Thanks.
See if this helps you
http://dimantdatabasesolutions.blogspot.com/2007/04/has-table-trigger.html
"klonic" <jaume.pf@.gmail.com> wrote in message
news:1181202534.816813.51310@.m36g2000hse.googlegro ups.com...
> Hi,
> I've a DB not designed by me. I found that there is a trigger updating
> table B when I update table A. I want to disable this trigger but I
> don't know the name. Can someone help me? Thanks.
>
|||Thanks for your help. I found the trigger I was looking for!!! It's
possible to know the code of the trigger?
On Jun 7, 10:04 am, "vt" <vinu.t.1...@.gmail.com> wrote:
> Hi
> Hope you using sql 2000
> SELECT
> name,
> status = CASE WHEN OBJECTPROPERTY (id, 'ExecIsTriggerDisabled') = 0
> THEN 'Enabled' ELSE 'Disabled' END,
> Table_name = OBJECT_NAME (parent_obj)
> FROM
> sysobjects
> WHERE
> type = 'TR'
> the above select returns all the triggers in the database.
> then use the ALTER TABLE to disable the trigger
> --
> VT
> Knowledge is power, share it...http://oneplace4sql.blogspot.com/"klonic" <jaume...@.gmail.com> wrote in message
> news:1181202534.816813.51310@.m36g2000hse.googlegro ups.com...
>
>
> - Show quoted text -
|||On Jun 7, 1:47 pm, "vt" <vinu.t.1...@.gmail.com> wrote:
> Hi
> sp_helptext triggername
> See bol for more info
> regards
> VT
> Knowledge is power, share it...http://oneplace4sql.blogspot.com/"klonic" <jaume...@.gmail.com> wrote in message
> news:1181205063.304596.159350@.p47g2000hsd.googlegr oups.com...
>
>
>
>
>
>
> - Show quoted text -
select object_definition(object_id(<trigger name>))
sp_helptext <table name>
select definition from sys.sql_modules where object_id =
object_id(<trigger name>)
|||Thanks to everybody for your help! With your posts and some
imagination i solved out the problem!
C U!
On Jun 7, 4:52 pm, amish <shahami...@.gmail.com> wrote:
> On Jun 7, 1:47 pm, "vt" <vinu.t.1...@.gmail.com> wrote:
>
>
>
>
>
>
>
>
>
>
> select object_definition(object_id(<trigger name>))
> sp_helptext <table name>
> select definition from sys.sql_modules where object_id =
> object_id(<trigger name>)- Hide quoted text -
> - Show quoted text -

find out tirggers in a DB

Hi,
I've a DB not designed by me. I found that there is a trigger updating
table B when I update table A. I want to disable this trigger but I
don't know the name. Can someone help me? Thanks.Hi
Hope you using sql 2000
SELECT
name,
status = CASE WHEN OBJECTPROPERTY (id, 'ExecIsTriggerDisabled') = 0
THEN 'Enabled' ELSE 'Disabled' END,
Table_name = OBJECT_NAME (parent_obj)
FROM
sysobjects
WHERE
type = 'TR'
the above select returns all the triggers in the database.
then use the ALTER TABLE to disable the trigger
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"klonic" <jaume.pf@.gmail.com> wrote in message
news:1181202534.816813.51310@.m36g2000hse.googlegroups.com...
> Hi,
> I've a DB not designed by me. I found that there is a trigger updating
> table B when I update table A. I want to disable this trigger but I
> don't know the name. Can someone help me? Thanks.
>|||See if this helps you
http://dimantdatabasesolutions.blog...le-trigger.html
"klonic" <jaume.pf@.gmail.com> wrote in message
news:1181202534.816813.51310@.m36g2000hse.googlegroups.com...
> Hi,
> I've a DB not designed by me. I found that there is a trigger updating
> table B when I update table A. I want to disable this trigger but I
> don't know the name. Can someone help me? Thanks.
>|||Thanks for your help. I found the trigger I was looking for!!! It's
possible to know the code of the trigger?
On Jun 7, 10:04 am, "vt" <vinu.t.1...@.gmail.com> wrote:
> Hi
> Hope you using sql 2000
> SELECT
> name,
> status = CASE WHEN OBJECTPROPERTY (id, 'ExecIsTriggerDisabled') = 0
> THEN 'Enabled' ELSE 'Disabled' END,
> Table_name = OBJECT_NAME (parent_obj)
> FROM
> sysobjects
> WHERE
> type = 'TR'
> the above select returns all the triggers in the database.
> then use the ALTER TABLE to disable the trigger
> --
> VT
> Knowledge is power, share it...http://oneplace4sql.blogspot.com/"klonic"
<jaume...@.gmail.com> wrote in message
> news:1181202534.816813.51310@.m36g2000hse.googlegroups.com...
>
>
>
> - Show quoted text -|||Hi
sp_helptext triggername
See bol for more info
regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"klonic" <jaume.pf@.gmail.com> wrote in message
news:1181205063.304596.159350@.p47g2000hsd.googlegroups.com...
> Thanks for your help. I found the trigger I was looking for!!! It's
> possible to know the code of the trigger?
> On Jun 7, 10:04 am, "vt" <vinu.t.1...@.gmail.com> wrote:
>|||On Jun 7, 1:47 pm, "vt" <vinu.t.1...@.gmail.com> wrote:
> Hi
> sp_helptext triggername
> See bol for more info
> regards
> VT
> Knowledge is power, share it...http://oneplace4sql.blogspot.com/"klonic"
<jaume...@.gmail.com> wrote in message
> news:1181205063.304596.159350@.p47g2000hsd.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
select object_definition(object_id(<trigger name> ))
sp_helptext <table name>
select definition from sys.sql_modules where object_id =
object_id(<trigger name> )|||Thanks to everybody for your help! With your posts and some
imagination i solved out the problem!
C U!
On Jun 7, 4:52 pm, amish <shahami...@.gmail.com> wrote:
> On Jun 7, 1:47 pm, "vt" <vinu.t.1...@.gmail.com> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> select object_definition(object_id(<trigger name> ))
> sp_helptext <table name>
> select definition from sys.sql_modules where object_id =
> object_id(<trigger name> )- Hide quoted text -
> - Show quoted text -

find out tirggers in a DB

Hi,
I've a DB not designed by me. I found that there is a trigger updating
table B when I update table A. I want to disable this trigger but I
don't know the name. Can someone help me? Thanks.Hi
Hope you using sql 2000
SELECT
name,
status = CASE WHEN OBJECTPROPERTY (id, 'ExecIsTriggerDisabled') = 0
THEN 'Enabled' ELSE 'Disabled' END,
Table_name = OBJECT_NAME (parent_obj)
FROM
sysobjects
WHERE
type = 'TR'
the above select returns all the triggers in the database.
then use the ALTER TABLE to disable the trigger
--
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"klonic" <jaume.pf@.gmail.com> wrote in message
news:1181202534.816813.51310@.m36g2000hse.googlegroups.com...
> Hi,
> I've a DB not designed by me. I found that there is a trigger updating
> table B when I update table A. I want to disable this trigger but I
> don't know the name. Can someone help me? Thanks.
>|||See if this helps you
http://dimantdatabasesolutions.blogspot.com/2007/04/has-table-trigger.html
"klonic" <jaume.pf@.gmail.com> wrote in message
news:1181202534.816813.51310@.m36g2000hse.googlegroups.com...
> Hi,
> I've a DB not designed by me. I found that there is a trigger updating
> table B when I update table A. I want to disable this trigger but I
> don't know the name. Can someone help me? Thanks.
>|||Thanks for your help. I found the trigger I was looking for!!! It's
possible to know the code of the trigger?
On Jun 7, 10:04 am, "vt" <vinu.t.1...@.gmail.com> wrote:
> Hi
> Hope you using sql 2000
> SELECT
> name,
> status = CASE WHEN OBJECTPROPERTY (id, 'ExecIsTriggerDisabled') = 0
> THEN 'Enabled' ELSE 'Disabled' END,
> Table_name = OBJECT_NAME (parent_obj)
> FROM
> sysobjects
> WHERE
> type = 'TR'
> the above select returns all the triggers in the database.
> then use the ALTER TABLE to disable the trigger
> --
> VT
> Knowledge is power, share it...http://oneplace4sql.blogspot.com/"klonic" <jaume...@.gmail.com> wrote in message
> news:1181202534.816813.51310@.m36g2000hse.googlegroups.com...
>
> > Hi,
> > I've a DB not designed by me. I found that there is a trigger updating
> > table B when I update table A. I want to disable this trigger but I
> > don't know the name. Can someone help me? Thanks.- Hide quoted text -
> - Show quoted text -|||Hi
sp_helptext triggername
See bol for more info
regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"klonic" <jaume.pf@.gmail.com> wrote in message
news:1181205063.304596.159350@.p47g2000hsd.googlegroups.com...
> Thanks for your help. I found the trigger I was looking for!!! It's
> possible to know the code of the trigger?
> On Jun 7, 10:04 am, "vt" <vinu.t.1...@.gmail.com> wrote:
>> Hi
>> Hope you using sql 2000
>> SELECT
>> name,
>> status = CASE WHEN OBJECTPROPERTY (id, 'ExecIsTriggerDisabled') = 0
>> THEN 'Enabled' ELSE 'Disabled' END,
>> Table_name = OBJECT_NAME (parent_obj)
>> FROM
>> sysobjects
>> WHERE
>> type = 'TR'
>> the above select returns all the triggers in the database.
>> then use the ALTER TABLE to disable the trigger
>> --
>> VT
>> Knowledge is power, share it...http://oneplace4sql.blogspot.com/"klonic"
>> <jaume...@.gmail.com> wrote in message
>> news:1181202534.816813.51310@.m36g2000hse.googlegroups.com...
>>
>> > Hi,
>> > I've a DB not designed by me. I found that there is a trigger updating
>> > table B when I update table A. I want to disable this trigger but I
>> > don't know the name. Can someone help me? Thanks.- Hide quoted text -
>> - Show quoted text -
>|||On Jun 7, 1:47 pm, "vt" <vinu.t.1...@.gmail.com> wrote:
> Hi
> sp_helptext triggername
> See bol for more info
> regards
> VT
> Knowledge is power, share it...http://oneplace4sql.blogspot.com/"klonic" <jaume...@.gmail.com> wrote in message
> news:1181205063.304596.159350@.p47g2000hsd.googlegroups.com...
>
> > Thanks for your help. I found the trigger I was looking for!!! It's
> > possible to know the code of the trigger?
> > On Jun 7, 10:04 am, "vt" <vinu.t.1...@.gmail.com> wrote:
> >> Hi
> >> Hope you using sql 2000
> >> SELECT
> >> name,
> >> status = CASE WHEN OBJECTPROPERTY (id, 'ExecIsTriggerDisabled') = 0
> >> THEN 'Enabled' ELSE 'Disabled' END,
> >> Table_name = OBJECT_NAME (parent_obj)
> >> FROM
> >> sysobjects
> >> WHERE
> >> type = 'TR'
> >> the above select returns all the triggers in the database.
> >> then use the ALTER TABLE to disable the trigger
> >> --
> >> VT
> >> Knowledge is power, share it...http://oneplace4sql.blogspot.com/"klonic"
> >> <jaume...@.gmail.com> wrote in message
> >>news:1181202534.816813.51310@.m36g2000hse.googlegroups.com...
> >> > Hi,
> >> > I've a DB not designed by me. I found that there is a trigger updating
> >> > table B when I update table A. I want to disable this trigger but I
> >> > don't know the name. Can someone help me? Thanks.- Hide quoted text -
> >> - Show quoted text -- Hide quoted text -
> - Show quoted text -
select object_definition(object_id(<trigger name>))
sp_helptext <table name>
select definition from sys.sql_modules where object_id =object_id(<trigger name>)|||Thanks to everybody for your help! With your posts and some
imagination i solved out the problem!
C U!
On Jun 7, 4:52 pm, amish <shahami...@.gmail.com> wrote:
> On Jun 7, 1:47 pm, "vt" <vinu.t.1...@.gmail.com> wrote:
>
>
> > Hi
> > sp_helptext triggername
> > See bol for more info
> > regards
> > VT
> > Knowledge is power, share it...http://oneplace4sql.blogspot.com/"klonic" <jaume...@.gmail.com> wrote in message
> >news:1181205063.304596.159350@.p47g2000hsd.googlegroups.com...
> > > Thanks for your help. I found the trigger I was looking for!!! It's
> > > possible to know the code of the trigger?
> > > On Jun 7, 10:04 am, "vt" <vinu.t.1...@.gmail.com> wrote:
> > >> Hi
> > >> Hope you using sql 2000
> > >> SELECT
> > >> name,
> > >> status = CASE WHEN OBJECTPROPERTY (id, 'ExecIsTriggerDisabled') = 0
> > >> THEN 'Enabled' ELSE 'Disabled' END,
> > >> Table_name = OBJECT_NAME (parent_obj)
> > >> FROM
> > >> sysobjects
> > >> WHERE
> > >> type = 'TR'
> > >> the above select returns all the triggers in the database.
> > >> then use the ALTER TABLE to disable the trigger
> > >> --
> > >> VT
> > >> Knowledge is power, share it...http://oneplace4sql.blogspot.com/"klonic"
> > >> <jaume...@.gmail.com> wrote in message
> > >>news:1181202534.816813.51310@.m36g2000hse.googlegroups.com...
> > >> > Hi,
> > >> > I've a DB not designed by me. I found that there is a trigger updating
> > >> > table B when I update table A. I want to disable this trigger but I
> > >> > don't know the name. Can someone help me? Thanks.- Hide quoted text -
> > >> - Show quoted text -- Hide quoted text -
> > - Show quoted text -
> select object_definition(object_id(<trigger name>))
> sp_helptext <table name>
> select definition from sys.sql_modules where object_id => object_id(<trigger name>)- Hide quoted text -
> - Show quoted text -