From: Jeff on
Hey

Sql Server 2005

In a table I added an extra column to a table:
alter table tableA add checked bit default 0

Then I give this column a value:
update tableA set checked = 0;

Now I create a stored procedure which contain 3 select using the column
'checked' in the where clause.

after running the stored procedure once, then I want to remove that extra
column I added to the table. But removing the column gives a 5474 error (an
constraint is dependant on the column 'checked')

I've read the http://support.microsoft.com/kb/816755 article about this
issue. It says that I need to drop the constraint first. Okay. but then I
must manually check what the constraint name is an drop it..

It takes time to check what the name on the constraint is and then type the
name.. etc..

So instead I would like to make script which delete this constrain no matter
name it would have. Then I can run this script without being sure what the
name of this constraint have.

I want to make this as fast as possible because some other program may start
to fail if this modification takes too long time

any suggestions?




From: Jamie Collins on
On Dec 14, 12:58 pm, "Jeff" <do...(a)spam.me> wrote:
> removing the column gives a 5474 error (an
> constraint is dependant on the column 'checked')
>
> I would like to make script which delete this constrain no matter
> name it would have.

See:

http://www.databasejournal.com/img/DropColumn.txt

The whole thing may be overkill for you (takes a relatively long time
to execute) so you could do as I did and just extract the relevant
code.

Jamie.

--

From: Dan Guzman on
> So instead I would like to make script which delete this constrain no
> matter name it would have. Then I can run this script without being sure
> what the name of this constraint have.

I suggest you specify a constraint name instead of relying on an
auto-generated name:

ALTER TABLE dbo.tableA
ADD checked bit NOT NULL
CONSTRAINT DF_tableA_checked DEFAULT 0;

ALTER TABLE dbo.tableA
DROP CONSTRAINT DF_tableA_checked;

ALTER TABLE dbo.tableA
DROP COLUMN checked;

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Jeff" <donot(a)spam.me> wrote in message
news:%23R8lWElPIHA.4476(a)TK2MSFTNGP06.phx.gbl...
> Hey
>
> Sql Server 2005
>
> In a table I added an extra column to a table:
> alter table tableA add checked bit default 0
>
> Then I give this column a value:
> update tableA set checked = 0;
>
> Now I create a stored procedure which contain 3 select using the column
> 'checked' in the where clause.
>
> after running the stored procedure once, then I want to remove that extra
> column I added to the table. But removing the column gives a 5474 error
> (an constraint is dependant on the column 'checked')
>
> I've read the http://support.microsoft.com/kb/816755 article about this
> issue. It says that I need to drop the constraint first. Okay. but then I
> must manually check what the constraint name is an drop it..
>
> It takes time to check what the name on the constraint is and then type
> the name.. etc..
>
> So instead I would like to make script which delete this constrain no
> matter name it would have. Then I can run this script without being sure
> what the name of this constraint have.
>
> I want to make this as fast as possible because some other program may
> start to fail if this modification takes too long time
>
> any suggestions?
>
>
>
>

From: Jamie Collins on
On Dec 14, 1:25 pm, "Dan Guzman" <guzma...(a)nospam-
online.sbcglobal.net> wrote:
> I suggest you specify a constraint name instead of relying on an
> auto-generated name:

Omitting a name for a DEFAULT is valid SQL-92 syntax, which would
explain why many people (me included) have done so.

BTW if SQL Server 2005 doesn't allow ALTER TABLE DROP COLUMN until
I've found and dropped the named DEFAULT 'constraint' (I don't think
SQL-92 would consider it a CONSTRAINT), does this mean SQL Server 2005
is non-compliant as regards SQL-92?

Jamie.

--

From: Aaron Bertrand [SQL Server MVP] on
>> I suggest you specify a constraint name instead of relying on an
>> auto-generated name:
>
> Omitting a name for a DEFAULT is valid SQL-92 syntax, which would
> explain why many people (me included) have done so.

"Valid" and "best practice" are two different things! :-)

A
 |  Next  |  Last
Pages: 1 2
Prev: db mail
Next: SQL server message