Mike, you can add columns to a table in Enterprise Manager without too much fuss at all. Just add them at the end. Then EM will just use the sp_addcolumn stored procedure.
phillcart, here are the actual T-SQL commands issued by EM to add a column named Suffix to the end of a table named Employees. I will let the code speak for itself. The alternative is a single ALTER TABLE command in QA. If you don't believe there's a performance difference, try adding a column to a table with 1,000,000 rows in EM; then perform the same task using ALTER TABLE in QA and time them:
use [master]
go
SET TEXTSIZE 2147483647
go
select fg.tablefg, fg.textfg, ft.catname, OBJECTPROPERTY(object_id(N'dbo.Employees'), 'TableTextInRowLimit') from (select t.id, t.groupname tablefg, ti.groupname as textfg from (select s.groupname, i.id from sysobjects o, (sysindexes i full outer join sysfilegroups s on i.groupid=s.groupid) where (o.id = object_id(N'dbo.Employees')) and ((o.type = 'U') or (o.type = 'S')) and i.indid in (0,1) and i.id = o.id ) t full outer join (select s.groupname, i.id from sysobjects o, (sysindexes i full outer join sysfilegroups s on i.groupid=s.groupid) where (o.id = object_id(N'dbo.Employees')) and ((o.type = 'U') or (o.type = 'S')) and i.indid=255 and i.id = o.id ) ti on t.id = ti.id ) fg full outer join (select c.name as catname, object_id(N'dbo.Employees') as id from sysfulltextcatalogs c where c.ftcatid = objectproperty(object_id(N'dbo.Employees'), 'TableFulltextCatalogId') ) ft on fg.id = ft.id
go
SET TEXTSIZE 64512
go
use [master]
go
use [master]
go
SET TEXTSIZE 2147483647
go
sp_MShelpcolumns N'dbo.Employees', null, 'id', 1
go
SET TEXTSIZE 64512
go
use [master]
go
use [master]
go
SET TEXTSIZE 2147483647
go
sp_MStablechecks N'dbo.Employees'
go
SET TEXTSIZE 64512
go
use [master]
go
use [master]
go
SET TEXTSIZE 2147483647
go
sp_MShelpindex N'dbo.Employees', null, 1
go
SET TEXTSIZE 64512
go
use [master]
go
use [master]
go
SET TEXTSIZE 2147483647
go
sp_MStablerefs N'dbo.Employees', N'actualtables', N'both', null
go
SET TEXTSIZE 64512
go
use [master]
go
use [master]
go
SET TEXTSIZE 2147483647
go
select xp.name, cast(xp.[value] as nvarchar(4000)), cast(SQL_VARIANT_PROPERTY(xp.[value], 'BaseType') as nvarchar(256)), cast(SQL_VARIANT_PROPERTY(xp.[value], 'MaxLength') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], 'Precision') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], 'Scale') as int)from ::fn_listextendedproperty(NULL, N'user', N'dbo', N'table', N'Employees', NULL, NULL) xp where xp.name in (N'MS_Description', N'MS_Filter', N'MS_OrderBy', N'MS_SubdatasheetName', N'MS_LinkChildFields', N'MS_LinkMasterFields', N'MS_SubdatasheetHeight', N'MS_SubdatasheetExpanded', N'MS_DefaultView')
go
SET TEXTSIZE 64512
go
use [master]
go
use [master]
go
SET TEXTSIZE 2147483647
go
select xp.name, cast(xp.[value] as nvarchar(4000)), cast(SQL_VARIANT_PROPERTY(xp.[value], 'BaseType') as nvarchar(256)), cast(SQL_VARIANT_PROPERTY(xp.[value], 'MaxLength') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], 'Precision') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], 'Scale') as int)from ::fn_listextendedproperty(NULL, N'user', N'dbo', N'table', N'Employees', N'column', N'ID') xp where xp.name in (N'MS_Description', N'MS_Format', N'MS_InputMask', N'MS_DecimalPlaces', N'MS_AllowBlanks', N'MS_Caption', N'MS_DisplayControl', N'MS_RowSourceType', N'MS_RowSource', N'MS_BoundColumn', N'MS_ColumnCount', N'MS_ColumnHeads', N'MS_ColumnWidths', N'MS_ListRows', N'MS_ListWidth', N'MS_LimitToList', N'MS_Hyperlink', N'MS_IMEMode', N'MS_IMESentenceMode', N'MS_FuriganaControl', N'MS_PostalAddress')
go
SET TEXTSIZE 64512
go
use [master]
go
use [master]
go
SET TEXTSIZE 2147483647
go
select xp.name, cast(xp.[value] as nvarchar(4000)), cast(SQL_VARIANT_PROPERTY(xp.[value], 'BaseType') as nvarchar(256)), cast(SQL_VARIANT_PROPERTY(xp.[value], 'MaxLength') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], 'Precision') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], 'Scale') as int)from ::fn_listextendedproperty(NULL, N'user', N'dbo', N'table', N'Employees', N'column', N'LastName') xp where xp.name in (N'MS_Description', N'MS_Format', N'MS_InputMask', N'MS_DecimalPlaces', N'MS_AllowBlanks', N'MS_Caption', N'MS_DisplayControl', N'MS_RowSourceType', N'MS_RowSource', N'MS_BoundColumn', N'MS_ColumnCount', N'MS_ColumnHeads', N'MS_ColumnWidths', N'MS_ListRows', N'MS_ListWidth', N'MS_LimitToList', N'MS_Hyperlink', N'MS_IMEMode', N'MS_IMESentenceMode', N'MS_FuriganaControl', N'MS_PostalAddress')
go
SET TEXTSIZE 64512
go
use [master]
go
use [master]
go
SET TEXTSIZE 2147483647
go
select xp.name, cast(xp.[value] as nvarchar(4000)), cast(SQL_VARIANT_PROPERTY(xp.[value], 'BaseType') as nvarchar(256)), cast(SQL_VARIANT_PROPERTY(xp.[value], 'MaxLength') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], 'Precision') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], 'Scale') as int)from ::fn_listextendedproperty(NULL, N'user', N'dbo', N'table', N'Employees', N'column', N'FirstName') xp where xp.name in (N'MS_Description', N'MS_Format', N'MS_InputMask', N'MS_DecimalPlaces', N'MS_AllowBlanks', N'MS_Caption', N'MS_DisplayControl', N'MS_RowSourceType', N'MS_RowSource', N'MS_BoundColumn', N'MS_ColumnCount', N'MS_ColumnHeads', N'MS_ColumnWidths', N'MS_ListRows', N'MS_ListWidth', N'MS_LimitToList', N'MS_Hyperlink', N'MS_IMEMode', N'MS_IMESentenceMode', N'MS_FuriganaControl', N'MS_PostalAddress')
go
SET TEXTSIZE 64512
go
use [master]
go
use [master]
go
SET TEXTSIZE 2147483647
go
select xp.name, cast(xp.[value] as nvarchar(4000)), cast(SQL_VARIANT_PROPERTY(xp.[value], 'BaseType') as nvarchar(256)), cast(SQL_VARIANT_PROPERTY(xp.[value], 'MaxLength') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], 'Precision') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], 'Scale') as int)from ::fn_listextendedproperty(NULL, N'user', N'dbo', N'table', N'Employees', N'column', N'MiddleName') xp where xp.name in (N'MS_Description', N'MS_Format', N'MS_InputMask', N'MS_DecimalPlaces', N'MS_AllowBlanks', N'MS_Caption', N'MS_DisplayControl', N'MS_RowSourceType', N'MS_RowSource', N'MS_BoundColumn', N'MS_ColumnCount', N'MS_ColumnHeads', N'MS_ColumnWidths', N'MS_ListRows', N'MS_ListWidth', N'MS_LimitToList', N'MS_Hyperlink', N'MS_IMEMode', N'MS_IMESentenceMode', N'MS_FuriganaControl', N'MS_PostalAddress')
go
SET TEXTSIZE 64512
go
use [master]
go
use [master]
go
SET TEXTSIZE 2147483647
go
select xp.name, cast(xp.[value] as nvarchar(4000)), cast(SQL_VARIANT_PROPERTY(xp.[value], 'BaseType') as nvarchar(256)), cast(SQL_VARIANT_PROPERTY(xp.[value], 'MaxLength') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], 'Precision') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], 'Scale') as int)from ::fn_listextendedproperty(NULL, N'user', N'dbo', N'table', N'Employees', N'constraint', N'Employees_LastNames') xp where xp.name in (N'MS_ConstraintText')
go
SET TEXTSIZE 64512
go
use [master]
go
use [master]
go
SET TEXTSIZE 2147483647
go
select xp.name, cast(xp.[value] as nvarchar(4000)), cast(SQL_VARIANT_PROPERTY(xp.[value], 'BaseType') as nvarchar(256)), cast(SQL_VARIANT_PROPERTY(xp.[value], 'MaxLength') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], 'Precision') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], 'Scale') as int)from ::fn_listextendedproperty(NULL, N'user', N'dbo', N'table', N'Employees', N'index', N'IX_Employees') xp where xp.name in (N'MS_ConstraintText')
go
SET TEXTSIZE 64512
go
use [master]
go
use [master]
go
SET TEXTSIZE 2147483647
go
select xp.name, cast(xp.[value] as nvarchar(4000)), cast(SQL_VARIANT_PROPERTY(xp.[value], 'BaseType') as nvarchar(256)), cast(SQL_VARIANT_PROPERTY(xp.[value], 'MaxLength') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], 'Precision') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], 'Scale') as int)from ::fn_listextendedproperty(NULL, N'user', N'dbo', N'table', N'Employees', N'constraint', N'PK__Employees__7AF2094E') xp where xp.name in (N'MS_ConstraintText')
go
SET TEXTSIZE 64512
go
use [master]
go
use [master]
go
set implicit_transactions on SET TEXTSIZE 2147483647
go
BEGIN TRANSACTION
go
SET QUOTED_IDENTIFIER ON
go
SET ARITHABORT ON
go
SET NUMERIC_ROUNDABORT OFF
go
SET CONCAT_NULL_YIELDS_NULL ON
go
SET ANSI_NULLS ON
go
SET ANSI_PADDING ON
go
SET ANSI_WARNINGS ON
go
COMMIT
go
IF @@TRANCOUNT > 0 COMMIT TRAN
go
set implicit_transactions off SET TEXTSIZE 64512
go
use [master]
go
use [master]
go
set implicit_transactions on SET TEXTSIZE 2147483647
go
DBCC USEROPTIONS
go
IF @@TRANCOUNT > 0 COMMIT TRAN
go
set implicit_transactions off SET TEXTSIZE 64512
go
use [master]
go
use [master]
go
set implicit_transactions on SET TEXTSIZE 2147483647
go
EXECUTE sp_MStablespace N'dbo.Employees'
go
IF @@TRANCOUNT > 0 COMMIT TRAN
go
set implicit_transactions off SET TEXTSIZE 64512
go
use [master]
go
use [master]
go
SET TEXTSIZE 2147483647
go
select distinct object_name(d.id), c.name, user_name(OBJECTPROPERTY(d.id, N'OwnerId')), (cast(case when OBJECTPROPERTY(d.id, N'IsEncrypted') = 1 then 0x01 else 0x00 end as int) & 0x01) | (cast(case when OBJECTPROPERTY(d.id, N'IsIndexed') = 1 then 0x02 else 0x00 end as int) & 0x02) | (cast(case when OBJECTPROPERTY(d.id, N'IsView') = 1 then 0x04 else 0x00 end as int) & 0x04) | (cast(case when OBJECTPROPERTY(d.id, N'IsScalarFunction') = 1 then 0x08 else 0x00 end as int) & 0x08) | (cast(case when OBJECTPROPERTY(d.id, N'IsTableFunction') = 1 then 0x10 else 0x00 end as int) & 0x10) | (cast(case when OBJECTPROPERTY(d.id, N'IsInlineFunction') = 1 then 0x20 else 0x00 end as int) & 0x20) from sysdepends d, syscolumns c where c.id = object_id(N'dbo.Employees') and d.depid = c.id and c.colid = d.depnumber and d.deptype = 1 and (OBJECTPROPERTY(d.id, N'IsView') = 1 or OBJECTPROPERTY(d.id, N'IsScalarFunction') = 1 or OBJECTPROPERTY(d.id, N'IsTableFunction') = 1 or OBJECTPROPERTY(d.id, N'IsInlineFunction') = 1) and OBJECTPROPERTY(d.id, N'IsSchemaBound') = 1
go
SET TEXTSIZE 64512
go
use [master]
go
use [master]
go
set implicit_transactions on SET TEXTSIZE 2147483647
go
select convert(int, case ObjectProperty(object_id(N'dbo.Employees'), N'IsAnsiNullsOn') when 1 then 0x1 else 0x00 end) | convert(int, case ColumnProperty(object_id(N'dbo.Employees'), N'ID', N'UsesAnsiTrim') when 1 then 0x4 when 0 then 0x8 else 0x00 end) | convert(int, case ColumnProperty(object_id(N'dbo.Employees'), N'LastName', N'UsesAnsiTrim') when 1 then 0x4 when 0 then 0x8 else 0x00 end) | convert(int, case ColumnProperty(object_id(N'dbo.Employees'), N'FirstName', N'UsesAnsiTrim') when 1 then 0x4 when 0 then 0x8 else 0x00 end) | convert(int, case ColumnProperty(object_id(N'dbo.Employees'), N'MiddleName', N'UsesAnsiTrim') when 1 then 0x4 when 0 then 0x8 else 0x00 end) | convert(int, case ColumnProperty(object_id(N'dbo.Employees'), N'Suffix', N'UsesAnsiTrim') when 1 then 0x4 when 0 then 0x8 else 0x00 end)
go
IF @@TRANCOUNT > 0 COMMIT TRAN
go
set implicit_transactions off SET TEXTSIZE 64512
go
use [master]
go
use [master]
go
set implicit_transactions on SET TEXTSIZE 2147483647
go
BEGIN TRANSACTION
go
ALTER TABLE dbo.Employees ADD
Suffix char(10) NULL
go
COMMIT
go
IF @@TRANCOUNT > 0 COMMIT TRAN
go
set implicit_transactions off SET TEXTSIZE 64512
go
use [master]
go
use [master]
go
SET TEXTSIZE 2147483647
go
select user_name()
go
SET TEXTSIZE 64512
go
use [master]
go
use [master]
go
set implicit_transactions on SET TEXTSIZE 2147483647
go
sp_MShelpcolumns N'dbo.Employees', null, 'id', 1
go
IF @@TRANCOUNT > 0 COMMIT TRAN
go
set implicit_transactions off SET TEXTSIZE 64512
go
use [master]
go
use [master]
go
set implicit_transactions on SET TEXTSIZE 2147483647
go
sp_MStablechecks N'dbo.Employees'
go
IF @@TRANCOUNT > 0 COMMIT TRAN
go
set implicit_transactions off SET TEXTSIZE 64512
go
use [master]
go
select s1 = o.name, s2 = user_name(o.uid),o.crdate,o.id, N'SystemObj' = (case when (OBJECTPROPERTY(o.id, N'IsMSShipped')=1) then 1 else OBJECTPROPERTY(o.id, N'IsSystemTable') end), o.category, 0, ObjectProperty(o.id, N'TableHasActiveFulltextIndex'), ObjectProperty(o.id, N'TableFulltextCatalogId'), N'FakeTable' = (case when (OBJECTPROPERTY(o.id, N'tableisfake')=1) then 1 else 0 end), (case when (OBJECTPROPERTY(o.id, N'IsQuotedIdentOn')=1) then 1 else 0 end), (case when (OBJECTPROPERTY(o.id, N'IsAnsiNullsOn')=1) then 1 else 0 end) from dbo.sysobjects o, dbo.sysindexes i where OBJECTPROPERTY(o.id, N'IsTable') = 1 and i.id = o.id and i.indid < 2 and o.name not like N'#%' and o.id = object_id(N'[dbo].[Employees]') order by s1, s2
go
And if you add the column anywhere but at the end, EM generates a new table and copies your data from the old table to the new table for a real performance hit:
use [master]
go
set implicit_transactions on SET TEXTSIZE 2147483647
go
BEGIN TRANSACTION
go
SET QUOTED_IDENTIFIER ON
go
SET ARITHABORT ON
go
SET NUMERIC_ROUNDABORT OFF
go
SET CONCAT_NULL_YIELDS_NULL ON
go
SET ANSI_NULLS ON
go
SET ANSI_PADDING ON
go
SET ANSI_WARNINGS ON
go
COMMIT
go
IF @@TRANCOUNT > 0 COMMIT TRAN
go
set implicit_transactions off SET TEXTSIZE 64512
go
use [master]
go
use [master]
go
set implicit_transactions on SET TEXTSIZE 2147483647
go
DBCC USEROPTIONS
go
IF @@TRANCOUNT > 0 COMMIT TRAN
go
set implicit_transactions off SET TEXTSIZE 64512
go
use [master]
go
use [master]
go
set implicit_transactions on SET TEXTSIZE 2147483647
go
EXECUTE sp_MStablespace N'dbo.LastNames'
go
IF @@TRANCOUNT > 0 COMMIT TRAN
go
set implicit_transactions off SET TEXTSIZE 64512
go
use [master]
go
use [master]
go
SET TEXTSIZE 2147483647
go
select distinct object_name(d.id), c.name, user_name(OBJECTPROPERTY(d.id, N'OwnerId')), (cast(case when OBJECTPROPERTY(d.id, N'IsEncrypted') = 1 then 0x01 else 0x00 end as int) & 0x01) | (cast(case when OBJECTPROPERTY(d.id, N'IsIndexed') = 1 then 0x02 else 0x00 end as int) & 0x02) | (cast(case when OBJECTPROPERTY(d.id, N'IsView') = 1 then 0x04 else 0x00 end as int) & 0x04) | (cast(case when OBJECTPROPERTY(d.id, N'IsScalarFunction') = 1 then 0x08 else 0x00 end as int) & 0x08) | (cast(case when OBJECTPROPERTY(d.id, N'IsTableFunction') = 1 then 0x10 else 0x00 end as int) & 0x10) | (cast(case when OBJECTPROPERTY(d.id, N'IsInlineFunction') = 1 then 0x20 else 0x00 end as int) & 0x20) from sysdepends d, syscolumns c where c.id = object_id(N'dbo.LastNames') and d.depid = c.id and c.colid = d.depnumber and d.deptype = 1 and (OBJECTPROPERTY(d.id, N'IsView') = 1 or OBJECTPROPERTY(d.id, N'IsScalarFunction') = 1 or OBJECTPROPERTY(d.id, N'IsTableFunction') = 1 or OBJECTPROPERTY(d.id, N'IsInlineFunction') = 1) and OBJECTPROPERTY(d.id, N'IsSchemaBound') = 1
go
SET TEXTSIZE 64512
go
use [master]
go
use [master]
go
set implicit_transactions on SET TEXTSIZE 2147483647
go
select convert(int, case ObjectProperty(object_id(N'dbo.LastNames'), N'IsAnsiNullsOn') when 1 then 0x1 else 0x00 end) | convert(int, case ColumnProperty(object_id(N'dbo.LastNames'), N'LastName', N'UsesAnsiTrim') when 1 then 0x4 when 0 then 0x8 else 0x00 end) | convert(int, case ColumnProperty(object_id(N'dbo.LastNames'), N'LastName_Soundex', N'UsesAnsiTrim') when 1 then 0x4 when 0 then 0x8 else 0x00 end)
go
IF @@TRANCOUNT > 0 COMMIT TRAN
go
set implicit_transactions off SET TEXTSIZE 64512
go
use [master]
go
use [master]
go
set implicit_transactions on SET TEXTSIZE 2147483647
go
EXECUTE sp_MStablespace N'dbo.Employees'
go
IF @@TRANCOUNT > 0 COMMIT TRAN
go
set implicit_transactions off SET TEXTSIZE 64512
go
use [master]
go
use [master]
go
SET TEXTSIZE 2147483647
go
select distinct object_name(d.id), user_name(OBJECTPROPERTY(d.id,'OwnerId')), (cast(case when OBJECTPROPERTY(d.id, N'IsEncrypted') = 1 then 0x01 else 0x00 end as int) & 0x01) | (cast(case when OBJECTPROPERTY(d.id, N'IsIndexed') = 1 then 0x02 else 0x00 end as int) & 0x02) | (cast(case when OBJECTPROPERTY(d.id, N'IsView') = 1 then 0x04 else 0x00 end as int) & 0x04) | (cast(case when OBJECTPROPERTY(d.id, N'IsScalarFunction') = 1 then 0x08 else 0x00 end as int) & 0x08) | (cast(case when OBJECTPROPERTY(d.id, N'IsTableFunction') = 1 then 0x10 else 0x00 end as int) & 0x10) | (cast(case when OBJECTPROPERTY(d.id, N'IsInlineFunction') = 1 then 0x20 else 0x00 end as int) & 0x20) from sysdepends d where d.depid = object_id(N'dbo.Employees') and (OBJECTPROPERTY(d.id, N'IsView') = 1 or OBJECTPROPERTY(d.id, N'IsScalarFunction') = 1 or OBJECTPROPERTY(d.id, N'IsTableFunction') = 1 or OBJECTPROPERTY(d.id, N'IsInlineFunction') = 1) and OBJECTPROPERTY(d.id, N'IsSchemaBound') = 1
go
SET TEXTSIZE 64512
go
use [master]
go
use [master]
go
SET TEXTSIZE 2147483647
go
sp_helptrigger N'dbo.Employees'
go
SET TEXTSIZE 64512
go
use [master]
go
use [master]
go
set implicit_transactions on SET TEXTSIZE 2147483647
go
select convert(int, case ObjectProperty(object_id(N'dbo.Employees'), N'IsAnsiNullsOn') when 1 then 0x1 else 0x00 end) | convert(int, case ColumnProperty(object_id(N'dbo.Employees'), N'ID', N'UsesAnsiTrim') when 1 then 0x4 when 0 then 0x8 else 0x00 end) | convert(int, case ColumnProperty(object_id(N'dbo.Employees'), N'Title', N'UsesAnsiTrim') when 1 then 0x4 when 0 then 0x8 else 0x00 end) | convert(int, case ColumnProperty(object_id(N'dbo.Employees'), N'LastName', N'UsesAnsiTrim') when 1 then 0x4 when 0 then 0x8 else 0x00 end) | convert(int, case ColumnProperty(object_id(N'dbo.Employees'), N'FirstName', N'UsesAnsiTrim') when 1 then 0x4 when 0 then 0x8 else 0x00 end) | convert(int, case ColumnProperty(object_id(N'dbo.Employees'), N'MiddleName', N'UsesAnsiTrim') when 1 then 0x4 when 0 then 0x8 else 0x00 end) | convert(int, case ColumnProperty(object_id(N'dbo.Employees'), N'Suffix', N'UsesAnsiTrim') when 1 then 0x4 when 0 then 0x8 else 0x00 end)
go
IF @@TRANCOUNT > 0 COMMIT TRAN
go
set implicit_transactions off SET TEXTSIZE 64512
go
use [master]
go
use [master]
go
set implicit_transactions on SET TEXTSIZE 2147483647
go
BEGIN TRANSACTION
go
select xp.name, cast(xp.[value] as nvarchar(4000)), cast(SQL_VARIANT_PROPERTY(xp.[value], 'BaseType') as nvarchar(256)), cast(SQL_VARIANT_PROPERTY(xp.[value], 'MaxLength') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], 'Precision') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], 'Scale') as int)from ::fn_listextendedproperty(NULL, N'user', N'dbo', N'table', N'Employees', N'constraint', N'Employees_LastNames') xp where xp.name not in (N'MS_ConstraintText')
go
ALTER TABLE dbo.Employees
DROP CONSTRAINT Employees_LastNames
go
COMMIT
go
IF @@TRANCOUNT > 0 COMMIT TRAN
go
set implicit_transactions off SET TEXTSIZE 64512
go
use [master]
go
use [master]
go
set implicit_transactions on SET TEXTSIZE 2147483647
go
sp_MShelpcolumns N'dbo.LastNames', null, 'id', 1
go
IF @@TRANCOUNT > 0 COMMIT TRAN
go
set implicit_transactions off SET TEXTSIZE 64512
go
use [master]
go
use [master]
go
set implicit_transactions on SET TEXTSIZE 2147483647
go
sp_MStablechecks N'dbo.LastNames'
go
IF @@TRANCOUNT > 0 COMMIT TRAN
go
set implicit_transactions off SET TEXTSIZE 64512
go
use [master]
go
use [master]
go
SET TEXTSIZE 2147483647
go
select id from sysobjects where id = object_id(N'dbo.Tmp_Employees')
go
SET TEXTSIZE 64512
go
use [master]
go
use [master]
go
set implicit_transactions on SET TEXTSIZE 2147483647
go
BEGIN TRANSACTION
go
EXECUTE sp_MSobjectprivs N'dbo.Employees'
go
select xp.name, cast(xp.[value] as nvarchar(4000)), cast(SQL_VARIANT_PROPERTY(xp.[value], 'BaseType') as nvarchar(256)), cast(SQL_VARIANT_PROPERTY(xp.[value], 'MaxLength') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], 'Precision') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], 'Scale') as int)from ::fn_listextendedproperty(NULL, N'user', N'dbo', N'table', N'Employees', NULL, NULL) xp where xp.name not in (N'MS_Description', N'MS_Filter', N'MS_OrderBy', N'MS_SubdatasheetName', N'MS_LinkChildFields', N'MS_LinkMasterFields', N'MS_SubdatasheetHeight', N'MS_SubdatasheetExpanded', N'MS_DefaultView')
go
declare @P1 int
set @P1=0
declare @P2 int
set @P2=16388
declare @P3 int
set @P3=8193
declare @P4 int
set @P4=5
exec sp_cursoropen @P1 output, N'select xp.name, cast(xp.[value] as nvarchar(4000)), cast(SQL_VARIANT_PROPERTY(xp.[value], ''BaseType'') as nvarchar(256)), cast(SQL_VARIANT_PROPERTY(xp.[value], ''MaxLength'') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], ''Precision'') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], ''Scale'') as int)from ::fn_listextendedproperty(NULL, N''user'', N''dbo'', N''table'', N''Employees'', N''column'', N''ID'') xp where xp.name not in (N''MS_Description'', N''MS_Format'', N''MS_InputMask'', N''MS_DecimalPlaces'', N''MS_AllowBlanks'', N''MS_Caption'', N''MS_DisplayControl'', N''MS_RowSourceType'', N''MS_RowSource'', N''MS_BoundColumn'', N''MS_ColumnCount'', N''MS_ColumnHeads'', N''MS_ColumnWidths'', N''MS_ListRows'', N''MS_ListWidth'', N''MS_LimitToList'', N''MS_Hyperlink'', N''MS_IMEMode'', N''MS_IMESentenceMode'', N''MS_FuriganaControl'', N''MS_PostalAddress'') ', @P2 output, @P3 output, @P4 output
select @P1, @P2, @P3, @P4
go
exec sp_cursorfetch 180150013, 2, 5, 5
go
declare @P1 int
set @P1=0
declare @P2 int
set @P2=16388
declare @P3 int
set @P3=8193
declare @P4 int
set @P4=5
exec sp_cursoropen @P1 output, N'select xp.name, cast(xp.[value] as nvarchar(4000)), cast(SQL_VARIANT_PROPERTY(xp.[value], ''BaseType'') as nvarchar(256)), cast(SQL_VARIANT_PROPERTY(xp.[value], ''MaxLength'') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], ''Precision'') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], ''Scale'') as int)from ::fn_listextendedproperty(NULL, N''user'', N''dbo'', N''table'', N''Employees'', N''column'', N''LastName'') xp where xp.name not in (N''MS_Description'', N''MS_Format'', N''MS_InputMask'', N''MS_DecimalPlaces'', N''MS_AllowBlanks'', N''MS_Caption'', N''MS_DisplayControl'', N''MS_RowSourceType'', N''MS_RowSource'', N''MS_BoundColumn'', N''MS_ColumnCount'', N''MS_ColumnHeads'', N''MS_ColumnWidths'', N''MS_ListRows'', N''MS_ListWidth'', N''MS_LimitToList'', N''MS_Hyperlink'', N''MS_IMEMode'', N''MS_IMESentenceMode'', N''MS_FuriganaControl'', N''MS_PostalAddress'') ', @P2 output, @P3 output, @P4 output
select @P1, @P2, @P3, @P4
go
exec sp_cursorfetch 180150014, 2, 5, 5
go
declare @P1 int
set @P1=0
declare @P2 int
set @P2=16388
declare @P3 int
set @P3=8193
declare @P4 int
set @P4=5
exec sp_cursoropen @P1 output, N'select xp.name, cast(xp.[value] as nvarchar(4000)), cast(SQL_VARIANT_PROPERTY(xp.[value], ''BaseType'') as nvarchar(256)), cast(SQL_VARIANT_PROPERTY(xp.[value], ''MaxLength'') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], ''Precision'') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], ''Scale'') as int)from ::fn_listextendedproperty(NULL, N''user'', N''dbo'', N''table'', N''Employees'', N''column'', N''FirstName'') xp where xp.name not in (N''MS_Description'', N''MS_Format'', N''MS_InputMask'', N''MS_DecimalPlaces'', N''MS_AllowBlanks'', N''MS_Caption'', N''MS_DisplayControl'', N''MS_RowSourceType'', N''MS_RowSource'', N''MS_BoundColumn'', N''MS_ColumnCount'', N''MS_ColumnHeads'', N''MS_ColumnWidths'', N''MS_ListRows'', N''MS_ListWidth'', N''MS_LimitToList'', N''MS_Hyperlink'', N''MS_IMEMode'', N''MS_IMESentenceMode'', N''MS_FuriganaControl'', N''MS_PostalAddress'') ', @P2 output, @P3 output, @P4 output
select @P1, @P2, @P3, @P4
go
exec sp_cursorfetch 180150015, 2, 5, 5
go
declare @P1 int
set @P1=0
declare @P2 int
set @P2=16388
declare @P3 int
set @P3=8193
declare @P4 int
set @P4=5
exec sp_cursoropen @P1 output, N'select xp.name, cast(xp.[value] as nvarchar(4000)), cast(SQL_VARIANT_PROPERTY(xp.[value], ''BaseType'') as nvarchar(256)), cast(SQL_VARIANT_PROPERTY(xp.[value], ''MaxLength'') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], ''Precision'') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], ''Scale'') as int)from ::fn_listextendedproperty(NULL, N''user'', N''dbo'', N''table'', N''Employees'', N''column'', N''MiddleName'') xp where xp.name not in (N''MS_Description'', N''MS_Format'', N''MS_InputMask'', N''MS_DecimalPlaces'', N''MS_AllowBlanks'', N''MS_Caption'', N''MS_DisplayControl'', N''MS_RowSourceType'', N''MS_RowSource'', N''MS_BoundColumn'', N''MS_ColumnCount'', N''MS_ColumnHeads'', N''MS_ColumnWidths'', N''MS_ListRows'', N''MS_ListWidth'', N''MS_LimitToList'', N''MS_Hyperlink'', N''MS_IMEMode'', N''MS_IMESentenceMode'', N''MS_FuriganaControl'', N''MS_PostalAddress'') ', @P2 output, @P3 output, @P4 output
select @P1, @P2, @P3, @P4
go
exec sp_cursorfetch 180150016, 2, 5, 5
go
declare @P1 int
set @P1=0
declare @P2 int
set @P2=16388
declare @P3 int
set @P3=8193
declare @P4 int
set @P4=5
exec sp_cursoropen @P1 output, N'select xp.name, cast(xp.[value] as nvarchar(4000)), cast(SQL_VARIANT_PROPERTY(xp.[value], ''BaseType'') as nvarchar(256)), cast(SQL_VARIANT_PROPERTY(xp.[value], ''MaxLength'') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], ''Precision'') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], ''Scale'') as int)from ::fn_listextendedproperty(NULL, N''user'', N''dbo'', N''table'', N''Employees'', N''column'', N''Suffix'') xp where xp.name not in (N''MS_Description'', N''MS_Format'', N''MS_InputMask'', N''MS_DecimalPlaces'', N''MS_AllowBlanks'', N''MS_Caption'', N''MS_DisplayControl'', N''MS_RowSourceType'', N''MS_RowSource'', N''MS_BoundColumn'', N''MS_ColumnCount'', N''MS_ColumnHeads'', N''MS_ColumnWidths'', N''MS_ListRows'', N''MS_ListWidth'', N''MS_LimitToList'', N''MS_Hyperlink'', N''MS_IMEMode'', N''MS_IMESentenceMode'', N''MS_FuriganaControl'', N''MS_PostalAddress'') ', @P2 output, @P3 output, @P4 output
select @P1, @P2, @P3, @P4
go
exec sp_cursorfetch 180150017, 2, 5, 5
go
declare @P1 int
set @P1=0
declare @P2 int
set @P2=16388
declare @P3 int
set @P3=8193
declare @P4 int
set @P4=5
exec sp_cursoropen @P1 output, N'select xp.name, cast(xp.[value] as nvarchar(4000)), cast(SQL_VARIANT_PROPERTY(xp.[value], ''BaseType'') as nvarchar(256)), cast(SQL_VARIANT_PROPERTY(xp.[value], ''MaxLength'') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], ''Precision'') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], ''Scale'') as int)from ::fn_listextendedproperty(NULL, N''user'', N''dbo'', N''table'', N''Employees'', N''index'', N''IX_Employees'') xp where xp.name not in (N''MS_ConstraintText'') ', @P2 output, @P3 output, @P4 output
select @P1, @P2, @P3, @P4
go
exec sp_cursorfetch 180150018, 2, 5, 5
go
declare @P1 int
set @P1=0
declare @P2 int
set @P2=16388
declare @P3 int
set @P3=8193
declare @P4 int
set @P4=5
exec sp_cursoropen @P1 output, N'select xp.name, cast(xp.[value] as nvarchar(4000)), cast(SQL_VARIANT_PROPERTY(xp.[value], ''BaseType'') as nvarchar(256)), cast(SQL_VARIANT_PROPERTY(xp.[value], ''MaxLength'') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], ''Precision'') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], ''Scale'') as int)from ::fn_listextendedproperty(NULL, N''user'', N''dbo'', N''table'', N''Employees'', N''constraint'', N''PK__Employees__7AF2094E'') xp where xp.name not in (N''MS_ConstraintText'') ', @P2 output, @P3 output, @P4 output
select @P1, @P2, @P3, @P4
go
exec sp_cursorfetch 180150019, 2, 5, 5
go
CREATE TABLE dbo.Tmp_Employees
(
ID int NOT NULL,
Title char(10) NULL,
LastName varchar(50) NOT NULL,
FirstName varchar(50) NOT NULL,
MiddleName varchar(50) NULL,
Suffix char(10) NULL) ON [PRIMARY]
go
IF EXISTS(SELECT * FROM dbo.Employees)
EXEC('INSERT INTO dbo.Tmp_Employees (ID, LastName, FirstName, MiddleName, Suffix)
SELECT ID, LastName, FirstName, MiddleName, Suffix FROM dbo.Employees (HOLDLOCK TABLOCKX)')
go
DROP TABLE dbo.Employees
go
declare @P1 int
set @P1=0
declare @P2 int
set @P2=16388
declare @P3 int
set @P3=8193
declare @P4 int
set @P4=5
exec sp_cursoropen @P1 output, N'EXECUTE sp_rename N''dbo.Tmp_Employees'', N''Employees'', ''OBJECT''
', @P2 output, @P3 output, @P4 output
select @P1, @P2, @P3, @P4
go
CREATE NONCLUSTERED INDEX IX_Employees ON dbo.Employees
(
LastName,
FirstName) ON [PRIMARY]
go
ALTER TABLE dbo.Employees ADD CONSTRAINT
PK__Employees__7AF2094E PRIMARY KEY NONCLUSTERED
(
ID) ON [PRIMARY]
go
ALTER TABLE dbo.Employees WITH NOCHECK ADD CONSTRAINT
Employees_LastNames FOREIGN KEY
(
LastName) REFERENCES dbo.LastNames
(
LastName)
go
COMMIT
Also, if you are editing the structure of a populated table, then it's actually better to do it in Enterprise Manger, grab the change script, and close the design view without saving the changes. The reason for this is that Enterprise Manager will do a better job of scripting all the constraints that apply to the table.
Assuming you are dealing with a table with a fairly complex set of constraints, this might be true. What do you do with that change script? Presumably you'll run it in QA? Try adding/removing an index or clustered index from a 1,000,000 row table in EM sometime and then issue the equivalent T-SQL command in QA and time your results.