how to drop index?

I want to check for duplicate data, if got duplicate data, then the
table will be dropped, if dun have, the table will not be dropped, but
the constraint and the indexed within the table will be dropped, and
an mail notification will be send. Below is my syntax uwing stored
procedure,when i run this stored procedure, it get an error message
:"Incorrect syntax near 'PK_Rewards_CatalogProducts_CS' ", Can anyone
tell me the correct way to drop the constraint and index?
if not exists(select catalog_code, count(catalog_code) from rd_awards
group
by catalog_code having
count(catalog_code) > 1)
begin
drop table [dbo].[Rewards_CatalogProducts_CS]
end
else
if exists(select catalog_code, count(catalog_code) from rd_awards
group
by catalog_code having
count(catalog_code) > 1)
begin
ALTER TABLE [dbo].[Rewards_CatalogProducts_CS]
DROP CONSTRAINT [PK_Rewards_CatalogProducts_CS]
GO
ALTER TABLE [dbo].[Rewards_CatalogProducts_CS]
DROP INDEX [idx_categorycode]
GO
ALTER TABLE [dbo].[Rewards_CatalogProducts_CS]
DROP INDEX [idx_g_org_SupplierId]
use master
declare @.FROM NVARCHAR(4000),
@.FROM_NAME NVARCHAR(4000),
@.TO NVARCHAR(4000),
@.CC NVARCHAR(4000),
@.BCC NVARCHAR(4000),
@.priority NVARCHAR(10),
@.subject NVARCHAR(4000),
@.message NVARCHAR(4000),
@.type NVARCHAR(100),
@.attachments NVARCHAR(4000),
@.codepage INT,
@.rc INT
select @.FROM = N'sqlmail@.cyber-village.net',
@.FROM_NAME = N'ChangMian',
@.TO = N'tchangmian@.yahoo.com.sg',
@.CC = N'changmian@.cyber-village.net',
@.priority = N'High',
@.subject = N'headache',
@.message = N'&

---

Hello SQL Server SMTP SQL
Mail

---

',
@.type = N'text/html',
@.attachments = N'',
@.codepage = 0
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = @.FROM,
@.TO = @.TO,
@.CC = @.CC,
@.priority = @.priority,
@.subject = @.subject,
@.message = @.message,
@.type = @.type,
@.attachments = @.attachments,
@.codepage = @.codepage,
@.server = N'mail.cyber-village.net'
select RC = @.rc
endHI!
Not the Alter Statement is the problem. The problem is that The BEGIN has no
coresponding END in your IF Statement, because you are dividing your script
in several batches with the GO Statement.
IF BEGIN END has to be in one Batch and with GO you are starting a new Batch
which is the unit of Parsing and Compilation for SQL Server. GO is not a
SQL Statement. It tells Query Analyzer, osql,... to seperate the script into
units which are send separately to SQL Server (see BOL)
Just remove the GO statements, then it should work. They are not necessary
in your case.
Cheers,
Herbert
"tchangmian" <tchangmian@.yahoo.com.sg> schrieb im Newsbeitrag
news:6447ee25.0410070127.2321f3f1@.posting.google.com...
> I want to check for duplicate data, if got duplicate data, then the
> table will be dropped, if dun have, the table will not be dropped, but
> the constraint and the indexed within the table will be dropped, and
> an mail notification will be send. Below is my syntax uwing stored
> procedure,when i run this stored procedure, it get an error message
> :"Incorrect syntax near 'PK_Rewards_CatalogProducts_CS' ", Can anyone
> tell me the correct way to drop the constraint and index?
> if not exists(select catalog_code, count(catalog_code) from rd_awards
> group
> by catalog_code having
> count(catalog_code) > 1)
> begin
> drop table [dbo].[Rewards_CatalogProducts_CS]
> end
>
> else
> if exists(select catalog_code, count(catalog_code) from rd_awards
> group
> by catalog_code having
> count(catalog_code) > 1)
> begin
> ALTER TABLE [dbo].[Rewards_CatalogProducts_CS]
> DROP CONSTRAINT [PK_Rewards_CatalogProducts_CS]
> GO
> ALTER TABLE [dbo].[Rewards_CatalogProducts_CS]
> DROP INDEX [idx_categorycode]
> GO
> ALTER TABLE [dbo].[Rewards_CatalogProducts_CS]
> DROP INDEX [idx_g_org_SupplierId]
>
> use master
> declare @.FROM NVARCHAR(4000),
> @.FROM_NAME NVARCHAR(4000),
> @.TO NVARCHAR(4000),
> @.CC NVARCHAR(4000),
> @.BCC NVARCHAR(4000),
> @.priority NVARCHAR(10),
> @.subject NVARCHAR(4000),
> @.message NVARCHAR(4000),
> @.type NVARCHAR(100),
> @.attachments NVARCHAR(4000),
> @.codepage INT,
> @.rc INT
> select @.FROM = N'sqlmail@.cyber-village.net',
> @.FROM_NAME = N'ChangMian',
> @.TO = N'tchangmian@.yahoo.com.sg',
> @.CC = N'changmian@.cyber-village.net',
> @.priority = N'High',
> @.subject = N'headache',
> @.message = N'&

