Drop table's primary key with knowing the constraint's name

Drop table's primary key with knowing the constraint name.
Based on some logic, my program needs to create a new primary key.
The problem is when the primary key was created, it was not given a name.
SQL Server assigned a name it to it.
ALTER TABLE t1
ADD PRIMARY KEY (id, name)
go
Contraint name: PK__term__1FCDBCEB
So how can I drop it without knowing the name?Try:
select
CONSTRAINT_NAME
from
INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
where
1 in (objectproperty (object_id(CONSTRAINT_NAME), 'CnstIsClustKey'),
objectproperty (object_id(CONSTRAINT_NAME), 'CnstIsNonclustKey'))
and TABLE_NAME = 't1'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:3EF84A74-A43D-4A7C-AC2A-1FB2FBC85B00@.microsoft.com...
Drop table's primary key with knowing the constraint name.
Based on some logic, my program needs to create a new primary key.
The problem is when the primary key was created, it was not given a name.
SQL Server assigned a name it to it.
ALTER TABLE t1
ADD PRIMARY KEY (id, name)
go
Contraint name: PK__term__1FCDBCEB
So how can I drop it without knowing the name?|||"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:3EF84A74-A43D-4A7C-AC2A-1FB2FBC85B00@.microsoft.com...
> Drop table's primary key with knowing the constraint name.
> Based on some logic, my program needs to create a new primary key.
> The problem is when the primary key was created, it was not given a name.
> SQL Server assigned a name it to it.
>
> ALTER TABLE t1
> ADD PRIMARY KEY (id, name)
> go
> Contraint name: PK__term__1FCDBCEB
> So how can I drop it without knowing the name?
>
Like this for example:
DECLARE @.pk_name NVARCHAR(256);
SET @.pk_name =
(SELECT QUOTENAME(constraint_name)
FROM information_schema.table_constraints
WHERE constraint_type = 'PRIMARY KEY'
AND table_schema = 'dbo'
AND table_name = 'table_name') ;
EXEC sp_rename @.pk_name, 'pk_table_name', 'OBJECT' ;
ALTER TABLE table_name DROP CONSTRAINT pk_table_name ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Drop table's primary key with knowing the constraint's name

Drop table's primary key with knowing the constraint name.
Based on some logic, my program needs to create a new primary key.
The problem is when the primary key was created, it was not given a name.
SQL Server assigned a name it to it.
ALTER TABLE t1
ADD PRIMARY KEY (id, name)
go
Contraint name: PK__term__1FCDBCEB
So how can I drop it without knowing the name?Try:
select
CONSTRAINT_NAME
from
INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
where
1 in (objectproperty (object_id(CONSTRAINT_NAME), 'CnstIsClustKey'),
objectproperty (object_id(CONSTRAINT_NAME), 'CnstIsNonclustKey'))
and TABLE_NAME = 't1'
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:3EF84A74-A43D-4A7C-AC2A-1FB2FBC85B00@.microsoft.com...
Drop table's primary key with knowing the constraint name.
Based on some logic, my program needs to create a new primary key.
The problem is when the primary key was created, it was not given a name.
SQL Server assigned a name it to it.
ALTER TABLE t1
ADD PRIMARY KEY (id, name)
go
Contraint name: PK__term__1FCDBCEB
So how can I drop it without knowing the name?|||"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:3EF84A74-A43D-4A7C-AC2A-1FB2FBC85B00@.microsoft.com...
> Drop table's primary key with knowing the constraint name.
> Based on some logic, my program needs to create a new primary key.
> The problem is when the primary key was created, it was not given a name.
> SQL Server assigned a name it to it.
>
> ALTER TABLE t1
> ADD PRIMARY KEY (id, name)
> go
> Contraint name: PK__term__1FCDBCEB
> So how can I drop it without knowing the name?
>
Like this for example:
DECLARE @.pk_name NVARCHAR(256);
SET @.pk_name = (SELECT QUOTENAME(constraint_name)
FROM information_schema.table_constraints
WHERE constraint_type = 'PRIMARY KEY'
AND table_schema = 'dbo'
AND table_name = 'table_name') ;
EXEC sp_rename @.pk_name, 'pk_table_name', 'OBJECT' ;
ALTER TABLE table_name DROP CONSTRAINT pk_table_name ;
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--sql

Drivers of Sqlserver 200

hi

I want to know file's list of SQLSERVER2000 Driver

I write a program , it connect to sqlserver2000 in Eternet and

client side of software install in every computer but i have to install sqlserver2000 in every client pc

how can i connect to SQLServer2000 Server (in a network) with out installing Sqlserver2000 ?

Tanks

Hi,

you just have to install the MDAC Components to access SQL Server.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Depending on your exact needs in addition to MDAC you may also want to try SQL Server Native Client, JDBC, or ADO.NET.

Check out http://msdn.microsoft.com/data for more info.

Driver

Hi All,
I’m developing a simple program which will connect to a SQL Server
2000, but when I install the SQL Server I got a message inform me that I
cannot complete, once I check the ODBC drivers I cannot find any of them.
I’m using a Win XP home edition, although I’ve tried to install the
MDAC it inform me that it cannot be run in such version.
Any help, Thanks in advance.
Kind Regards,
MohammadWhat version of MDAC did you try to install? Make sure it's
one of the later versions. You probably want to check your
MDAC installation with component checker as well. You can
download the tool and MDAC versions from:
http://msdn.microsoft.com/data/mdac...ds/default.aspx
-Sue
On Tue, 4 Oct 2005 04:25:02 -0700, "Mohammed"
<Mohammed@.discussions.microsoft.com> wrote:

>Hi All,
> Im developing a simple program which will connect to a SQL Server
>2000, but when I install the SQL Server I got a message inform me that I
>cannot complete, once I check the ODBC drivers I cannot find any of them.
> Im using a Win XP home edition, although Ive tried to install the
>MDAC it inform me that it cannot be run in such version.
> Any help, Thanks in advance.
>Kind Regards,
>Mohammad
>

Driver

Hi All,
I’m developing a simple program which will connect to a SQL Server
2000, but when I install the SQL Server I got a message inform me that I
cannot complete, once I check the ODBC drivers I cannot find any of them.
I’m using a Win XP home edition, although I’ve tried to install the
MDAC it inform me that it cannot be run in such version.
Any help, Thanks in advance.
Kind Regards,
Mohammad
What version of MDAC did you try to install? Make sure it's
one of the later versions. You probably want to check your
MDAC installation with component checker as well. You can
download the tool and MDAC versions from:
http://msdn.microsoft.com/data/mdac/...s/default.aspx
-Sue
On Tue, 4 Oct 2005 04:25:02 -0700, "Mohammed"
<Mohammed@.discussions.microsoft.com> wrote:

>Hi All,
> Im developing a simple program which will connect to a SQL Server
>2000, but when I install the SQL Server I got a message inform me that I
>cannot complete, once I check the ODBC drivers I cannot find any of them.
> Im using a Win XP home edition, although Ive tried to install the
>MDAC it inform me that it cannot be run in such version.
> Any help, Thanks in advance.
>Kind Regards,
>Mohammad
>