drop view - 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

SQL Server 2008 Forums

Other SQL Server 2008 Topics

drop view

Author

Topic

nord
Posting Yak Master

126 Posts

Posted - 2012-05-14 : 15:58:21

Hi,
I need make alter column:
alter table dbo.STA_IB_SalesDownloadHZ
alter column STA_IBSDHZ_LayawayNumber bigint
from int to bigint,but i cant because i have relentship in view
i need write script to customer to run for another machine:
1.drop view
2.alter column
3.create view
how i can make this script?
thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-05-14 : 16:08:45

You practically just posted it. What problem are you having?

DROP VIEW ViewName
GO
ALTER TABLE...
GO
CREATE VIEW ...
GO

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

nord
Posting Yak Master

126 Posts

Posted - 2012-05-14 : 16:11:54

--/****** Object: Table [dbo].[HZ_vSalesInboundErrors] Script Date: 05/10/2012 08:25:19 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HZ_vSalesInboundErrors]') AND type in (N'U'))
DROP view dbo.HZ_vSalesInboundErrors
else

/****** Object: Table [dbo].[STA_IB_SalesDownloadHZ] Script Date: 05/10/2012 08:25:19 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[STA_IB_SalesDownloadHZ]') AND type in (N'U'))
alter table dbo.STA_IB_SalesDownloadHZ
alter column STA_IBSDHZ_LayawayNumber bigint

else
begin
CREATE TABLE [dbo].[STA_IB_SalesDownloadHZ](
[STA_IBSDHZ_SequenceNumber] [int] IDENTITY(1,1) NOT NULL,
[STA_IBSDHZ_SessionNumber] [int] NOT NULL,
[STA_IBSDHZ_BrandName] [varchar](100) NULL,
[STA_IBSDHZ_FileName] [varchar](100) NULL,
[STA_IBSDHZ_Linenumber] [int] NOT NULL,
[STA_IBSDHZ_Tsid] [datetime] NOT NULL,
[STA_IBSDHZ_Status] [char](1) NULL,
[STA_IBSDHZ_StoreCode] [varchar](10) NULL,
[STA_IBSDHZ_RegisterNumber] [char](3) NULL,
[STA_IBSDHZ_TransactionNumber] [int] NULL,
[STA_IBSDHZ_TransactionDate] [char](8) NULL,
[STA_IBSDHZ_TransactionTime] [char](6) NULL,
[STA_IBSDHZ_TransactionSequenceNumber] [int] NULL,
[STA_IBSDHZ_LayawayNumber] [bigint] NULL,
[STA_IBSDHZ_LayawayPaidtoDate] [numeric](12, 2) NULL,
[STA_IBSDHZ_LayawayBalanceDue] [numeric](12, 2) NULL,
[STA_IBSDHZ_ReferenceStoreCode] [varchar](10) NULL,
[STA_IBSDHZ_HeaderType] [varchar](10) NULL,
[STA_IBSDHZ_OpenCloseRegisterSessionNumber] [int] NULL,
[STA_IBSDHZ_StaffSalespersonCode] [varchar](6) NULL,
[STA_IBSDHZ_LineType] [varchar](10) NULL,
[STA_IBSDHZ_ItemType] [varchar](20) NULL,
[STA_IBSDHZ_ProductCode] [varchar](25) NULL,
[STA_IBSDHZ_Quantity] [numeric](9, 2) NULL,
[STA_IBSDHZ_UnitPrice] [numeric](12, 2) NULL,
[STA_IBSDHZ_ClientCode] [varchar](20) NULL,
[STA_IBSDHZ_ClientName] [varchar](40) NULL,
[STA_IBSDHZ_ClientFirstName] [varchar](30) NULL,
[STA_IBSDHZ_ClientTelephone] [varchar](13) NULL,
[STA_IBSDHZ_LayawaySequenceNumber] [int] NULL,
[STA_IBSDHZ_Ref_TransNum] [int] NULL,
[STA_IBSDHZ_Ref_TransactionDate] [datetime] NULL,
[STA_IBSDHZ_RetailPrice] [numeric](12, 2) NULL,
[STA_IBSDHZ_TaxRegionID] [int] NULL,
[STA_IBSDHZ_TaxCategoryID] [int] NULL,
[STA_IBSDHZ_TaxAmount] [numeric](16, 6) NULL,
[STA_IBSDHZ_RCPT_NOTE1] [varchar](50) NULL,
CONSTRAINT [pk_sta_ib_salesdownloadhz] PRIMARY KEY NONCLUSTERED
(
[STA_IBSDHZ_SequenceNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[STA_IB_SalesDownloadHZ] ADD CONSTRAINT [DF__STA_IBSDHZ__4560CAFB] DEFAULT (getdate()) FOR [STA_IBSDHZ_Tsid]
ALTER TABLE [dbo].[STA_IB_SalesDownloadHZ] ADD CONSTRAINT [DF__STA_IBSDHZ__4654EF34] DEFAULT ('N') FOR [STA_IBSDHZ_Status]
end

errors:Msg 5074, Level 16, State 1, Line 8
The object 'HZ_vSalesInboundErrors' is dependent on column 'STA_IBSDHZ_LayawayNumber'.
Msg 4922, Level 16, State 9, Line 8
ALTER TABLE ALTER COLUMN STA_IBSDHZ_LayawayNumber failed because one or more objects access this column.
Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-05-14 : 16:28:33

Add GOs in there. Hopefully the parser will allow it after you add them like in my example. If not, you may need to use dynamic SQL for this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

nord
Posting Yak Master

126 Posts

Posted - 2012-05-14 : 16:34:12

Thnks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources