Cambio de tamaño de una columna

mandle,

No importa si lo haces por EM o por T-SQL, el script final debe ser el
mismo. Primero debes identificar todas las referencias hacia esta tabla -
columna, eliminar las referencias, eliminar la restriccion de clave primaria,
eliminar indices, modificar la columna en las tablas hijas (que la
referencian a ella), modificar la columna en la tabla en question, recrear la
clave primaria, recrear las restricciones de clave foranea, recrear indices,
etc. Si tienes alguna vista y/o funcion que la referencien, y estas fueron
creadas con la opcion WITH SCHEMABINDING, entonces tendras que eliminar estas
antes de poder cambiar algo en la tabla.

Te voy a pasar el script que crea EM, cuando trato de cambiar el tipo o el
tamanio en la bd northwind, tabla customers, columna customerid. Esto no
quiere decir que el tuyo sera igual, pero puedes guiarte por el.

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
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.CustomerCustomerDemo
DROP CONSTRAINT FK_CustomerCustomerDemo
GO
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
CREATE TABLE dbo.Tmp_Customers
(
CustomerID nchar(10) NOT NULL,
CompanyName nvarchar(40) NOT NULL,
ContactName nvarchar(30) NULL,
ContactTitle nvarchar(30) NULL,
Address nvarchar(60) NULL,
City nvarchar(15) NULL,
Region nvarchar(15) NULL,
PostalCode nvarchar(10) NULL,
Country nvarchar(15) NULL,
Phone nvarchar(24) NULL,
Fax nvarchar(24) NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.Customers)
EXEC('INSERT INTO dbo.Tmp_Customers (CustomerID, CompanyName, ContactName,
ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax)
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City,
Region, PostalCode, Country, Phone, Fax FROM dbo.Customers TABLOCKX')
GO
ALTER TABLE dbo.Orders
DROP CONSTRAINT FK_Orders_Customers
GO
ALTER TABLE dbo.CustomerCustomerDemo
DROP CONSTRAINT FK_CustomerCustomerDemo_Customers
GO
DROP TABLE dbo.Customers
GO
EXECUTE sp_rename N'dbo.Tmp_Customers', N'Customers', 'OBJECT'
GO
ALTER TABLE dbo.Customers ADD CONSTRAINT
PK_Customers PRIMARY KEY CLUSTERED
(
CustomerID
) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX City ON dbo.Customers
(
City
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX CompanyName ON dbo.Customers
(
CompanyName
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX PostalCode ON dbo.Customers
(
PostalCode
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX Region ON dbo.Customers
(
Region
) ON [PRIMARY]
GO
GRANT REFERENCES ON dbo.Customers TO public AS dbo
GRANT SELECT ON dbo.Customers TO public AS dbo
GRANT UPDATE ON dbo.Customers TO public AS dbo
GRANT INSERT ON dbo.Customers TO public AS dbo
GRANT DELETE ON dbo.Customers TO public AS dbo
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_CustomerCustomerDemo
(
CustomerID nchar(10) NOT NULL,
CustomerTypeID nchar(10) NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.CustomerCustomerDemo)
EXEC('INSERT INTO dbo.Tmp_CustomerCustomerDemo (CustomerID, CustomerTypeID)
SELECT CustomerID, CustomerTypeID FROM dbo.CustomerCustomerDemo TABLOCKX')
GO
DROP TABLE dbo.CustomerCustomerDemo
GO
EXECUTE sp_rename N'dbo.Tmp_CustomerCustomerDemo', N'CustomerCustomerDemo',
'OBJECT'
GO
ALTER TABLE dbo.CustomerCustomerDemo ADD CONSTRAINT
PK_CustomerCustomerDemo PRIMARY KEY NONCLUSTERED
(
CustomerID,
CustomerTypeID
) ON [PRIMARY]

GO
ALTER TABLE dbo.CustomerCustomerDemo WITH NOCHECK ADD CONSTRAINT
FK_CustomerCustomerDemo FOREIGN KEY
(
CustomerTypeID
) REFERENCES dbo.CustomerDemographics
(
CustomerTypeID
)
GO
ALTER TABLE dbo.CustomerCustomerDemo WITH NOCHECK ADD CONSTRAINT
FK_CustomerCustomerDemo_Customers FOREIGN KEY
(
CustomerID
) REFERENCES dbo.Customers
(
CustomerID
)
GO
GRANT REFERENCES ON dbo.CustomerCustomerDemo TO public AS dbo
GRANT SELECT ON dbo.CustomerCustomerDemo TO public AS dbo
GRANT UPDATE ON dbo.CustomerCustomerDemo TO public AS dbo
GRANT INSERT ON dbo.CustomerCustomerDemo TO public AS dbo
GRANT DELETE ON dbo.CustomerCustomerDemo TO public AS dbo
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT DF_Orders_Freight
GO
CREATE TABLE dbo.Tmp_Orders
(
OrderID int NOT NULL IDENTITY (1, 1),
CustomerID nchar(10) 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
ALTER TABLE dbo.Tmp_Orders ADD CONSTRAINT
DF_Orders_Freight DEFAULT (0) FOR Freight
GO
SET IDENTITY_INSERT dbo.Tmp_Orders ON
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 TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_Orders OFF
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
GRANT REFERENCES ON dbo.Orders TO public AS dbo
GRANT SELECT ON dbo.Orders TO public AS dbo
GRANT UPDATE ON dbo.Orders TO public AS dbo
GRANT INSERT ON dbo.Orders TO public AS dbo
GRANT DELETE ON dbo.Orders TO public AS dbo
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
go


AMB


"mandle" wrote:

Hola quiero hacer un script que me permita cambiar el tamaño de una columna,
el problema es que la columna es llave, es una tabla principal y esta
referenciada por dos tablas.

Cómo puedo hacer para cambiarle el tamaño de la columna?

Debido a que estoy utilizando MSDE y no tengo acceso a un enterprise
manager, no lo puedo hacer desde desde el EM...

Gracias por su ayuda..

¿todo eso?
EM te amo, perdon por haberte insultado tantas veces...

Guido :)

"Alejandro Mesa" escribió en el
mensaje news:

mandle,

No importa si lo haces por EM o por T-SQL, el script final debe ser el
mismo. Primero debes identificar todas las referencias hacia esta tabla -
columna, eliminar las referencias, eliminar la restriccion de clave

primaria,

eliminar indices, modificar la columna en las tablas hijas (que la
referencian a ella), modificar la columna en la tabla en question, recrear

la

clave primaria, recrear las restricciones de clave foranea, recrear

indices,

etc. Si tienes alguna vista y/o funcion que la referencien, y estas fueron
creadas con la opcion WITH SCHEMABINDING, entonces tendras que eliminar

estas

antes de poder cambiar algo en la tabla.

Te voy a pasar el script que crea EM, cuando trato de cambiar el tipo o el
tamanio en la bd northwind, tabla customers, columna customerid. Esto no
quiere decir que el tuyo sera igual, pero puedes guiarte por el.

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
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.CustomerCustomerDemo
DROP CONSTRAINT FK_CustomerCustomerDemo
GO
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
CREATE TABLE dbo.Tmp_Customers
(
CustomerID nchar(10) NOT NULL,
CompanyName nvarchar(40) NOT NULL,
ContactName nvarchar(30) NULL,
ContactTitle nvarchar(30) NULL,
Address nvarchar(60) NULL,
City nvarchar(15) NULL,
Region nvarchar(15) NULL,
PostalCode nvarchar(10) NULL,
Country nvarchar(15) NULL,
Phone nvarchar(24) NULL,
Fax nvarchar(24) NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.Customers)
EXEC('INSERT INTO dbo.Tmp_Customers (CustomerID, CompanyName, ContactName,
ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax)
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City,
Region, PostalCode, Country, Phone, Fax FROM dbo.Customers TABLOCKX')
GO
ALTER TABLE dbo.Orders
DROP CONSTRAINT FK_Orders_Customers
GO
ALTER TABLE dbo.CustomerCustomerDemo
DROP CONSTRAINT FK_CustomerCustomerDemo_Customers
GO
DROP TABLE dbo.Customers
GO
EXECUTE sp_rename N'dbo.Tmp_Customers', N'Customers', 'OBJECT'
GO
ALTER TABLE dbo.Customers ADD CONSTRAINT
PK_Customers PRIMARY KEY CLUSTERED
(
CustomerID
) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX City ON dbo.Customers
(
City
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX CompanyName ON dbo.Customers
(
CompanyName
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX PostalCode ON dbo.Customers
(
PostalCode
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX Region ON dbo.Customers
(
Region
) ON [PRIMARY]
GO
GRANT REFERENCES ON dbo.Customers TO public AS dbo
GRANT SELECT ON dbo.Customers TO public AS dbo
GRANT UPDATE ON dbo.Customers TO public AS dbo
GRANT INSERT ON dbo.Customers TO public AS dbo
GRANT DELETE ON dbo.Customers TO public AS dbo
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_CustomerCustomerDemo
(
CustomerID nchar(10) NOT NULL,
CustomerTypeID nchar(10) NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.CustomerCustomerDemo)
EXEC('INSERT INTO dbo.Tmp_CustomerCustomerDemo (CustomerID,

CustomerTypeID)

SELECT CustomerID, CustomerTypeID FROM dbo.CustomerCustomerDemo TABLOCKX')
GO
DROP TABLE dbo.CustomerCustomerDemo
GO
EXECUTE sp_rename N'dbo.Tmp_CustomerCustomerDemo',

N'CustomerCustomerDemo',

'OBJECT'
GO
ALTER TABLE dbo.CustomerCustomerDemo ADD CONSTRAINT
PK_CustomerCustomerDemo PRIMARY KEY NONCLUSTERED
(
CustomerID,
CustomerTypeID
) ON [PRIMARY]

GO
ALTER TABLE dbo.CustomerCustomerDemo WITH NOCHECK ADD CONSTRAINT
FK_CustomerCustomerDemo FOREIGN KEY
(
CustomerTypeID
) REFERENCES dbo.CustomerDemographics
(
CustomerTypeID
)
GO
ALTER TABLE dbo.CustomerCustomerDemo WITH NOCHECK ADD CONSTRAINT
FK_CustomerCustomerDemo_Customers FOREIGN KEY
(
CustomerID
) REFERENCES dbo.Customers
(
CustomerID
)
GO
GRANT REFERENCES ON dbo.CustomerCustomerDemo TO public AS dbo
GRANT SELECT ON dbo.CustomerCustomerDemo TO public AS dbo
GRANT UPDATE ON dbo.CustomerCustomerDemo TO public AS dbo
GRANT INSERT ON dbo.CustomerCustomerDemo TO public AS dbo
GRANT DELETE ON dbo.CustomerCustomerDemo TO public AS dbo
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT DF_Orders_Freight
GO
CREATE TABLE dbo.Tmp_Orders
(
OrderID int NOT NULL IDENTITY (1, 1),
CustomerID nchar(10) 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
ALTER TABLE dbo.Tmp_Orders ADD CONSTRAINT
DF_Orders_Freight DEFAULT (0) FOR Freight
GO
SET IDENTITY_INSERT dbo.Tmp_Orders ON
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 TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_Orders OFF
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
GRANT REFERENCES ON dbo.Orders TO public AS dbo
GRANT SELECT ON dbo.Orders TO public AS dbo
GRANT UPDATE ON dbo.Orders TO public AS dbo
GRANT INSERT ON dbo.Orders TO public AS dbo
GRANT DELETE ON dbo.Orders TO public AS dbo
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
go


AMB


"mandle" wrote:

> Hola quiero hacer un script que me permita cambiar el tamaño de una

columna,

> el problema es que la columna es llave, es una tabla principal y esta
> referenciada por dos tablas.
>
> Cómo puedo hacer para cambiarle el tamaño de la columna?
>
> Debido a que estoy utilizando MSDE y no tengo acceso a un enterprise
> manager, no lo puedo hacer desde desde el EM...
>
> Gracias por su ayuda..
>
>
>