Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Enterprise Manager Tricks Expand / Collapse
Author
Message
Posted Monday, September 26, 2005 11:19 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, September 16, 2013 8:08 AM
Points: 1,987, Visits: 210

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.

 

Post #223669
Posted Tuesday, September 27, 2005 3:10 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, September 29, 2014 2:44 PM
Points: 3,475, Visits: 583

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




Regards,
Yelena Varshal

Post #224157
Posted Tuesday, September 27, 2005 3:44 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:57 AM
Points: 2,555, Visits: 604
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...







**ASCII stupid question, get a stupid ANSI !!!**
Post #224166
Posted Tuesday, September 27, 2005 5:15 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135

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.

Post #224189
Posted Tuesday, September 27, 2005 6:30 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 10:44 PM
Points: 2,693, Visits: 1,213

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.

 



Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
Post #224197
Posted Wednesday, September 28, 2005 12:19 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135
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.
Post #224520
Posted Wednesday, October 26, 2005 7:13 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, September 13, 2014 7:01 AM
Points: 66, Visits: 361

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.

 




Post #232396
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse