SQLServerCentral / Discuss Content Posted by Sushila Iyer / Article Discussions / Article Discussions by Author / Enterprise Manager Tricks / Latest Posts

RE: Enterprise Manager Tricks

gregsoc — Wed, 26 Oct 2005 07:13:00 GMT

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.

RE: Enterprise Manager Tricks

Mike C — Wed, 28 Sep 2005 12:19:00 GMT

Yeah, SQL Server generates some pretty good change scripts that you can copy and paste into QA; but if you run the changes directly from EM you can end up with a lot more admin/overhead T-SQL code being run than you may have bargained for.

RE: Enterprise Manager Tricks

philcart — Tue, 27 Sep 2005 18:30:00 GMT

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 >COMMITBEGIN TRANSACTIONALTER TABLE mytable ADD Col1 varchar(50) NULL etc...GOCOMMIT

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.

RE: Enterprise Manager Tricks

Mike C — Tue, 27 Sep 2005 17:15:00 GMT

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]goSET TEXTSIZE 2147483647goselect 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.idgoSET TEXTSIZE 64512gouse [master]gouse [master]goSET TEXTSIZE 2147483647gosp_MShelpcolumns N'dbo.Employees', null, 'id', 1goSET TEXTSIZE 64512gouse [master]gouse [master]goSET TEXTSIZE 2147483647gosp_MStablechecks N'dbo.Employees'goSET TEXTSIZE 64512gouse [master]gouse [master]goSET TEXTSIZE 2147483647gosp_MShelpindex N'dbo.Employees', null, 1goSET TEXTSIZE 64512gouse [master]gouse [master]goSET TEXTSIZE 2147483647gosp_MStablerefs N'dbo.Employees', N'actualtables', N'both', nullgoSET TEXTSIZE 64512gouse [master]gouse [master]goSET TEXTSIZE 2147483647goselect 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') goSET TEXTSIZE 64512gouse [master]gouse [master]goSET TEXTSIZE 2147483647goselect 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') goSET TEXTSIZE 64512gouse [master]gouse [master]goSET TEXTSIZE 2147483647goselect 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') goSET TEXTSIZE 64512gouse [master]gouse [master]goSET TEXTSIZE 2147483647goselect 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') goSET TEXTSIZE 64512gouse [master]gouse [master]goSET TEXTSIZE 2147483647goselect 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') goSET TEXTSIZE 64512gouse [master]gouse [master]goSET TEXTSIZE 2147483647goselect 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') goSET TEXTSIZE 64512gouse [master]gouse [master]goSET TEXTSIZE 2147483647goselect 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') goSET TEXTSIZE 64512gouse [master]gouse [master]goSET TEXTSIZE 2147483647goselect 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') goSET TEXTSIZE 64512gouse [master]gouse [master]goset implicit_transactions on SET TEXTSIZE 2147483647goBEGIN TRANSACTION

goSET QUOTED_IDENTIFIER ON

goSET ARITHABORT ON

goSET NUMERIC_ROUNDABORT OFF

goSET CONCAT_NULL_YIELDS_NULL ON

goSET ANSI_NULLS ON

goSET ANSI_PADDING ON

goSET ANSI_WARNINGS ON

goCOMMIT

goALTER TABLE dbo.Employees ADD Suffix char(10) NULL

goCOMMIT

goIF @@TRANCOUNT > 0 COMMIT TRANgoset implicit_transactions off SET TEXTSIZE 64512gouse [master]gouse [master]goSET TEXTSIZE 2147483647goselect user_name()goSET TEXTSIZE 64512gouse [master]gouse [master]goset implicit_transactions on SET TEXTSIZE 2147483647gosp_MShelpcolumns N'dbo.Employees', null, 'id', 1goIF @@TRANCOUNT > 0 COMMIT TRANgoset implicit_transactions off SET TEXTSIZE 64512gouse [master]gouse [master]goset implicit_transactions on SET TEXTSIZE 2147483647gosp_MStablechecks N'dbo.Employees'goIF @@TRANCOUNT > 0 COMMIT TRANgoset implicit_transactions off SET TEXTSIZE 64512gouse [master]goselect 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, s2go

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]goset implicit_transactions on SET TEXTSIZE 2147483647goBEGIN TRANSACTION

goSET QUOTED_IDENTIFIER ON

goSET ARITHABORT ON

goSET NUMERIC_ROUNDABORT OFF

goSET CONCAT_NULL_YIELDS_NULL ON

goSET ANSI_NULLS ON

goSET ANSI_PADDING ON

goSET ANSI_WARNINGS ON

goCOMMIT

