drop primary key - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

drop primary key

Author

Topic

pnpsql
Posting Yak Master

246 Posts

Posted - 2011-10-08 : 01:35:05

hi members , i need to drop a primary key from a table but i dont know the name of that primary key, i am not able to get the name of that primary key , how can i remove this primary key because i need to change data type of that column and again create primary key .please help...

challenge everything

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-08 : 01:37:11

sp_helpindex <table name>

KH
[spoiler]Time is always against us[/spoiler]

pnpsql
Posting Yak Master

246 Posts

Posted - 2011-10-08 : 01:43:49

but i have no acces of server , i need to send script only

challenge everything

sql-programmers
Posting Yak Master

190 Posts

Posted - 2011-10-08 : 01:52:42

Use sp_help <tablename>.
We get all the details about the table..,

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

pnpsql
Posting Yak Master

246 Posts

Posted - 2011-10-08 : 02:25:40

sp_help gives all details , its ok but how can i see details and use it when i have no server access
, i need to send a script that drop primary key and then change data type of column and again create a primary key..please help..

challenge everything

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-08 : 03:18:14

you can do like


select tc.constraint_name,tc.table_name,
stuff((select ',' + column_name  from information_schema.constraint_column_usage  where constraint_name = tc.constraint_name for xml path('')),1,1,'')
from information_schema.table_constraints tc
WHERE tc.TABLE_NAME = 'Your table'
AND tc.TABLE_SCHEMA ='your schema'
AND tc.CONSTRAINT_TYPE='PRIMARY KEY'

this will give you name of pk constraint, table name and columns involved

then use ALTER TABLE <tablename> DROP CONSTRAINT...
to drop it

then change the columns datatype and recreate the primary key after adding new column also to column list.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources