I was hoping for a way to script a procedure using TSQL. Using the Object Browser in QA would not be much different than using EM.

I was thinking something like

SELECT text FROM syscomments WHERE id = OBJECT_ID('myProc')

Unfortunately, like sp_helptext, you still get line breaks in all the wrong places.

I tried running Profiler once while scripting a proc from EM, but did not have a whole lot of luck (it was awhile ago, don't remember the details). And, I think there is a way to do it using SQLDMO, but didn't want to have to go that route either.

Sushila,

Great article. And a great purpose: people tend to forget that other people may be newbies in something but be expert professionals in something else.

The only comment I would like to make is the one that is important for the beginners, they don't know that: SQL Server Enterprise Manager is NOT SQL Server. This is a Front End, a Client to SQL Server and as a client has to be connected to SQL Server in the security context of someone. This is the process we call Registering Servers. If EM is installed at the same time as SQL Server the local server is automatically registered with Windows authentication. So a lot of objects: Tables, Procedures, Jobs etc will be created through EM with Windows login as an owner if this login is not SA, Sysadmin and other few cases when dbo or other user is specified (I don't list here all cases)

But you idea is excellent, we do need more articles on the beginner's level, basic backup and restore would be a good candidate for the next article.

Yelena

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]

Hmmm ... that looks awful. I'm presuming this is not the change script generated in EM. Given that amount of output I'd be thinking that's more to do with the database than EM itself. I've added half a dozen varchar columns to a table with just over 2mil records and it execute in a second or two.

I was actually wrong in saying that it executes sp_addcolumn, it does actually use ALTER TABLE. The script produced looks something like

BEGIN TRANSACTION
< whole lot of set statments >
COMMIT
BEGIN TRANSACTION
ALTER TABLE mytable ADD
 Col1 varchar(50) NULL
 etc...
GO
COMMIT

I agree that edit and saving table/index changes in EM is a real pain. I was gald to see that MS have finally put in a warning message when you edit large tables. However, I still maintain that EM can be the better tool for editing and generating change scripts.

I approach introducing EM to newbies with both joy and fear.

Giving 'newbies' access to EM can be a dangerous encounter.

Something about only having to right click an object , select delete and confirm and 'pouf' data/logic  is gone.

I have found that once newbies start to use EM they are always using it to return table recordsets... (right click a table... select return all rows... then they start to query using the 'SQL button'...?!?!?)

This is not a good practice especially if the db tables are part of a production application.  I have seen occassions where someone left EM open that created a lock on a database that prevented a backup job from starting or a sp from executing.  I regularly encounter EM Users (people who do not want to be called 'Newbies' because they now consider themselves DBA's) who do everything in EM.

EM is a great tool but it is almost too powerful.  It is sort of like giving electricians keys to the nuclear power plant.

EM has it share of bugs and issues... like refreshing and refreshing. ugh!!  Has anyone ever got the favorites in MMC to work with EM?  Favorites work with IIS and other MS MMC plugins, but EM seems to live by it's own rules in MMC.

I almost welcome the fact that it will disappear in SQL 2005.