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 2005 Forums
 Transact-SQL (2005)
 BOL is Wrong?

Author  Topic 

X002548
Not Just a Number

15586 Posts

Posted - 2009-02-17 : 17:39:39
This is the third time I cut and pasted some bol code and I get errors...what's up

I tried this from bol, which doesn't work

ALTER TABLE MyCustomers ALTER COLUMN CompanyName DROP DEFAULT

And SSMS generates this syntax, which does work

ALTER TABLE dbo.RHO_Type
DROP CONSTRAINT DF__RHO_Type__ADD_Dt__112C2E87

What's up?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-17 : 17:50:04
Which version of Books Online are you using?
quote:
ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name 
{
ALTER COLUMN column_name
{
[ type_schema_name. ] type_name [ ( { precision [ , scale ]
| max | xml_schema_collection } ) ]
[ COLLATE collation_name ]
[ SPARSE | NULL | NOT NULL ]
| {ADD | DROP }
{ ROWGUIDCOL | PERSISTED | NOT FOR REPLICATION | SPARSE }
}
| [ WITH { CHECK | NOCHECK } ]

| ADD
{
<column_definition>
| <computed_column_definition>
| <table_constraint>
| <column_set_definition>
} [ ,...n ]

| DROP
{
[ CONSTRAINT ] constraint_name
[ WITH ( <drop_clustered_constraint_option> [ ,...n ] ) ]
| COLUMN column_name
} [ ,...n ]

| [ WITH { CHECK | NOCHECK } ] { CHECK | NOCHECK } CONSTRAINT
{ ALL | constraint_name [ ,...n ] }

| { ENABLE | DISABLE } TRIGGER
{ ALL | trigger_name [ ,...n ] }

| { ENABLE | DISABLE } CHANGE_TRACKING
[ WITH ( TRACK_COLUMNS_UPDATED = { ON | OFF } ) ]

| SWITCH [ PARTITION source_partition_number_expression ]
TO target_table
[ PARTITION target_partition_number_expression ]

| SET ( FILESTREAM_ON = { partition_scheme_name | filegroup |
"default" | "NULL" } )

| REBUILD
[ [PARTITION = ALL]
[ WITH ( <rebuild_option> [ ,...n ] ) ]
| [ PARTITION = partition_number
[ WITH ( <single_partition_rebuild_option> [ ,...n ] ) ]
]
]

| (<table_option>)
}
[ ; ]

<column_set_definition> ::=
column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS

<drop_clustered_constraint_option> ::=
{
MAXDOP = max_degree_of_parallelism
| ONLINE = {ON | OFF }
| MOVE TO { partition_scheme_name ( column_name ) | filegroup
| "default" }
}
<table_option> ::=
{
SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )
}

<single_partition_rebuild__option> ::=
{
SORT_IN_TEMPDB = { ON | OFF }
| MAXDOP = max_degree_of_parallelism
| DATA_COMPRESSION = { NONE | ROW | PAGE} }
}




E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2009-02-17 : 17:56:11
The one that I hit help for in SSMS?

Is there a difference?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-17 : 17:56:18
All references to the first syntax is related to Compact Edition of Microsoft SQL Server.



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-17 : 18:05:11
Make sure your BOL is up to date. The RTM version has been updated a few times already.

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

Subscribe to my blog
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2009-02-17 : 18:08:56
ok...how do I do that?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-17 : 18:12:55
I go to the Microsoft site for my downloads.

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

Subscribe to my blog
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2009-02-17 : 18:22:26
link? I was being lazy....alrighty then

thx anyway

Just curious also as to if anyone else saw bogus examples....pretty bush league



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2009-02-17 : 18:54:53
OK...just to get to the point

Where do I find the DEFAULT constraint of a column?

Is it in an INFORMATION_SCHEMA view somewhere?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-02-17 : 22:25:16
This seems to work in SQL 2008; not tested in 2005.

select
quotename(schema_name(d.schema_id))+'.'+
quotename(object_name(d.parent_object_id)) as [Table_Name],
quotename(c.name) as [Column_Name],
quotename(d.name) as [Default_Name],
d.definition as [Default_Definition]
from
sys.default_constraints d
inner join
sys.columns c
on
d.parent_object_id = c.object_id and
d.parent_column_id = c.column_id
order by
1,2,3


Results:
Table_Name Column_Name Default_Name Default_Definition
[dbo].[BuildVersion] [ModifiedDate] [DF_BuildVersion_ModifiedDate] (getdate())
[dbo].[ErrorLog] [ErrorTime] [DF_ErrorLog_ErrorTime] (getdate())
[SalesLT].[Address] [ModifiedDate] [DF_Address_ModifiedDate] (getdate())



CODO ERGO SUM
Go to Top of Page
   

- Advertisement -