Enterprise Manager Tricks
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sarur/enterprisemanagertricks.asp
**ASCII stupid question, get a stupid ANSI !!!** -
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.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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 
--------------------
Colt 45 - the original point and click interface
-
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.
-
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?
-
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.
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.
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
--------------------
Colt 45 - the original point and click interface -
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.
**ASCII stupid question, get a stupid ANSI !!!** -
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 ....
-
what link would that be hb01 ?!
**ASCII stupid question, get a stupid ANSI !!!** -
Sorry I wasn't clear. I hadn't seen you EM Tricks article, so I tried clicking the link in your first post.
-
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
Regards,Yelena Varsha
-
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 !!!** -
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.
-
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.
--------------------
Colt 45 - the original point and click interface
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply