Deleting a field in a table

hi,
I am trying to delete a column in a table. I am not sure there is anything
(ie.SP. Views or triggers) is ref the that field.
There is a way to find out?
Thnaks`You can use sp_depends to list the dependencies. EXEC sp_depends @.objname =
N'table_name'
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"mecn" <mecn2002@.yahoo.com> wrote in message
news:OoAFzpjiGHA.2456@.TK2MSFTNGP04.phx.gbl...
> hi,
> I am trying to delete a column in a table. I am not sure there is anything
> (ie.SP. Views or triggers) is ref the that field.
> There is a way to find out?
> Thnaks`
>|||Thanks, Gail
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:OYG0JLliGHA.3496@.TK2MSFTNGP04.phx.gbl...
> You can use sp_depends to list the dependencies. EXEC sp_depends @.objname
> = N'table_name'
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/pr...oads/books.mspx
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:OoAFzpjiGHA.2456@.TK2MSFTNGP04.phx.gbl...
>

Deleting a field in a table

hi,
I am trying to delete a column in a table. I am not sure there is anything
(ie.SP. Views or triggers) is ref the that field.
There is a way to find out?
Thnaks`You can use sp_depends to list the dependencies. EXEC sp_depends @.objname =N'table_name'
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"mecn" <mecn2002@.yahoo.com> wrote in message
news:OoAFzpjiGHA.2456@.TK2MSFTNGP04.phx.gbl...
> hi,
> I am trying to delete a column in a table. I am not sure there is anything
> (ie.SP. Views or triggers) is ref the that field.
> There is a way to find out?
> Thnaks`
>|||Thanks, Gail
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:OYG0JLliGHA.3496@.TK2MSFTNGP04.phx.gbl...
> You can use sp_depends to list the dependencies. EXEC sp_depends @.objname
> = N'table_name'
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:OoAFzpjiGHA.2456@.TK2MSFTNGP04.phx.gbl...
>> hi,
>> I am trying to delete a column in a table. I am not sure there is
>> anything (ie.SP. Views or triggers) is ref the that field.
>> There is a way to find out?
>> Thnaks`
>

Delete Triggers/Procedures...

What's the SQL statement that will allow me to delete all the triggers and procedures that I have created. (I don't remember the names of the procedures and triggers, but would like to clear everything). Thanks.If you can connect to the schema which created those triggers & procedures, you can get the names by querying the user_objects views.

select object_type, object_name
from user_objects
where object_type like 'PROC%' or object_type like 'TRIG%';

And to delete all the above, issue these sqls:

sql> set feedback off
sql> set termout off
sql> spool c:\temp\droptrigproc.sql
sql> select 'drop '||object_type||' '||object_name
2> from user_objects
3> where object_type like 'PROC%' or object_type like 'TRIG%';
sql> spool off
sql> @.c:\temp\droptrigproc.sql

Before doing the above, make sure you are NOT deleting other required objects !

Or the fastest way to cleanup would be to drop the schema/user and recreate it.

Originally posted by VB_Oracle
What's the SQL statement that will allow me to delete all the triggers and procedures that I have created. (I don't remember the names of the procedures and triggers, but would like to clear everything). Thanks.

delete triggers

Hi,, How do i delete all triggers in one database? Quick way.
ThanksI think you could use the new 'SQL Server Management Studio' to highlight as
many triggers you wanted, right click and select delete. I know you can with
Embarcadero's DBArtisan tool.
hth
"mecn" <mecn2002@.yahoo.com> wrote in message
news:O9pIpnDVGHA.5148@.TK2MSFTNGP12.phx.gbl...
> Hi,, How do i delete all triggers in one database? Quick way.
> Thanks
>|||Thanks
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:e0hhli$177l$1@.sxnews1.qg.com...
>I think you could use the new 'SQL Server Management Studio' to highlight
>as many triggers you wanted, right click and select delete. I know you can
>with Embarcadero's DBArtisan tool.
> hth
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:O9pIpnDVGHA.5148@.TK2MSFTNGP12.phx.gbl...
>

delete triggers

Hi,, How do i delete all triggers in one database? Quick way.
ThanksI think you could use the new 'SQL Server Management Studio' to highlight as
many triggers you wanted, right click and select delete. I know you can with
Embarcadero's DBArtisan tool.
hth
"mecn" <mecn2002@.yahoo.com> wrote in message
news:O9pIpnDVGHA.5148@.TK2MSFTNGP12.phx.gbl...
> Hi,, How do i delete all triggers in one database? Quick way.
> Thanks
>|||Thanks
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:e0hhli$177l$1@.sxnews1.qg.com...
>I think you could use the new 'SQL Server Management Studio' to highlight
>as many triggers you wanted, right click and select delete. I know you can
>with Embarcadero's DBArtisan tool.
> hth
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:O9pIpnDVGHA.5148@.TK2MSFTNGP12.phx.gbl...
>> Hi,, How do i delete all triggers in one database? Quick way.
>> Thanks
>

delete triggers

sql2k sp3
I assumed this would work as easily as the Insert and Update Triggers I
wrote did:
create trigger DelAuthors
on authors
for delete
as
begin
set nocount on
delete tmp_authors
where tmp_authors.au_id = deleted.au_id
end
Server: Msg 107, Level 16, State 2, Procedure DelAuthors, Line 7
The column prefix 'deleted' does not match with a table name or alias name
used in the query.
Heres the schema for tmp_authors(and Authors):
CREATE TABLE [dbo].[tmp_authors] (
[au_id] [id] NOT NULL ,
[au_lname] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[au_fname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[phone] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[address] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[state] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zip] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[contract] [bit] NOT NULL
) ON [PRIMARY]
GO
Help is appreciated.
TIA, ChrisRChange your DELETE statement to:
DELETE FROM tmp_authors
WHERE EXISTS
(SELECT *
FROM deleted
WHERE tmp_authors.au_id = deleted.au_id)
That's the ANSI Standard update syntax. SQL Server also supports Microsoft
own proprietary version of the DELETE statement using joins and which,
bizarely, requires an extra FROM clause to do what you are attempting.
DELETE
FROM tmp_authors
FROM deleted
WHERE tmp_authors.au_id = deleted.au_id
David Portas
SQL Server MVP
--|||Thanks David.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:ZLWdnU8W65uGbv_fRVn-tw@.giganews.com...
> Change your DELETE statement to:
> DELETE FROM tmp_authors
> WHERE EXISTS
> (SELECT *
> FROM deleted
> WHERE tmp_authors.au_id = deleted.au_id)
> That's the ANSI Standard update syntax. SQL Server also supports Microsoft
> own proprietary version of the DELETE statement using joins and which,
> bizarely, requires an extra FROM clause to do what you are attempting.
> DELETE
> FROM tmp_authors
> FROM deleted
> WHERE tmp_authors.au_id = deleted.au_id
> --
> David Portas
> SQL Server MVP
> --
>

delete triggers

Hi,, How do i delete all triggers in one database? Quick way.
Thanks
I think you could use the new 'SQL Server Management Studio' to highlight as
many triggers you wanted, right click and select delete. I know you can with
Embarcadero's DBArtisan tool.
hth
"mecn" <mecn2002@.yahoo.com> wrote in message
news:O9pIpnDVGHA.5148@.TK2MSFTNGP12.phx.gbl...
> Hi,, How do i delete all triggers in one database? Quick way.
> Thanks
>
|||Thanks
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:e0hhli$177l$1@.sxnews1.qg.com...
>I think you could use the new 'SQL Server Management Studio' to highlight
>as many triggers you wanted, right click and select delete. I know you can
>with Embarcadero's DBArtisan tool.
> hth
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:O9pIpnDVGHA.5148@.TK2MSFTNGP12.phx.gbl...
>