Remove identity property of a primary key

Hi,
I have a table with a column named ID as primary key, this column has
the identity property. This ID is referenced by some other tables as foreign
key.
Is there a way, I can use "alter table alter ID int not null...." TSQL
to remove this identity property?
Thanks!
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours Hardy1. drop FK
2. sp_rename table with identity to some other name
3. create table new with the same name without identity
4. insert new select * from old
5. create FK
6 drop old
"Hardy Wang" <hardywang@.hotmail.com> wrote in message
news:O7AqRnXrFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a table with a column named ID as primary key, this column has
> the identity property. This ID is referenced by some other tables as
> foreign key.
> Is there a way, I can use "alter table alter ID int not null...." TSQL
> to remove this identity property?
> Thanks!
> --
> WWW: http://hardywang.1accesshost.com
> ICQ: 3359839
> yours Hardy
>|||The only way is dropping the column. Try using EM if you really want to do
this. In EM, before saving changes, press button "save change script", third
from left to right in the tool bar. You will see what really EM does in orde
r
to accomplish this tak.
Example: (from northwind.orders)
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT FK_Orders_Shippers
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT FK_Orders_Employees
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT FK_Orders_Customers
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT DF_Orders_Freight
GO
CREATE TABLE dbo.Tmp_Orders
(
OrderID int NOT NULL,
CustomerID nchar(5) NULL,
EmployeeID int NULL,
OrderDate datetime NULL,
RequiredDate datetime NULL,
ShippedDate datetime NULL,
ShipVia int NULL,
Freight money NULL,
ShipName nvarchar(40) NULL,
ShipAddress nvarchar(60) NULL,
ShipCity nvarchar(15) NULL,
ShipRegion nvarchar(15) NULL,
ShipPostalCode nvarchar(10) NULL,
ShipCountry nvarchar(15) NULL
) ON [PRIMARY]
GO
DECLARE @.v sql_variant
SET @.v = N''
EXECUTE sp_addextendedproperty N'MS_Description', @.v, N'user', N'dbo',
N'table', N'Tmp_Orders', N'column', N'OrderID'
GO
ALTER TABLE dbo.Tmp_Orders ADD CONSTRAINT
DF_Orders_Freight DEFAULT (0) FOR Freight
GO
IF EXISTS(SELECT * FROM dbo.Orders)
EXEC('INSERT INTO dbo.Tmp_Orders (OrderID, CustomerID, EmployeeID,
OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName,
ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate,
ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion,
ShipPostalCode, ShipCountry FROM dbo.Orders (HOLDLOCK TABLOCKX)')
GO
ALTER TABLE dbo.[Order Details]
DROP CONSTRAINT FK_Order_Details_Orders
GO
DROP TABLE dbo.Orders
GO
EXECUTE sp_rename N'dbo.Tmp_Orders', N'Orders', 'OBJECT'
GO
ALTER TABLE dbo.Orders ADD CONSTRAINT
PK_Orders PRIMARY KEY CLUSTERED
(
OrderID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX CustomerID ON dbo.Orders
(
CustomerID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX CustomersOrders ON dbo.Orders
(
CustomerID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX EmployeeID ON dbo.Orders
(
EmployeeID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX EmployeesOrders ON dbo.Orders
(
EmployeeID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX OrderDate ON dbo.Orders
(
OrderDate
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX ShippedDate ON dbo.Orders
(
ShippedDate
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX ShippersOrders ON dbo.Orders
(
ShipVia
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX ShipPostalCode ON dbo.Orders
(
ShipPostalCode
) ON [PRIMARY]
GO
ALTER TABLE dbo.Orders WITH NOCHECK ADD CONSTRAINT
FK_Orders_Customers FOREIGN KEY
(
CustomerID
) REFERENCES dbo.Customers
(
CustomerID
)
GO
ALTER TABLE dbo.Orders WITH NOCHECK ADD CONSTRAINT
FK_Orders_Employees FOREIGN KEY
(
EmployeeID
) REFERENCES dbo.Employees
(
EmployeeID
)
GO
ALTER TABLE dbo.Orders WITH NOCHECK ADD CONSTRAINT
FK_Orders_Shippers FOREIGN KEY
(
ShipVia
) REFERENCES dbo.Shippers
(
ShipperID
)
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.[Order Details] WITH NOCHECK ADD CONSTRAINT
FK_Order_Details_Orders FOREIGN KEY
(
OrderID
) REFERENCES dbo.Orders
(
OrderID
)
GO
COMMIT
AMB
"Hardy Wang" wrote:

> Hi,
> I have a table with a column named ID as primary key, this column has
> the identity property. This ID is referenced by some other tables as forei
gn
> key.
> Is there a way, I can use "alter table alter ID int not null...." TSQ
L
> to remove this identity property?
> Thanks!
> --
> WWW: http://hardywang.1accesshost.com
> ICQ: 3359839
> yours Hardy
>
>