---

Hello SQL Server SMTP SQL
> Mail

---

',
> @.type = N'text/html',
> @.attachments = N'',
> @.codepage = 0
> exec @.rc = master.dbo.xp_smtp_sendmail
> @.FROM = @.FROM,
> @.TO = @.TO,
> @.CC = @.CC,
> @.priority = @.priority,
> @.subject = @.subject,
> @.message = @.message,
> @.type = @.type,
> @.attachments = @.attachments,
> @.codepage = @.codepage,
> @.server = N'mail.cyber-village.net'
> select RC = @.rc
> end

how to drop index?

I want to check for duplicate data, if got duplicate data, then the
table will be dropped, if dun have, the table will not be dropped, but
the constraint and the indexed within the table will be dropped, and
an mail notification will be send. Below is my syntax uwing stored
procedure,when i run this stored procedure, it get an error message
:"Incorrect syntax near 'PK_Rewards_CatalogProducts_CS' ", Can anyone
tell me the correct way to drop the constraint and index?
if not exists(select catalog_code, count(catalog_code) from rd_awards
group
by catalog_code having
count(catalog_code) > 1)
begin
drop table [dbo].[Rewards_CatalogProducts_CS]
end
else
if exists(select catalog_code, count(catalog_code) from rd_awards
group
by catalog_code having
count(catalog_code) > 1)
begin
ALTER TABLE [dbo].[Rewards_CatalogProducts_CS]
DROP CONSTRAINT [PK_Rewards_CatalogProducts_CS]
GO
ALTER TABLE [dbo].[Rewards_CatalogProducts_CS]
DROP INDEX [idx_categorycode]
GO
ALTER TABLE [dbo].[Rewards_CatalogProducts_CS]
DROP INDEX [idx_g_org_SupplierId]
use master
declare @.FROM NVARCHAR(4000),
@.FROM_NAME NVARCHAR(4000),
@.TO NVARCHAR(4000),
@.CC NVARCHAR(4000),
@.BCC NVARCHAR(4000),
@.priority NVARCHAR(10),
@.subject NVARCHAR(4000),
@.message NVARCHAR(4000),
@.type NVARCHAR(100),
@.attachments NVARCHAR(4000),
@.codepage INT,
@.rc INT
select @.FROM = N'sqlmail@.cyber-village.net',
@.FROM_NAME = N'ChangMian',
@.TO = N'tchangmian@.yahoo.com.sg',
@.CC = N'changmian@.cyber-village.net',
@.priority = N'High',
@.subject = N'headache',
@.message = N'<HTML><H1>Hello SQL Server SMTP SQL
Mail</H1></HTML>',
@.type = N'text/html',
@.attachments = N'',
@.codepage = 0
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = @.FROM,
@.TO = @.TO,
@.CC = @.CC,
@.priority = @.priority,
@.subject = @.subject,
@.message = @.message,
@.type = @.type,
@.attachments = @.attachments,
@.codepage = @.codepage,
@.server = N'mail.cyber-village.net'
select RC = @.rc
end
HI!
Not the Alter Statement is the problem. The problem is that The BEGIN has no
coresponding END in your IF Statement, because you are dividing your script
in several batches with the GO Statement.
IF BEGIN END has to be in one Batch and with GO you are starting a new Batch
which is the unit of Parsing and Compilation for SQL Server. GO is not a
SQL Statement. It tells Query Analyzer, osql,... to seperate the script into
units which are send separately to SQL Server (see BOL)
Just remove the GO statements, then it should work. They are not necessary
in your case.
Cheers,
Herbert
"tchangmian" <tchangmian@.yahoo.com.sg> schrieb im Newsbeitrag
news:6447ee25.0410070127.2321f3f1@.posting.google.c om...
> I want to check for duplicate data, if got duplicate data, then the
> table will be dropped, if dun have, the table will not be dropped, but
> the constraint and the indexed within the table will be dropped, and
> an mail notification will be send. Below is my syntax uwing stored
> procedure,when i run this stored procedure, it get an error message
> :"Incorrect syntax near 'PK_Rewards_CatalogProducts_CS' ", Can anyone
> tell me the correct way to drop the constraint and index?
> if not exists(select catalog_code, count(catalog_code) from rd_awards
> group
> by catalog_code having
> count(catalog_code) > 1)
> begin
> drop table [dbo].[Rewards_CatalogProducts_CS]
> end
>
> else
> if exists(select catalog_code, count(catalog_code) from rd_awards
> group
> by catalog_code having
> count(catalog_code) > 1)
> begin
> ALTER TABLE [dbo].[Rewards_CatalogProducts_CS]
> DROP CONSTRAINT [PK_Rewards_CatalogProducts_CS]
> GO
> ALTER TABLE [dbo].[Rewards_CatalogProducts_CS]
> DROP INDEX [idx_categorycode]
> GO
> ALTER TABLE [dbo].[Rewards_CatalogProducts_CS]
> DROP INDEX [idx_g_org_SupplierId]
>
> use master
> declare @.FROM NVARCHAR(4000),
> @.FROM_NAME NVARCHAR(4000),
> @.TO NVARCHAR(4000),
> @.CC NVARCHAR(4000),
> @.BCC NVARCHAR(4000),
> @.priority NVARCHAR(10),
> @.subject NVARCHAR(4000),
> @.message NVARCHAR(4000),
> @.type NVARCHAR(100),
> @.attachments NVARCHAR(4000),
> @.codepage INT,
> @.rc INT
> select @.FROM = N'sqlmail@.cyber-village.net',
> @.FROM_NAME = N'ChangMian',
> @.TO = N'tchangmian@.yahoo.com.sg',
> @.CC = N'changmian@.cyber-village.net',
> @.priority = N'High',
> @.subject = N'headache',
> @.message = N'<HTML><H1>Hello SQL Server SMTP SQL
> Mail</H1></HTML>',
> @.type = N'text/html',
> @.attachments = N'',
> @.codepage = 0
> exec @.rc = master.dbo.xp_smtp_sendmail
> @.FROM = @.FROM,
> @.TO = @.TO,
> @.CC = @.CC,
> @.priority = @.priority,
> @.subject = @.subject,
> @.message = @.message,
> @.type = @.type,
> @.attachments = @.attachments,
> @.codepage = @.codepage,
> @.server = N'mail.cyber-village.net'
> select RC = @.rc
> end

how to Drop an index if exists

Hi all,

I come from MySQL.

In sqlserverce

I'm looking for an equivalent command..to

"DROP INDEX IF EXISTS inventory.idxItems"

or somthing similar so that I do not get an error or crash the C# app when I try to drop a nonexisting index..

also for droping a table.

Use can use the INFORMATION.SCHEMA veiws, both for tables and indexes, like this:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = ’MyUserTable’

or

SELECT INDEX_NAME
FROM INFORMATION_SCHEMA.INDEXES
WHERE INDEX_NAME = ’MyUserIndex’

For more information (not a lot though), see http://msdn2.microsoft.com/en-us/library/ms174156.aspx

How to drop all primary key of all tables in DB?

How to drop all primary key of all tables in DB?
After reading a document for explaning how to use clustered index, I knew
set all primary key to be clustered is not correct for me.
I would like to drop them. recreate a new nonclustered primary key and
create other clustered indexes.
Now I would like to know how to drop all primary key of all tables in DB by
T-SQL, because there are too many tables to drop primary key by hand.
Thanks
--UsingSQL2005DevFrank
declare @.pklist table
(
ident int identity(1, 1),
pkname sysname,
tablename sysname
)
insert into
@.pklist
(
pkname,
tablename
)
select
constraint_name,
table_name
from
information_schema.table_constraints
where
constraint_type = 'primary key'
set @.counter = @.@.rowcount
while @.counter > 0
begin
select @.constraint = pkname, @.table = tablename from @.pklist where ident =
@.counter
exec ('alter table [' + @.table + '] drop constraint [' + @.constraint + ']')
set @.counter = @.counter - 1
end
"Frank Lee" <Reply@.to.newsgroup> wrote in message
news:ezgjh5CEGHA.3528@.TK2MSFTNGP12.phx.gbl...
> How to drop all primary key of all tables in DB?
> After reading a document for explaning how to use clustered index, I knew
> set all primary key to be clustered is not correct for me.
> I would like to drop them. recreate a new nonclustered primary key and
> create other clustered indexes.
> Now I would like to know how to drop all primary key of all tables in DB
> by T-SQL, because there are too many tables to drop primary key by hand.
> Thanks
> --UsingSQL2005Dev
>|||Hi
You should extend Uri's solution so that all Foreign Keys that reference
your Primary Key is removed before trying to remove the Primary Key.
John
"Frank Lee" <Reply@.to.newsgroup> wrote in message
news:ezgjh5CEGHA.3528@.TK2MSFTNGP12.phx.gbl...
> How to drop all primary key of all tables in DB?
> After reading a document for explaning how to use clustered index, I knew
> set all primary key to be clustered is not correct for me.
> I would like to drop them. recreate a new nonclustered primary key and
> create other clustered indexes.
> Now I would like to know how to drop all primary key of all tables in DB
> by T-SQL, because there are too many tables to drop primary key by hand.
> Thanks
> --UsingSQL2005Dev
>|||I see. Thx.
"John Bell" <jbellnewsposts@.hotmail.com> glsD:eNvQsxGEGHA.3892@.TK2MSFTNGP10.phx.g
bl...
> Hi
> You should extend Uri's solution so that all Foreign Keys that reference
> your Primary Key is removed before trying to remove the Primary Key.
> John
> "Frank Lee" <Reply@.to.newsgroup> wrote in message
> news:ezgjh5CEGHA.3528@.TK2MSFTNGP12.phx.gbl...
>