goIF @@TRANCOUNT > 0 COMMIT TRANgoset implicit_transactions off SET TEXTSIZE 64512gouse [master]gouse [master]goset implicit_transactions on SET TEXTSIZE 2147483647goDBCC USEROPTIONSgoIF @@TRANCOUNT > 0 COMMIT TRANgoset implicit_transactions off SET TEXTSIZE 64512gouse [master]gouse [master]goset implicit_transactions on SET TEXTSIZE 2147483647goEXECUTE sp_MStablespace N'dbo.LastNames'goIF @@TRANCOUNT > 0 COMMIT TRANgoset implicit_transactions off SET TEXTSIZE 64512gouse [master]gouse [master]goSET TEXTSIZE 2147483647goselect 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') = 1goSET TEXTSIZE 64512gouse [master]gouse [master]goset implicit_transactions on SET TEXTSIZE 2147483647goselect 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)goIF @@TRANCOUNT > 0 COMMIT TRANgoset implicit_transactions off SET TEXTSIZE 64512gouse [master]gouse [master]goset implicit_transactions on SET TEXTSIZE 2147483647goEXECUTE sp_MStablespace N'dbo.Employees'goIF @@TRANCOUNT > 0 COMMIT TRANgoset implicit_transactions off SET TEXTSIZE 64512gouse [master]gouse [master]goSET TEXTSIZE 2147483647goselect 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') = 1goSET TEXTSIZE 64512gouse [master]gouse [master]goSET TEXTSIZE 2147483647gosp_helptrigger N'dbo.Employees'goSET TEXTSIZE 64512gouse [master]gouse [master]goset implicit_transactions on SET TEXTSIZE 2147483647goselect 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)goIF @@TRANCOUNT > 0 COMMIT TRANgoset implicit_transactions off SET TEXTSIZE 64512gouse [master]gouse [master]goset implicit_transactions on SET TEXTSIZE 2147483647goBEGIN TRANSACTION

goselect 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') goALTER TABLE dbo.Employees DROP CONSTRAINT Employees_LastNames

goCOMMIT

goIF @@TRANCOUNT > 0 COMMIT TRANgoset implicit_transactions off SET TEXTSIZE 64512gouse [master]gouse [master]goset implicit_transactions on SET TEXTSIZE 2147483647gosp_MShelpcolumns N'dbo.LastNames', null, 'id', 1goIF @@TRANCOUNT > 0 COMMIT TRANgoset implicit_transactions off SET TEXTSIZE 64512gouse [master]gouse [master]goset implicit_transactions on SET TEXTSIZE 2147483647gosp_MStablechecks N'dbo.LastNames'goIF @@TRANCOUNT > 0 COMMIT TRANgoset implicit_transactions off SET TEXTSIZE 64512gouse [master]gouse [master]goSET TEXTSIZE 2147483647goselect id from sysobjects where id = object_id(N'dbo.Tmp_Employees')goSET TEXTSIZE 64512gouse [master]gouse [master]goset implicit_transactions on SET TEXTSIZE 2147483647goBEGIN TRANSACTION

goEXECUTE sp_MSobjectprivs N'dbo.Employees'goselect 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') godeclare @P1 intset @P1=0declare @P2 intset @P2=16388declare @P3 intset @P3=8193declare @P4 intset @P4=5exec 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 outputselect @P1, @P2, @P3, @P4goexec sp_cursorfetch 180150013, 2, 5, 5godeclare @P1 intset @P1=0declare @P2 intset @P2=16388declare @P3 intset @P3=8193declare @P4 intset @P4=5exec 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 outputselect @P1, @P2, @P3, @P4goexec sp_cursorfetch 180150014, 2, 5, 5godeclare @P1 intset @P1=0declare @P2 intset @P2=16388declare @P3 intset @P3=8193declare @P4 intset @P4=5exec 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 outputselect @P1, @P2, @P3, @P4goexec sp_cursorfetch 180150015, 2, 5, 5godeclare @P1 intset @P1=0declare @P2 intset @P2=16388declare @P3 intset @P3=8193declare @P4 intset @P4=5exec 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 outputselect @P1, @P2, @P3, @P4goexec sp_cursorfetch 180150016, 2, 5, 5godeclare @P1 intset @P1=0declare @P2 intset @P2=16388declare @P3 intset @P3=8193declare @P4 intset @P4=5exec 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 outputselect @P1, @P2, @P3, @P4goexec sp_cursorfetch 180150017, 2, 5, 5godeclare @P1 intset @P1=0declare @P2 intset @P2=16388declare @P3 intset @P3=8193declare @P4 intset @P4=5exec 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 outputselect @P1, @P2, @P3, @P4goexec sp_cursorfetch 180150018, 2, 5, 5godeclare @P1 intset @P1=0declare @P2 intset @P2=16388declare @P3 intset @P3=8193declare @P4 intset @P4=5exec 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 outputselect @P1, @P2, @P3, @P4goexec sp_cursorfetch 180150019, 2, 5, 5goCREATE 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]

goIF 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)')

goDROP TABLE dbo.Employees

godeclare @P1 intset @P1=0declare @P2 intset @P2=16388declare @P3 intset @P3=8193declare @P4 intset @P4=5exec sp_cursoropen @P1 output, N'EXECUTE sp_rename N''dbo.Tmp_Employees'', N''Employees'', ''OBJECT''', @P2 output, @P3 output, @P4 outputselect @P1, @P2, @P3, @P4goCREATE NONCLUSTERED INDEX IX_Employees ON dbo.Employees ( LastName, FirstName) ON [PRIMARY]

goALTER TABLE dbo.Employees ADD CONSTRAINT PK__Employees__7AF2094E PRIMARY KEY NONCLUSTERED ( ID) ON [PRIMARY]

goALTER TABLE dbo.Employees WITH NOCHECK ADD CONSTRAINT Employees_LastNames FOREIGN KEY ( LastName) REFERENCES dbo.LastNames ( LastName)

goCOMMIT

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.

RE: Enterprise Manager Tricks

sushila — Tue, 27 Sep 2005 15:44:00 GMT

yelena - thanks for your input & feedback...hopefully anyone who's read this far is noting your points - my purpose was to show how this great administrative tool with its' easy-to-use graphical interface could help developers in learning some T-Sql & surprisingly enough have "basic backup & restore" as part 3 of this theme...

RE: Enterprise Manager Tricks

Yelena Varshal — Tue, 27 Sep 2005 15:10:00 GMT

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

RE: Enterprise Manager Tricks

Richard Sisk — Mon, 26 Sep 2005 11:19:00 GMT

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.

RE: Enterprise Manager Tricks

homebrew01 — Mon, 26 Sep 2005 09:43:00 GMT

Sorry I wasn't clear. I hadn't seen you EM Tricks article, so I tried clicking the link in your first post.

RE: Enterprise Manager Tricks

sushila — Mon, 26 Sep 2005 09:35:00 GMT

what link would that be hb01 ?!

RE: Enterprise Manager Tricks

homebrew01 — Mon, 26 Sep 2005 09:30:00 GMT

I tried the link above, and got page not found ?? Did some pirates whisk it away somewhere ?

(edit) Found it by looking around a bit ....

RE: Enterprise Manager Tricks

sushila — Mon, 26 Sep 2005 08:28:00 GMT

Richard - not sure about your scripting stored procedures question but there're several ways to do it:1) In EM - use "All tasks" - "Generate Sql Script"....2) In QA - use the object browser and "Script object to New Window As..."3) Create a template & use it to modify & create new procedures.Hope at least one of these methods has addressed your question.Jeff - thanks for the kind words - find it difficult to believe that I could've helped any "oldbie" but will take your word for it!Phill - thx. for drawing my attention to the non-mentioned trick - Mike - Didn't know that EM does this - Think that with the expertise of all the articles you've written you're quick to find "missing links" and I'm grateful for your input.

RE: Enterprise Manager Tricks

philcart — Mon, 26 Sep 2005 08:21:00 GMT

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.

Richard, you can script most things from the Object Browser in Query Analyzer. Just right-clcick the object and choose "Script to New Window as ..."

Just make sure you visit the Options before you do any scripting. Checking the "Include Object Permissions" is one that always catches me

RE: Enterprise Manager Tricks

Richard Sisk — Mon, 26 Sep 2005 08:10:00 GMT

I use EM for two functions that I do quite often, creating databases and scripting store procedures.

I know that it is fairly simple to create a db in TSQL, I just got in the habit of doing it through EM.

Scripting store procedures, on the other hand, is another story. I don't know of anyway to script a store procedure from TSQL, scripting it to the point of being able to cut/paste into QA to modify the procedure. sp_helptext does not help because it puts carriage returns in inappropriate places.

Is there a way to script a store procedure in TSQL?

RE: Enterprise Manager Tricks

Mike C — Mon, 26 Sep 2005 08:00:00 GMT

Nice article. You might want to mention that EM should not be used to modify tables that are already populated. EM completely re-builds and re-populates the table from scratch for any modifications to it - even something as simple as adding a new column. Using T-SQL in QA will usually run several orders of magnitude faster, especially for tables with a lot of rows.

RE: Enterprise Manager Tricks

philcart — Mon, 26 Sep 2005 05:20:00 GMT

Nice article, but you forgot to mention one trick, or should I say chore Refresh, refresh, refresh and if you still can't find what you're looking for, refresh again

RE: Enterprise Manager Tricks

Jeff Moden — Mon, 26 Sep 2005 04:55:00 GMT

Nice job Sushila.... I use EM to troubleshoot other people's more complex joins and, occasionally, to make my own. And, I "steal" code from it and QA quite a bit for the sake of speed as you suggested. You and Erik are correct... when I was a newbie and found out how to do those things, it was about as good as Christmas.

I've been busy enough where I haven't paid much attention to Yukon and was disappointed to hear (in your article) the EM is going away. Perhaps another article that compares some of the tools like EM between 2000 and Yukon would be worthwhile.

Thanks for the article and the forum answers you've posted. They help Newbies and Oldbies alike.

Enterprise Manager Tricks

sushila — Tue, 23 Aug 2005 17:19:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sarur/enterprisemanagertricks.asp