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 upI tried this from bol, which doesn't workALTER TABLE MyCustomers ALTER COLUMN CompanyName DROP DEFAULTAnd SSMS generates this syntax, which does workALTER TABLE dbo.RHO_Type DROP CONSTRAINT DF__RHO_Type__ADD_Dt__112C2E87What's up?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd 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" |
|
|
X002548
Not Just a Number
15586 Posts |
|
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" |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
X002548
Not Just a Number
15586 Posts |
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
X002548
Not Just a Number
15586 Posts |
|
X002548
Not Just a Number
15586 Posts |
|
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_idorder by 1,2,3Results: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 |
|
|
|