How to drop all PKs on tables in database?

I have a list of 35 tables that need to drop the primary key index from in my database.

My problem is as follows for these 35 tables:

1. How can I get a list of all the primary keys for this subset of tables in my database
2. How can I drop just the PK for each of these tables?

I want an easy quick way to do this without having to manually do this for each of the 35 tables in my database. I dont want to do this for all tables just the subset.

Thanksdeclare @.Table varchar(100)
declare @.PK varchar(100)
declare cur cursor for select name,object_name(parent_obj) from sysobjects where xtype='PK' and object_name(parent_obj) in ('table1','table2',.....)
open cur
fetch next from cur into @.PK, @.Table
while @.@.fetch_status = 0
begin
exec ('alter table ' + @.Table + ' drop constraint ' + @.PK )
fetch next from cur into @.PK, @.Table
end
close cur
deallocate cur|||I think it would be safer to do this inside the loop:

print 'alter table ' + @.Table + ' drop constraint ' + @.PK

rather than this:

exec ('alter table ' + @.Table + ' drop constraint ' + @.PK )

that way, you can inspect the result for correctness, make sure you really want to execute it, etc.

when playing the sql-from-sql game, you should execute only after inspecting the result, IMO.|||the answer mention above would goes wrong if foreign keys are there so we need to drop all foreing keys first then apply above mention procedure
1 step first
drop all foreign keys relation ship
declare @.Table varchar(100)
declare @.FK varchar(100)
declare cur cursor for select name,object_name(parent_obj) from sysobjects where xtype='F' and object_name(parent_obj) in ('Table1','Table2',...)
open cur
fetch next from cur into @.FK, @.Table
while @.@.fetch_status = 0
begin
exec ('alter table ' + @.Table + ' drop constraint ' + @.FK )
fetch next from cur into @.FK, @.Table
end
close cur
deallocate cur
2. step second now drop all primary key relation ship
declare @.Table varchar(100)
declare @.PK varchar(100)
declare cur cursor for select name,object_name(parent_obj) from sysobjects where xtype='PK' and object_name(parent_obj) in ('table1','table2',.....)
open cur
fetch next from cur into @.PK, @.Table
while @.@.fetch_status = 0
begin
exec ('alter table ' + @.Table + ' drop constraint ' + @.PK )
fetch next from cur into @.PK, @.Table
end
close cur
deallocate cur|||right... we need to drop the FKs first.
but the code from greenindia is having the following problems

1) xtype for foriegn-key is "F" and not "FK"
2) who says that the tables with FKs belongs to the same set of that with PKs? since the same set of tables are used in the code
object_name(parent_obj) in ('Table1','Table2',...)

u need a trip from sysforeignkeys to trap the relation properly :rolleyes:|||you are my dear friend upalsen , xtype for foreign key should have been F instead of FK.|||anybody think of asking our friend why he would want to do this before we hand him a loaded gun?|||anybody think of asking our friend why he would want to do this before we hand him a loaded gun?

What would be the fun in that?|||Hi all,

Thanks for your help, however I cannot see the output of the print commands in SQL Server Query Analyzer when I run the cursor script:

declare @.Table varchar(100)
declare @.PK varchar(100)
declare cur cursor for select name,object_name(parent_obj) from sysobjects where xtype='P' and object_name(parent_obj) in ('table1', 'table2', 'table3')
open cur
fetch next from cur into @.PK, @.Table
while @.@.fetch_status = 0
begin
print ('alter table ' + @.Table + ' drop constraint ' + @.PK )
fetch next from cur into @.PK, @.Table
end
close cur
deallocate cur

print 'alter table ' + @.Table + ' drop constraint ' + @.PK|||does this query return anything? if not, there's your answer.

select name,object_name(parent_obj) from sysobjects where xtype='P' and object_name(parent_obj) in ('table1', 'table2', 'table3')