March 8, 2011 at 11:47 am
need to delete the columns in the table if they exist
CREATE TABLE [dbo].[tblServiceProvider](
[ProviderID] [int] NOT NULL,
[FinancialContactPrefix] [nvarchar](4) NULL,
[FinancialContact] [nvarchar](50) NULL,
[FinancialPosition] [nvarchar](50) NULL,
[FinancialPhone] [nvarchar](14) NULL,
[FinancialExtension] [nvarchar](4) NULL,
[FinancialFax] [nvarchar](14) NULL,
[FinancialEmail] [nvarchar](50) NULL,
[NewProvider] [bit] NOT NULL,
CONSTRAINT [PK_tblServiceProvider] PRIMARY KEY CLUSTERED
(
[ProviderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
These are the columns:
[FinancialContactPrefix] [nvarchar](4) NULL,
[FinancialContact] [nvarchar](50) NULL,
[FinancialPosition] [nvarchar](50) NULL,
[FinancialPhone] [nvarchar](14) NULL,
[FinancialExtension] [nvarchar](4) NULL,
[FinancialFax] [nvarchar](14) NULL,
[FinancialEmail] [nvarchar](50) NULL
Any help would be awesome.
March 8, 2011 at 12:01 pm
this should get you close. Just have to repeat each column you want to drop
if exists(
select * from syscolumns
where id = OBJECT_ID('table name')
and name = 'column name'
)
alter table
drop column [column name]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 8, 2011 at 12:53 pm
is this ok?
ALTER TABLE tblServiceProvider DROP COLUMN FinancialContactPrefix
GO
ALTER TABLE tblServiceProvider DROP COLUMN FinancialContact
GO
ALTER TABLE tblServiceProvider DROP COLUMN FinancialPosition
GO
ALTER TABLE tblServiceProvider DROP COLUMN FinancialPhone
GO
ALTER TABLE tblServiceProvider DROP COLUMN FinancialExtension
GO
ALTER TABLE tblServiceProvider DROP COLUMN FinancialFax
GO
ALTER TABLE tblServiceProvider DROP COLUMN FinancialEmail
GO
March 8, 2011 at 12:58 pm
that will work fine as long as the column exists. I put the exists check in to make sure they exist per your requirements.
...need to delete the columns in the table if they exist
If you know with absolute certainty they exist you can exclude the exists check.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 8, 2011 at 1:01 pm
nice job!
March 8, 2011 at 1:01 pm
nice job!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply