ALTER TABLE ALTER COLUMN

For some reason, the SQL Server I am accessing indicates the following
script executed just fine, but when I look at the database design in
Enterprise Manager, it indicates the design has not been modified at
all. Any suggestions as to why this is happening?
USE [dbaseName]
ALTER TABLE [tableName]
ALTER COLUMN [columnName] int NULL
goDid you refresh EM?
"Mike London" <mikey@.email.unc.edu> wrote in message
news:eewGDcmJFHA.4012@.TK2MSFTNGP09.phx.gbl...
> For some reason, the SQL Server I am accessing indicates the following
> script executed just fine, but when I look at the database design in
> Enterprise Manager, it indicates the design has not been modified at
> all. Any suggestions as to why this is happening?
> USE [dbaseName]
> ALTER TABLE [tableName]
> ALTER COLUMN [columnName] int NULL
> go|||Yep, even closed it out and loaded it again, no dice. Even checked to
make sure I was in the development version (where I was applying it).
ChrisR wrote:
> Did you refresh EM?
> "Mike London" <mikey@.email.unc.edu> wrote in message
> news:eewGDcmJFHA.4012@.TK2MSFTNGP09.phx.gbl...
>>For some reason, the SQL Server I am accessing indicates the following
>>script executed just fine, but when I look at the database design in
>>Enterprise Manager, it indicates the design has not been modified at
>>all. Any suggestions as to why this is happening?
>>USE [dbaseName]
>>ALTER TABLE [tableName]
>>ALTER COLUMN [columnName] int NULL
>>go
>
>|||What does Query Analyzer say when you say sp_help tablename?
(In other words, you shouldn't care what Enterprise Manager is "informing"
you with...)
http://www.aspfaq.com/2455
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Mike London" <mikey@.email.unc.edu> wrote in message
news:u$Z2TpmJFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Yep, even closed it out and loaded it again, no dice. Even checked to
> make sure I was in the development version (where I was applying it).
> ChrisR wrote:
> > Did you refresh EM?
> >
> > "Mike London" <mikey@.email.unc.edu> wrote in message
> > news:eewGDcmJFHA.4012@.TK2MSFTNGP09.phx.gbl...
> >
> >>For some reason, the SQL Server I am accessing indicates the following
> >>script executed just fine, but when I look at the database design in
> >>Enterprise Manager, it indicates the design has not been modified at
> >>all. Any suggestions as to why this is happening?
> >>
> >>USE [dbaseName]
> >>
> >>ALTER TABLE [tableName]
> >>ALTER COLUMN [columnName] int NULL
> >>go
> >
> >
> >|||If you sp_who for the table, what does it say?
"Mike London" <mikey@.email.unc.edu> wrote in message
news:u$Z2TpmJFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Yep, even closed it out and loaded it again, no dice. Even checked to
> make sure I was in the development version (where I was applying it).
> ChrisR wrote:
> > Did you refresh EM?
> >
> > "Mike London" <mikey@.email.unc.edu> wrote in message
> > news:eewGDcmJFHA.4012@.TK2MSFTNGP09.phx.gbl...
> >
> >>For some reason, the SQL Server I am accessing indicates the following
> >>script executed just fine, but when I look at the database design in
> >>Enterprise Manager, it indicates the design has not been modified at
> >>all. Any suggestions as to why this is happening?
> >>
> >>USE [dbaseName]
> >>
> >>ALTER TABLE [tableName]
> >>ALTER COLUMN [columnName] int NULL
> >>go
> >
> >
> >|||WHOOPS, sorry lemme check...Ignore the last reply ;)
Aaron [SQL Server MVP] wrote:
> What does Query Analyzer say when you say sp_help tablename?
> (In other words, you shouldn't care what Enterprise Manager is "informing"
> you with...)
> http://www.aspfaq.com/2455
>|||It says Command executed successfully, the normal text .
Aaron [SQL Server MVP] wrote:
> What does Query Analyzer say when you say sp_help tablename?
> (In other words, you shouldn't care what Enterprise Manager is "informing"
> you with...)
> http://www.aspfaq.com/2455
>|||I assume that is sp_help. The sp_help seems to not show a column for
default values in it, unless it is named something else,.... either
way, it is not showing ANY default values in the display.
ChrisR wrote:
> If you sp_who for the table, what does it say?
>
> "Mike London" <mikey@.email.unc.edu> wrote in message
> news:u$Z2TpmJFHA.3336@.TK2MSFTNGP10.phx.gbl...
>>Yep, even closed it out and loaded it again, no dice. Even checked to
>>make sure I was in the development version (where I was applying it).
>>ChrisR wrote:
>>
>>Did you refresh EM?
>>"Mike London" <mikey@.email.unc.edu> wrote in message
>>news:eewGDcmJFHA.4012@.TK2MSFTNGP09.phx.gbl...
>>
>>For some reason, the SQL Server I am accessing indicates the following
>>script executed just fine, but when I look at the database design in
>>Enterprise Manager, it indicates the design has not been modified at
>>all. Any suggestions as to why this is happening?
>>USE [dbaseName]
>>ALTER TABLE [tableName]
>>ALTER COLUMN [columnName] int NULL
>>go
>>
>|||This is the first time you've mentioned that you added a default value. Go
back to your original post, it says you wrote:
USE [dbaseName]
ALTER TABLE [tableName]
ALTER COLUMN [columnName] int NULL
go
Where is the default value there?
Anyway, if you did something different than the above, then you need to look
elsewhere, as I don't think sp_help brings back defaults:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tableName'
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Mike London" <mikey@.email.unc.edu> wrote in message
news:O6r8j#mJFHA.3340@.TK2MSFTNGP14.phx.gbl...
> I assume that is sp_help. The sp_help seems to not show a column for
> default values in it, unless it is named something else,.... either
> way, it is not showing ANY default values in the display.|||Sorry I was not clear. I am trying to REMOVE default value settings
that are currently in the Dbase, that is why they are not in the
original script.
I have a table in production (which I cannot modify the design directly,
the DBA needs to do that) which has defaults in it. I want to remove
those defaults from the column. I tried the script I posted originally
and it says it ran, but did NOT clear the defaults.
Does that clarify?
Sorry,
Mike
Aaron [SQL Server MVP] wrote:
> This is the first time you've mentioned that you added a default value. Go
> back to your original post, it says you wrote:
> USE [dbaseName]
> ALTER TABLE [tableName]
> ALTER COLUMN [columnName] int NULL
> go
> Where is the default value there?
> Anyway, if you did something different than the above, then you need to look
> elsewhere, as I don't think sp_help brings back defaults:
> SELECT * FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'tableName'
>|||> I have a table in production (which I cannot modify the design directly,
> the DBA needs to do that) which has defaults in it. I want to remove
> those defaults from the column. I tried the script I posted originally
> and it says it ran, but did NOT clear the defaults.
And I wouldn't expect it to. All you did was alter the column, your script
didn't mention anything about the default that is associated with the
column.
This will generate the statement necessary to drop a default constraint:
DECLARE @.tbl NVARCHAR(64), @.col NVARCHAR(64)
SET @.tbl = N'tableName'
SET @.col = N'columnName'
SELECT N'ALTER TABLE '+@.tbl
+ ' DROP CONSTRAINT '+so.name
FROM syscolumns sc INNER JOIN sysobjects so
ON sc.cdefault = so.id
WHERE sc.id=OBJECT_ID(@.tbl)
AND sc.name = @.col|||Removing a default is done by removing the default constraint:
ALTER TABLE tblname
DROP CONSTRAINT cnstrname
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Mike London" <mikey@.email.unc.edu> wrote in message news:%23CA8KJnJFHA.656@.TK2MSFTNGP14.phx.gbl...
> Sorry I was not clear. I am trying to REMOVE default value settings that are currently in the
> Dbase, that is why they are not in the original script.
> I have a table in production (which I cannot modify the design directly, the DBA needs to do that)
> which has defaults in it. I want to remove those defaults from the column. I tried the script I
> posted originally and it says it ran, but did NOT clear the defaults.
> Does that clarify?
> Sorry,
> Mike
>
> Aaron [SQL Server MVP] wrote:
>> This is the first time you've mentioned that you added a default value. Go
>> back to your original post, it says you wrote:
>> USE [dbaseName]
>> ALTER TABLE [tableName]
>> ALTER COLUMN [columnName] int NULL
>> go
>> Where is the default value there?
>> Anyway, if you did something different than the above, then you need to look
>> elsewhere, as I don't think sp_help brings back defaults:
>> SELECT * FROM INFORMATION_SCHEMA.COLUMNS
>> WHERE TABLE_NAME = 'tableName'|||thank you!
Aaron [SQL Server MVP] wrote:
>>I have a table in production (which I cannot modify the design directly,
>>the DBA needs to do that) which has defaults in it. I want to remove
>>those defaults from the column. I tried the script I posted originally
>>and it says it ran, but did NOT clear the defaults.
>
> And I wouldn't expect it to. All you did was alter the column, your script
> didn't mention anything about the default that is associated with the
> column.
> This will generate the statement necessary to drop a default constraint:
> DECLARE @.tbl NVARCHAR(64), @.col NVARCHAR(64)
> SET @.tbl = N'tableName'
> SET @.col = N'columnName'
> SELECT N'ALTER TABLE '+@.tbl
> + ' DROP CONSTRAINT '+so.name
> FROM syscolumns sc INNER JOIN sysobjects so
> ON sc.cdefault = so.id
> WHERE sc.id=OBJECT_ID(@.tbl)
> AND sc.name = @.col
>
>