DSO in SQL2005: recreate

Showing posts with label recreate. Show all posts

Monday, March 19, 2012

DROP PROCS

Is it necessary to drop & recreate all procedures and triggers every
few months? If yes, why
?.
No; where did you hear that?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"docsql" <docsql@.noemail.nospam> wrote in message
news:u%23J7LRH7FHA.956@.TK2MSFTNGP10.phx.gbl...
> Is it necessary to drop & recreate all procedures and triggers every
> few months? If yes, why
> ?.
>
>
|||Actually it was a question on a supplemental questionnaire for a DBA
position. Do you think it might be true for other databases?
Sybase/oracle?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uCEDuSH7FHA.3976@.TK2MSFTNGP15.phx.gbl...
> No; where did you hear that?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:u%23J7LRH7FHA.956@.TK2MSFTNGP10.phx.gbl...
>
|||Sounds like a trick question to me. Your other questions, too. I would be
very cautious about taking this job if I were you.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"docsql" <docsql@.noemail.nospam> wrote in message
news:%231jyCmH7FHA.3200@.TK2MSFTNGP11.phx.gbl...
> Actually it was a question on a supplemental questionnaire for a DBA
> position. Do you think it might be true for other databases?
> Sybase/oracle?
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uCEDuSH7FHA.3976@.TK2MSFTNGP15.phx.gbl...
>
|||By the way, they might be looking for recompilation (perhaps whoever wrote
the test didn't know how to recompile stored procedures and thought they had
to be dropped and re-created?) ... That's my only guess.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"docsql" <docsql@.noemail.nospam> wrote in message
news:%231jyCmH7FHA.3200@.TK2MSFTNGP11.phx.gbl...
> Actually it was a question on a supplemental questionnaire for a DBA
> position. Do you think it might be true for other databases?
> Sybase/oracle?
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uCEDuSH7FHA.3976@.TK2MSFTNGP15.phx.gbl...
>

DROP PROCS

Is it necessary to drop & recreate all procedures and triggers every
few months? If yes, why
?.No; where did you hear that?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"docsql" <docsql@.noemail.nospam> wrote in message
news:u%23J7LRH7FHA.956@.TK2MSFTNGP10.phx.gbl...
> Is it necessary to drop & recreate all procedures and triggers every
> few months? If yes, why
> ?.
>
>|||Actually it was a question on a supplemental questionnaire for a DBA
position. Do you think it might be true for other databases?
Sybase/oracle?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uCEDuSH7FHA.3976@.TK2MSFTNGP15.phx.gbl...
> No; where did you hear that?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:u%23J7LRH7FHA.956@.TK2MSFTNGP10.phx.gbl...
>> Is it necessary to drop & recreate all procedures and triggers every
>> few months? If yes, why
>> ?.
>>
>|||Sounds like a trick question to me. Your other questions, too. I would be
very cautious about taking this job if I were you.
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"docsql" <docsql@.noemail.nospam> wrote in message
news:%231jyCmH7FHA.3200@.TK2MSFTNGP11.phx.gbl...
> Actually it was a question on a supplemental questionnaire for a DBA
> position. Do you think it might be true for other databases?
> Sybase/oracle?
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uCEDuSH7FHA.3976@.TK2MSFTNGP15.phx.gbl...
>> No; where did you hear that?
>>
>> --
>> Adam Machanic
>> Pro SQL Server 2005, available now
>> http://www.apress.com/book/bookDisplay.html?bID=457
>> --
>>
>> "docsql" <docsql@.noemail.nospam> wrote in message
>> news:u%23J7LRH7FHA.956@.TK2MSFTNGP10.phx.gbl...
>> Is it necessary to drop & recreate all procedures and triggers
>> every few months? If yes, why
>> ?.
>>
>>
>|||By the way, they might be looking for recompilation (perhaps whoever wrote
the test didn't know how to recompile stored procedures and thought they had
to be dropped and re-created?) ... That's my only guess.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"docsql" <docsql@.noemail.nospam> wrote in message
news:%231jyCmH7FHA.3200@.TK2MSFTNGP11.phx.gbl...
> Actually it was a question on a supplemental questionnaire for a DBA
> position. Do you think it might be true for other databases?
> Sybase/oracle?
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uCEDuSH7FHA.3976@.TK2MSFTNGP15.phx.gbl...
>> No; where did you hear that?
>>
>> --
>> Adam Machanic
>> Pro SQL Server 2005, available now
>> http://www.apress.com/book/bookDisplay.html?bID=457
>> --
>>
>> "docsql" <docsql@.noemail.nospam> wrote in message
>> news:u%23J7LRH7FHA.956@.TK2MSFTNGP10.phx.gbl...
>> Is it necessary to drop & recreate all procedures and triggers
>> every few months? If yes, why
>> ?.
>>
>>
>

DROP PROCS

Is it necessary to drop & recreate all procedures and triggers every
few months? If yes, why
?.No; where did you hear that?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"docsql" <docsql@.noemail.nospam> wrote in message
news:u%23J7LRH7FHA.956@.TK2MSFTNGP10.phx.gbl...
> Is it necessary to drop & recreate all procedures and triggers every
> few months? If yes, why
> ?.
>
>|||Actually it was a question on a supplemental questionnaire for a DBA
position. Do you think it might be true for other databases?
Sybase/oracle?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uCEDuSH7FHA.3976@.TK2MSFTNGP15.phx.gbl...
> No; where did you hear that?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:u%23J7LRH7FHA.956@.TK2MSFTNGP10.phx.gbl...
>|||Sounds like a trick question to me. Your other questions, too. I would be
very cautious about taking this job if I were you.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"docsql" <docsql@.noemail.nospam> wrote in message
news:%231jyCmH7FHA.3200@.TK2MSFTNGP11.phx.gbl...
> Actually it was a question on a supplemental questionnaire for a DBA
> position. Do you think it might be true for other databases?
> Sybase/oracle?
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uCEDuSH7FHA.3976@.TK2MSFTNGP15.phx.gbl...
>|||By the way, they might be looking for recompilation (perhaps whoever wrote
the test didn't know how to recompile stored procedures and thought they had
to be dropped and re-created?) ... That's my only guess.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"docsql" <docsql@.noemail.nospam> wrote in message
news:%231jyCmH7FHA.3200@.TK2MSFTNGP11.phx.gbl...
> Actually it was a question on a supplemental questionnaire for a DBA
> position. Do you think it might be true for other databases?
> Sybase/oracle?
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uCEDuSH7FHA.3976@.TK2MSFTNGP15.phx.gbl...
>

Friday, March 9, 2012

drop and recreate views in mssql server

hi,
how can i drop views and recreate again?
thinks ...um, using the DROP and CREATE commands

or else i don't understand your question|||... or you could use the ALTER view statement, which does the same as drop:ing and recreating a view with the added bonus that it doesn't allow you to make any faults that violates the T-SQL syntax. :)

Drop and Recreate subscription

I need to drop and recreate few subscriptions in transactional publication
Do I need to worry about log marker issues ?
Do I need to set the primary and replicate databases in 'DBO use only'
The Primary and Replicate databases are being accessed all the time.To your two questions, the answers are

No and No.

Drop and recreate indexes

So, the ongoing saga continues. To drop and recreate my indexes on a
specific table, do I need to copy the SQL for the existing indexes (I need
to keep them the same) then delete them thru Manage Indexes and run the SQL
create index scripts? Or is there a better way? Thanks for the help!
WillieWillie,
Drop/re-create or rebuild? i.e, DBCC DBREINDEX / DBCC INDEXDEFRAG?
HTH
Jerry
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:ONgI0I20FHA.3780@.TK2MSFTNGP12.phx.gbl...
> So, the ongoing saga continues. To drop and recreate my indexes on a
> specific table, do I need to copy the SQL for the existing indexes (I need
> to keep them the same) then delete them thru Manage Indexes and run the
> SQL create index scripts? Or is there a better way? Thanks for the help!
> Willie
>|||Well, I've already tried dbcc indexdefrag and dbcc dbreindex and neither of
them seem to have helped my situation, so I was thinking I would go to the
next level and start over again with the indexes by creating new indexes
(with the same definitions). Is that a reasonable thing to do?
Willie
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23z%23dLL20FHA.664@.tk2msftngp13.phx.gbl...
> Willie,
> Drop/re-create or rebuild? i.e, DBCC DBREINDEX / DBCC INDEXDEFRAG?
> HTH
> Jerry
> "Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
> news:ONgI0I20FHA.3780@.TK2MSFTNGP12.phx.gbl...
>> So, the ongoing saga continues. To drop and recreate my indexes on a
>> specific table, do I need to copy the SQL for the existing indexes (I
>> need to keep them the same) then delete them thru Manage Indexes and run
>> the SQL create index scripts? Or is there a better way? Thanks for the
>> help!
>> Willie
>|||Willie,
I don't remember what your original issue was. However, you could try
recreating a single index prior to hitting them all to see if it fixes your
issue. (i.e, start small). Check out CREATE INDEX ...WITH DROP_EXISTING and
DBCC DBREINDEX.
HTH
Jerry
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:eKFSRU20FHA.404@.TK2MSFTNGP09.phx.gbl...
> Well, I've already tried dbcc indexdefrag and dbcc dbreindex and neither
> of them seem to have helped my situation, so I was thinking I would go to
> the next level and start over again with the indexes by creating new
> indexes (with the same definitions). Is that a reasonable thing to do?
> Willie
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23z%23dLL20FHA.664@.tk2msftngp13.phx.gbl...
>> Willie,
>> Drop/re-create or rebuild? i.e, DBCC DBREINDEX / DBCC INDEXDEFRAG?
>> HTH
>> Jerry
>> "Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
>> news:ONgI0I20FHA.3780@.TK2MSFTNGP12.phx.gbl...
>> So, the ongoing saga continues. To drop and recreate my indexes on a
>> specific table, do I need to copy the SQL for the existing indexes (I
>> need to keep them the same) then delete them thru Manage Indexes and run
>> the SQL create index scripts? Or is there a better way? Thanks for the
>> help!
>> Willie
>>
>|||Willie Bodger wrote:
> Well, I've already tried dbcc indexdefrag and dbcc dbreindex and
> neither of them seem to have helped my situation, so I was thinking I
> would go to the next level and start over again with the indexes by
> creating new indexes (with the same definitions). Is that a
> reasonable thing to do?
I don't think so. DBCC DBREINDEX does pretty much the same as dropping
and recreating so I would not expect your situation to better. Maybe you
need a *different* index or must take other measures (e.g. work on the IO
performance side). Did you actually identify the cause of your problem?
Kind regards
robert|||Verify that the table does not have any poorly written / performing
triggers.|||I have not been able to really identify the issue, but the table doesn't
have any triggers (I thought it did, but upon actually going to Manage
Triggers it shows none). I ran thru the index tuning wizard and it had no
suggestions to make, the Update Statement is super simple (set field=xx
where field=yy). Is an Update statement just that slow?
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23LUT0%2390FHA.2924@.TK2MSFTNGP15.phx.gbl...
> Verify that the table does not have any poorly written / performing
> triggers.
>|||> suggestions to make, the Update Statement is super simple (set field=xx
> where field=yy). Is an Update statement just that slow?
Impossible to tell without sufficient and accurate information about the
schema, the actual statement, an understanding of the distribution of data
in the affected table(s), and knowledge of any code that might be executed
as a side-effect of the statement. Perhaps the easiest way to figure out
what is happening is via the Profiler. As Tibor indicated in your last
thread - check the query plan - this should at least point to the reason.
In fact, there were quite a few suggestions - perhaps you should re-review
them?|||Hmm... Guess I'm just missing something. I went thru the Estimated Execution
Plan, but it didn't seem to tell me a whole lot (granted, I'm still trying
to figure out what it all means). I thought that I had posted everything
relevant to trying to figure out what is going on, but I guess I missed a
few things, so let me try a little further.
>accurate information about the schema (is this the table create script? Do
>you need the Index info?):
CREATE TABLE [dbo].[CustomerProduct] (
[iProductId] [int] NOT NULL ,
[iSiteId] [int] NOT NULL ,
[iOwnerId] [int] NOT NULL ,
[chLanguageCode] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[iContactId] [int] NULL ,
[chProductNumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[vchSerialNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[flQuantity] [OnyxFloat] NULL ,
[dtPurchaseDate] [datetime] NULL ,
[iTrackingId] [int] NULL ,
[iSourceId] [int] NULL ,
[iStatusId] [int] NULL ,
[iAccessCode] [int] NOT NULL ,
[vchUser1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser3] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser4] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser5] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser6] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser7] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser8] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser9] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser10] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[chInsertBy] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dtInsertDate] [datetime] NOT NULL ,
[chUpdateBy] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dtUpdateDate] [datetime] NOT NULL ,
[tiRecordStatus] [tinyint] NOT NULL ,
[dtModifiedDate] [smalldatetime] NULL
) ON [PRIMARY]
>the actual statement:
UPDATE CustomerProduct
SET chProductNumber='PAFGLPLK0B0BMS0EVDML'
WHERE chProductNumber='PAFGLPLK0B0BMS0RTDEN'
>an understanding of the distribution of data in the affected table(s):
13910 of 1283741 records need to be updated
>knowledge of any code that might be executed as a side-effect of the
>statement:
There are no triggers on this table, so what other side-effects migth there
be?
Again, I really do appreciate teh help of those with more expertise than I
in this area.
Willie
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%233vJrFB1FHA.560@.TK2MSFTNGP12.phx.gbl...
>> suggestions to make, the Update Statement is super simple (set field=xx
>> where field=yy). Is an Update statement just that slow?
> Impossible to tell without sufficient and accurate information about the
> schema, the actual statement, an understanding of the distribution of data
> in the affected table(s), and knowledge of any code that might be executed
> as a side-effect of the statement. Perhaps the easiest way to figure out
> what is happening is via the Profiler. As Tibor indicated in your last
> thread - check the query plan - this should at least point to the reason.
> In fact, there were quite a few suggestions - perhaps you should re-review
> them?
>|||Willie Bodger wrote:
> Hmm... Guess I'm just missing something. I went thru the Estimated
> Execution Plan, but it didn't seem to tell me a whole lot (granted,
> I'm still trying to figure out what it all means). I thought that I
> had posted everything relevant to trying to figure out what is going
> on, but I guess I missed a few things, so let me try a little further.
>> accurate information about the schema (is this the table create
>> script? Do you need the Index info?):
> CREATE TABLE [dbo].[CustomerProduct] (
> [iProductId] [int] NOT NULL ,
> [iSiteId] [int] NOT NULL ,
> [iOwnerId] [int] NOT NULL ,
> [chLanguageCode] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL , [iContactId] [int] NULL ,
> [chProductNumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [vchSerialNumber] [varchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [flQuantity] [OnyxFloat] NULL ,
> [dtPurchaseDate] [datetime] NULL ,
> [iTrackingId] [int] NULL ,
> [iSourceId] [int] NULL ,
> [iStatusId] [int] NULL ,
> [iAccessCode] [int] NOT NULL ,
> [vchUser1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [vchUser2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [vchUser3] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [vchUser4] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [vchUser5] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [vchUser6] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [vchUser7] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [vchUser8] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [vchUser9] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [vchUser10] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> , [chInsertBy] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL , [dtInsertDate] [datetime] NOT NULL ,
> [chUpdateBy] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL , [dtUpdateDate] [datetime] NOT NULL ,
> [tiRecordStatus] [tinyint] NOT NULL ,
> [dtModifiedDate] [smalldatetime] NULL
> ) ON [PRIMARY]
>> the actual statement:
> UPDATE CustomerProduct
> SET chProductNumber='PAFGLPLK0B0BMS0EVDML'
> WHERE chProductNumber='PAFGLPLK0B0BMS0RTDEN'
>> an understanding of the distribution of data in the affected
>> table(s):
> 13910 of 1283741 records need to be updated
>> knowledge of any code that might be executed as a side-effect of the
>> statement:
> There are no triggers on this table, so what other side-effects migth
> there be?
> Again, I really do appreciate teh help of those with more expertise
> than I in this area.
I didn't see any execution plan. Did you mean to include it? You can
generate a text version with QA.
Also, what might make things slow here is TX logging. If your TX log
resides on a slow disk (or the same disk as the data) you'll see
performance degradation for all updating SQL.
Kind regards
robert|||>>the actual statement:
> UPDATE CustomerProduct
> SET chProductNumber='PAFGLPLK0B0BMS0EVDML'
> WHERE chProductNumber='PAFGLPLK0B0BMS0RTDEN'
>>an understanding of the distribution of data in the affected table(s):
> 13910 of 1283741 records need to be updated
A straightforward update on a relatively small table. There must be
something more to this than is obvious. Your last thread included a
**bunch** of indexes. Was this accurate? The information posted wasn't
easily understandable since it did not clearly identify the columns used in
the various indexes (don't particularly care about names or filegroups). Is
there no primary key? If so, then this starts to look like an issue with a
poorly designed schema. A table with lots of NULLable columns can also be
an indication of schema design problems.
> There are no triggers on this table, so what other side-effects migth
> there be?
Are you absolutely certain? The reason I ask is because of the
datetime/user columns in the table. It would seem that the creation and
last modified columns are populated via a trigger (unless you are purposely
trying to avoid changing the last updated columns) - they are not nullable
and have no defaults and columns of this nature are usually populated via
some automated mechanism (if only to avoid problems with programmers or
administrators forgetting to do these sorts of things).
Are there foreign keys in other tables that point to chProductNumber? Are
there indexed views that reference the table? You might want to investigate
the relationships this table has to any thing else in the database.|||Very good point, no I am not sure about the triggers/outside effects. I did
check that it does not seem to fire any triggers thru the Manage Triggers
command in QA. Here is the showplan_all for the query if that is helpful.
UPDATE CustomerProduct SET chProductNumber='PAFGLPLK0B0BMS0EVDML' WHERE
chProductNumber='PAFGLPLK0B0BMS0RTDEN' 1 1 0 NULL NULL 1 NULL 13926.935 NULL
NULL NULL 2.7688262 NULL NULL UPDATE 0 NULL
|--Sequence 1 2 1 Sequence Sequence NULL NULL 13926.935 0.0 5.5707738E-2
27 2.7688262 NULL NULL PLAN_ROW 0 1.0
|--Index
Update(OBJECT:([onyx].[dbo].[CustomerProduct].[customerproduct_Site_ProductNumber]),
SET:([iProductId1010]=[CustomerProduct].[iProductId],
[chProductNumber1009]=RaiseIfNull([CustomerProduct].[chProductNumber]),
[iSiteId1008]=[CustomerProduc 1 3 2 Index Update Update
OBJECT:([onyx].[dbo].[CustomerProduct].[customerproduct_Site_ProductNumber]),
SET:([iProductId1010]=[CustomerProduct].[iProductId],
[chProductNumber1009]=RaiseIfNull([CustomerProduct].[chProductNumber]),
[iSiteId1008]=[CustomerProduct].[iSiteId], [IdxBmk10 NULL 27853.869
1.0026711E-2 2.7853869E-2 27 1.3565593 NULL NULL PLAN_ROW 0 1.0
| |--Table Spool 1 4 3 Table Spool Eager Spool NULL NULL 27853.869
0.79318851 5.145763E-3 59 1.3186787 [CustomerProduct].[chProductNumber],
[Bmk1003], [CustomerProduct].[iProductId], [CustomerProduct].[iSiteId],
[Act1006] NULL PLAN_ROW 0 1.0
| |--Split 1 5 4 Split Split NULL [Act1006] 27853.869 0.0
0.19428073 59 1.01403 [CustomerProduct].[chProductNumber], [Bmk1003],
[CustomerProduct].[iProductId], [CustomerProduct].[iSiteId], [Act1006] NULL
PLAN_ROW 0 1.0
| |--Clustered Index
Update(OBJECT:([onyx].[dbo].[CustomerProduct].[PKNUCCustomerProduct]),
SET:([CustomerProduct].[chProductNumber]=RaiseIfNull(Convert([@.1])))) 1 6 5
Clustered Index Update Update
OBJECT:([onyx].[dbo].[CustomerProduct].[PKNUCCustomerProduct]),
SET:([CustomerProduct].[chProductNumber]=RaiseIfNull(Convert([@.1]))) NULL
13926.935 1.0126539E-2 1.3926934E-2 93 0.8197493
[CustomerProduct].[chProductNumber], [Bmk1003],
[CustomerProduct].[iProductId], [CustomerProduct].[iSiteId], [ConstExpr1005]
NULL PLAN_ROW 0 1.0
| |--Compute
Scalar(DEFINE:([ConstExpr1005]=Convert([@.1]))) 1 7 6 Compute Scalar Compute
Scalar DEFINE:([ConstExpr1005]=Convert([@.1])) [ConstExpr1005]=Convert([@.1])
13926.935 0.0 1.3926935E-3 75 0.79569584 [Bmk1000],
[CustomerProduct].[chProductNumber], [CustomerProduct].[iProductId],
[CustomerProduct].[iSiteId], [ConstExpr1005] NULL PLAN_ROW 0 1.0
| |--Table Spool 1 8 7 Table Spool Eager Spool
NULL NULL 13926.935 0.72851229 5.0140964E-3 55 0.79430312 [Bmk1000],
[CustomerProduct].[chProductNumber], [CustomerProduct].[iProductId],
[CustomerProduct].[iSiteId] NULL PLAN_ROW 0 1.0
| |--Top(ROWCOUNT est 0) 1 9 8 Top Top
NULL NULL 13926.935 0.0 1.3926935E-3 55 6.0776766E-2 [Bmk1000],
[CustomerProduct].[chProductNumber], [CustomerProduct].[iProductId],
[CustomerProduct].[iSiteId] NULL PLAN_ROW 0 1.0
| |--Index
Seek(OBJECT:([onyx].[dbo].[CustomerProduct].[NNXcustomerproduct_prodnumber]),
SEEK:([CustomerProduct].[chProductNumber]='PAFGLPLK0B0BMS0RTDEN') ORDERED
FORWARD) 1 10 9 Index Seek Index Seek
OBJECT:([onyx].[dbo].[CustomerProduct].[NNXcustomerproduct_prodnumber]),
SEEK:([CustomerProduct].[chProductNumber]='PAFGLPLK0B0BMS0RTDEN') ORDERED
FORWARD [Bmk1000], [CustomerProduct].[chProductNumber],
[CustomerProduct].[iProductId], [CustomerProduct].[iSiteId] 13926.935
4.3944165E-2 1.5439909E-2 55 5.9384074E-2 [Bmk1000],
[CustomerProduct].[chProductNumber], [CustomerProduct].[iProductId],
[CustomerProduct].[iSiteId] NULL PLAN_ROW 0 1.0
|--Index
Update(OBJECT:([onyx].[dbo].[CustomerProduct].[NNXcustomerproduct_prodnumber]),
SET:([iSiteId1014]=[CustomerProduct].[iSiteId],
[iProductId1013]=[CustomerProduct].[iProductId],
[chProductNumber1012]=RaiseIfNull([CustomerProduct].[chProductN 1 14 2 Index
Update Update
OBJECT:([onyx].[dbo].[CustomerProduct].[NNXcustomerproduct_prodnumber]),
SET:([iSiteId1014]=[CustomerProduct].[iSiteId],
[iProductId1013]=[CustomerProduct].[iProductId],
[chProductNumber1012]=RaiseIfNull([CustomerProduct].[chProductNumber]),
[IdxBmk1011]=R NULL 27853.869 1.0026711E-2 2.7853869E-2 27 1.3565593 NULL
NULL PLAN_ROW 0 1.0
|--Table Spool 1 4 14 Table Spool Eager Spool NULL NULL
27853.869 0.79318851 5.145763E-3 59 1.3186787
[CustomerProduct].[chProductNumber], [Bmk1003],
[CustomerProduct].[iProductId], [CustomerProduct].[iSiteId], [Act1006] NULL
PLAN_ROW 0 1.0
"Scott Morris" <bogus@.bogus.com> wrote in message
news:esnXxXL1FHA.1264@.tk2msftngp13.phx.gbl...
>>the actual statement:
>> UPDATE CustomerProduct
>> SET chProductNumber='PAFGLPLK0B0BMS0EVDML'
>> WHERE chProductNumber='PAFGLPLK0B0BMS0RTDEN'
>>an understanding of the distribution of data in the affected table(s):
>> 13910 of 1283741 records need to be updated
> A straightforward update on a relatively small table. There must be
> something more to this than is obvious. Your last thread included a
> **bunch** of indexes. Was this accurate? The information posted wasn't
> easily understandable since it did not clearly identify the columns used
> in the various indexes (don't particularly care about names or
> filegroups). Is there no primary key? If so, then this starts to look
> like an issue with a poorly designed schema. A table with lots of
> NULLable columns can also be an indication of schema design problems.
>> There are no triggers on this table, so what other side-effects migth
>> there be?
> Are you absolutely certain? The reason I ask is because of the
> datetime/user columns in the table. It would seem that the creation and
> last modified columns are populated via a trigger (unless you are
> purposely trying to avoid changing the last updated columns) - they are
> not nullable and have no defaults and columns of this nature are usually
> populated via some automated mechanism (if only to avoid problems with
> programmers or administrators forgetting to do these sorts of things).
> Are there foreign keys in other tables that point to chProductNumber? Are
> there indexed views that reference the table? You might want to
> investigate the relationships this table has to any thing else in the
> database.
>
>|||Difficult to read with all the formatting / re-formatting done between your
posting and my reading. Plus, I am no expert in reading the text plan
output. Perhaps others can decipher it. If you use the graphical plan, you
should see any "extra" statements executed in triggers.
I'm not aware of any "manager triggers" command in QA. Are you using sql
server 2000 or another version?|||My bad, it's in the EM under the right click menu. Yeah, I looked at the
graphical version and it was pretty cut and dried, but there was one level
below the main that took like 48% of the time. I had though about posting an
attachment in NotePad. I'm thinking it is just a culmination of a few things
that is causing the slowness, so for now I am just going to run the query at
a very slow time and watch to like a hawk. Thanks for the help.
Willie
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23SQ6z0N1FHA.2616@.tk2msftngp13.phx.gbl...
> Difficult to read with all the formatting / re-formatting done between
> your posting and my reading. Plus, I am no expert in reading the text
> plan output. Perhaps others can decipher it. If you use the graphical
> plan, you should see any "extra" statements executed in triggers.
> I'm not aware of any "manager triggers" command in QA. Are you using sql
> server 2000 or another version?
>

Drop and Recreate Excel table

I'm having a heck of a time trying to upload data to an excel spreadsheet. This works perfectly in sql 2000 but I've been having problems with 2005

SSIS package "Package1.dtsx" starting.
Error: 0xC002F210 at Drop table(s) SQL Task, Execute SQL Task: Executing the query "drop table `GRE`
" failed with the following error: "Table 'xxx' does not exist.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Drop table(s) SQL Task
Error: 0xC002F210 at Preparation SQL Task, Execute SQL Task: Executing the query "CREATE TABLE `xxx` (
`TEST_REC_NBR` Decimal(29,0),
`PROCESS_DT_GRE` LongText
)
" failed with the following error: "Invalid precision for decimal data type.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Preparation SQL Task
SSIS package "Package1.dtsx" finished: Failure.

It looks like the problem is Decimal (29,0) - seems it is not a valid precision for excel. I tried a create table statement with Decimal (28,0) and it succeeded while Decimal(29,0) gave the same error. I think the maximum precision Excel supports for Decimal is 28.|||This is the output when I choose create and drop the table AND when I try to delete the rows. I've changed the field to decimal(28,0) also.
I can get around the problem by using a file system task and removing the file. This will work for what I'm doing but what if I need to delete only certain rows?
- Validating (Error)
Messages
Error 0xc001000e: {5143E747-D851-4E0F-9335-CCBF5E66371E}: The connection "DestinationConnectionOLEDB" is not found. This error is thrown by Connections collection when the specific connection element is not found.
(SQL Server Import and Export Wizard)
Error 0xc001000e: {5143E747-D851-4E0F-9335-CCBF5E66371E}: The connection "DestinationConnectionOLEDB" is not found. This error is thrown by Connections collection when the specific connection element is not found.
(SQL Server Import and Export Wizard)
Error 0xc00291eb: Drop table(s) SQL Task: Connection manager "DestinationConnectionOLEDB" does not exist.
(SQL Server Import and Export Wizard)
Error 0xc0024107: Drop table(s) SQL Task: There were errors during task validation.
(SQL Server Import and Export Wizard)

|||

I think you have hit a bug in Import Export Wizard, where the connection for the Execute SQL Task which drops the table is set incorrectly. I will investigate further.

|||

Ranjeeta wrote:

I think you have hit a bug in Import Export Wizard, where the connection for the Execute SQL Task which drops the table is set incorrectly. I will investigate further.

I am having trouble deleting Excel 2007 sheets programmatically. In the earlier versions of Excel, I used to open an ADO connection and executed some code like below

strSQL = "DROP TABLE NameOfExcelSheet;"

objCommand = New OleDb.OleDbCommand(strSQL, cnn)

objCommand.ExecuteNonQuery()

By executing the above code I could delete the sheet I wanted from an Excel workbook. But this method does NOT work with Excel 2007.

How can I programmatically delete sheets from an Excel 2007 workbook?

|||

Sorry... please ignore my previous comments and question.

strSQL = "DROP TABLE NameOfExcelSheet;" only clears the contects of an Excel sheet and does not delete the sheet itself on all versions of Excel.

Drop and Recreate Excel table

I'm having a heck of a time trying to upload data to an excel spreadsheet. This works perfectly in sql 2000 but I've been having problems with 2005

try to delete the rows. I've changed the field to decimal(28,0)

also.

I can get around the problem by using a file system task

and removing the file. This will work for what I'm doing

but what if I need to delete only certain rows?

- Validating (Error)

Messages

Error 0xc001000e: {5143E747-D851-4E0F-9335-CCBF5E66371E}: The

connection "DestinationConnectionOLEDB" is not found. This error is

thrown by Connections collection when the specific connection element

is not found.

(SQL Server Import and Export Wizard)
Error 0xc001000e: {5143E747-D851-4E0F-9335-CCBF5E66371E}: The

connection "DestinationConnectionOLEDB" is not found. This error is

thrown by Connections collection when the specific connection element

is not found.

(SQL Server Import and Export Wizard)
Error 0xc00291eb: Drop table(s) SQL Task: Connection manager "DestinationConnectionOLEDB" does not exist.

(SQL Server Import and Export Wizard)
Error 0xc0024107: Drop table(s) SQL Task: There were errors during task validation.

(SQL Server Import and Export Wizard)|||

I think you have hit a bug in Import Export Wizard, where the connection for the Execute SQL Task which drops the table is set incorrectly. I will investigate further.

|||

Ranjeeta wrote:

I think you have hit a bug in Import Export Wizard, where the connection for the Execute SQL Task which drops the table is set incorrectly. I will investigate further.

I am having trouble deleting Excel 2007 sheets programmatically. In the earlier versions of Excel, I used to open an ADO connection and executed some code like below

strSQL = "DROP TABLE NameOfExcelSheet;"

objCommand = New OleDb.OleDbCommand(strSQL, cnn)

objCommand.ExecuteNonQuery()

By executing the above code I could delete the sheet I wanted from an Excel workbook. But this method does NOT work with Excel 2007.

How can I programmatically delete sheets from an Excel 2007 workbook?

|||

Sorry... please ignore my previous comments and question.

strSQL = "DROP TABLE NameOfExcelSheet;" only clears the contects of an Excel sheet and does not delete the sheet itself on all versions of Excel.

Wednesday, March 7, 2012

Drop and Recreate all objects except tables

Could someone advise me on the best way to drop and recreate all objects in a database except tables (considering I must drop and create them in the correct order of dependency). More specifically, I would like to drop and recreate:

Indexes

Defaults

FK Constraints

Views

Procs

UDF

Check Constraints

Must I loop through every object in the database or is there any way to do this en masse?

Thanks.

I am currently working on creating a SSIS package that achieves this. I am using a ForEach Loop container (which contains a script task and Execute SQL task) for my tables, view, stored procedures and functions. The loop container uses a ForEach SMO enumerator. In my case I wanted to transfer objects from a different database so I added a Transfer SQL objects task to transfer objects. So far this is working pretty well.

I am having one issue though and that is with dropping my defaults. I haven't figured out how to get my SMO enumerator to pick up my defaults. If anyone can shed some light on how to configure this option I would appreciate it.

Thanks.

|||

Take a look at the Transfer object that allows you to create compound scripts in dependency order.

A little more elaborate, but you can also use the Scripter.

See also http://blogs.msdn.com/mwories/archive/2005/05/07/basic-scripting.aspx for a primer on scripting.

|||

Does anyone have any samples that show looping through every table in the database, then for each table dropping any foreign key constraints and indexes, then dropping the table itself?

Thanks.

Drop and Recreate all objects except tables

Indexes Defaults FK Constraints Views Procs UDF Check Constraints

Must I loop through every object in the database or is there any way to do this en masse?

Thanks.

|||

Take a look at the Transfer object that allows you to create compound scripts in dependency order.

A little more elaborate, but you can also use the Scripter.

See also http://blogs.msdn.com/mwories/archive/2005/05/07/basic-scripting.aspx for a primer on scripting.

|||

Does anyone have any samples that show looping through every table in the database, then for each table dropping any foreign key constraints and indexes, then dropping the table itself?

Thanks.

Drop and Recreate all objects except tables

Indexes Defaults FK Constraints Views Procs UDF Check Constraints

Must I loop through every object in the database or is there any way to do this en masse?

Thanks.

|||

Take a look at the Transfer object that allows you to create compound scripts in dependency order.

A little more elaborate, but you can also use the Scripter.

See also http://blogs.msdn.com/mwories/archive/2005/05/07/basic-scripting.aspx for a primer on scripting.

|||

Does anyone have any samples that show looping through every table in the database, then for each table dropping any foreign key constraints and indexes, then dropping the table itself?

Thanks.

|||You can use SMO for dropping and recreating the Database objects.
|||Looping to all databases in the Server
public Database SqlPackageAdminServer(string ConnectionString, string sDatabase)
{

Server oServer = GetServer(ConnectionString);
Database oDatabase = oServer.Databases[sDatabase];
return oDatabase;
}

private Server GetServer(string ConnectionString)
{
SqlConnection oSqlConnection = new SqlConnection(ConnectionString);
oSqlConnection.Open();
ServerConnection oServerConnection = new ServerConnection(oSqlConnection);
Server oServer = new Server(oServerConnection);
return oServer;
}

public DataTable GetAllDatabases(Server oServer)
{
DataTable oDataTable = new DataTable("Databases");
foreach (Database oDatabase in oServer.Databases)
{
if (oDatabase.IsSystemObject != true)
{
AddDataToDataTable(oDatabase.Name, oDataTable);
}

}
return oDataTable;
}

Looping to all the tables in the database
foreach (Table oTable in DataBase.Tables)
{
/// oTable.Name is the name of the table
}

to use this code you should include
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using System.Data;

Drop and recreate all indexes

Sql server 2005
There are a couple of administrators who have repeatedly said they
dropped and recreated indexes
and performance spiked after a migration and it would be nice to leave
no stone unturned in a bid to better performance.
Has anyone come across a script or has a way to do this
Your input as usual is greatly appreciated
MikeThere are many flavors out there... but this is a pretty common way to
reindex your tables.
The DBCC DBREINDEX statement is the key!
!UNTESTED SQL!
USE DatabaseName --Enter the name of the database you want to reindex
DECLARE @.TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@.TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @.TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
"Massa Batheli" <mngong@.gmail.com> wrote in message
news:1161357089.891287.7040@.e3g2000cwe.googlegroups.com...
> Sql server 2005
> There are a couple of administrators who have repeatedly said they
> dropped and recreated indexes
> and performance spiked after a migration and it would be nice to leave
> no stone unturned in a bid to better performance.
> Has anyone come across a script or has a way to do this
> Your input as usual is greatly appreciated
>
> Mike
>|||Hi
Check out ALTER INDEX ALL in Books Online, example D in the topic
sys.dm_db_index_physical_stats shows you how you can call this for multiple
tables http://msdn2.microsoft.com/en-us/library/ms188917.aspx
John
"Massa Batheli" wrote:
> Sql server 2005
> There are a couple of administrators who have repeatedly said they
> dropped and recreated indexes
> and performance spiked after a migration and it would be nice to leave
> no stone unturned in a bid to better performance.
> Has anyone come across a script or has a way to do this
> Your input as usual is greatly appreciated
>
> Mike
>|||Thank you so much Immy.
Just ran something similar and the next step was to run a drop index
...
and recreate index ...
That is what is help is needed with ,again thank you for your time and
I appreciate more ideas|||Hi
DBCC DBREINDEX may be removed in future versions of SQL Server, therefore if
you are writing production code you should consider using ALTER INDEX.
John
"Massa Batheli" wrote:
> Thank you so much Immy.
> Just ran something similar and the next step was to run a drop index
> ...
> and recreate index ...
> That is what is help is needed with ,again thank you for your time and
> I appreciate more ideas
>|||As said earlier John the purpose is to completely drop and rebuild
indexes
Not sure why that has to be done but still looking for ways to do that
on instructions
Reason for this post
.....
John Bell wrote:
> Hi
> DBCC DBREINDEX may be removed in future versions of SQL Server, therefore if
> you are writing production code you should consider using ALTER INDEX.
> John
> "Massa Batheli" wrote:
> > Thank you so much Immy.
> > Just ran something similar and the next step was to run a drop index
> > ...
> > and recreate index ...
> > That is what is help is needed with ,again thank you for your time and
> > I appreciate more ideas
> >
> >|||DBCC DBREINDEX and ALTER INDEX with the REBILD option will execute the same code internally as if
you do DROP INDEX and then CREATE INDEX.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Massa Batheli" <mngong@.gmail.com> wrote in message
news:1161806390.411989.59800@.i42g2000cwa.googlegroups.com...
> As said earlier John the purpose is to completely drop and rebuild
> indexes
> Not sure why that has to be done but still looking for ways to do that
> on instructions
> Reason for this post
>
> .....
> John Bell wrote:
>> Hi
>> DBCC DBREINDEX may be removed in future versions of SQL Server, therefore if
>> you are writing production code you should consider using ALTER INDEX.
>> John
>> "Massa Batheli" wrote:
>> > Thank you so much Immy.
>> > Just ran something similar and the next step was to run a drop index
>> > ...
>> > and recreate index ...
>> > That is what is help is needed with ,again thank you for your time and
>> > I appreciate more ideas
>> >
>> >
>|||Hi
Rebuilding indexes should certainly be done post upgrading to SQL 2005, and
you should periodically rebuild your indexes to remove fragmentation to make
sure that will perform efficiently. You should also look at updating
statistics and usage. Check out the view sys.dm_db_index_physical_stats in
books online, which will give you an example script for rebuilding indexes if
they are fragmented by a certain amount.
John
"Massa Batheli" wrote:
> As said earlier John the purpose is to completely drop and rebuild
> indexes
> Not sure why that has to be done but still looking for ways to do that
> on instructions
> Reason for this post
>
> ......
> John Bell wrote:
> > Hi
> >
> > DBCC DBREINDEX may be removed in future versions of SQL Server, therefore if
> > you are writing production code you should consider using ALTER INDEX.
> >
> > John
> >
> > "Massa Batheli" wrote:
> >
> > > Thank you so much Immy.
> > > Just ran something similar and the next step was to run a drop index
> > > ...
> > > and recreate index ...
> > > That is what is help is needed with ,again thank you for your time and
> > > I appreciate more ideas
> > >
> > >
>

Drop and recreate all indexes

Sql server 2005
There are a couple of administrators who have repeatedly said they
dropped and recreated indexes
and performance spiked after a migration and it would be nice to leave
no stone unturned in a bid to better performance.
Has anyone come across a script or has a way to do this
Your input as usual is greatly appreciated
MikeThere are many flavors out there... but this is a pretty common way to
reindex your tables.
The DBCC DBREINDEX statement is the key!
!UNTESTED SQL!
USE DatabaseName --Enter the name of the database you want to reindex
DECLARE @.TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@.TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @.TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
"Massa Batheli" <mngong@.gmail.com> wrote in message
news:1161357089.891287.7040@.e3g2000cwe.googlegroups.com...
> Sql server 2005
> There are a couple of administrators who have repeatedly said they
> dropped and recreated indexes
> and performance spiked after a migration and it would be nice to leave
> no stone unturned in a bid to better performance.
> Has anyone come across a script or has a way to do this
> Your input as usual is greatly appreciated
>
> Mike
>|||Hi
Check out ALTER INDEX ALL in Books Online, example D in the topic
sys.dm_db_index_physical_stats shows you how you can call this for multiple
tables http://msdn2.microsoft.com/en-us/library/ms188917.aspx
John
"Massa Batheli" wrote:

> Sql server 2005
> There are a couple of administrators who have repeatedly said they
> dropped and recreated indexes
> and performance spiked after a migration and it would be nice to leave
> no stone unturned in a bid to better performance.
> Has anyone come across a script or has a way to do this
> Your input as usual is greatly appreciated
>
> Mike
>|||Thank you so much Immy.
Just ran something similar and the next step was to run a drop index
...
and recreate index ...
That is what is help is needed with ,again thank you for your time and
I appreciate more ideas|||Hi
DBCC DBREINDEX may be removed in future versions of SQL Server, therefore if
you are writing production code you should consider using ALTER INDEX.
John
"Massa Batheli" wrote:

> Thank you so much Immy.
> Just ran something similar and the next step was to run a drop index
> ...
> and recreate index ...
> That is what is help is needed with ,again thank you for your time and
> I appreciate more ideas
>|||As said earlier John the purpose is to completely drop and rebuild
indexes
Not sure why that has to be done but still looking for ways to do that
on instructions
Reason for this post
.....
John Bell wrote:[vbcol=seagreen]
> Hi
> DBCC DBREINDEX may be removed in future versions of SQL Server, therefore
if
> you are writing production code you should consider using ALTER INDEX.
> John
> "Massa Batheli" wrote:
>|||DBCC DBREINDEX and ALTER INDEX with the REBILD option will execute the same
code internally as if
you do DROP INDEX and then CREATE INDEX.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Massa Batheli" <mngong@.gmail.com> wrote in message
news:1161806390.411989.59800@.i42g2000cwa.googlegroups.com...
> As said earlier John the purpose is to completely drop and rebuild
> indexes
> Not sure why that has to be done but still looking for ways to do that
> on instructions
> Reason for this post
>
> .....
> John Bell wrote:
>|||Hi
Rebuilding indexes should certainly be done post upgrading to SQL 2005, and
you should periodically rebuild your indexes to remove fragmentation to make
sure that will perform efficiently. You should also look at updating
statistics and usage. Check out the view sys.dm_db_index_physical_stats in
books online, which will give you an example script for rebuilding indexes i
f
they are fragmented by a certain amount.
John
"Massa Batheli" wrote:

> As said earlier John the purpose is to completely drop and rebuild
> indexes
> Not sure why that has to be done but still looking for ways to do that
> on instructions
> Reason for this post
>
> ......
> John Bell wrote:
>

Drop All DB Indexes

Hi, All
I need to drop all indexes in a DB and then recreate them with a script. The
recreate is not a problem, but I've struggling to find an example script of
how to drop all user indexes in a DB. I will need to know if the index is
being used to enforce a primary key and if so I'll have to remove any
contraints before attempting the drop.
The other thing I've notice when selecting all tbl's from sysobject where
type='U' it's showing a table called dtProperties which is not a user table.
This table shows as a system table in the enterprise manager. Why is it
specified as 'U' in the sysobjects?
I'd really appreciate any help I can get
Thanks
Antony
If you automate the drop, how will you automate the re-create? The re-create is the difficult part
as you need to know the columns, whether it is unique, whether it is clustered, PK, UNIQUE etc.
Anyhow, some tips on automating the DROP:

> I need to drop all indexes in a DB and then recreate them with a script. The
> recreate is not a problem, but I've struggling to find an example script of
> how to drop all user indexes in a DB.
Use a cursor to loop sysindexes and inside the cursor use dynamic SQL to drop the indexes. Use
INDEXPROPERTY to filter out statistics and hypothetical indexes. Also, OBJECTPROPERTY to filter out
non-system tables.

> I will need to know if the index is
> being used to enforce a primary key and if so I'll have to remove any
> contraints before attempting the drop.
Use the status column for this (see Books Online, sysindexes).

> The other thing I've notice when selecting all tbl's from sysobject where
> type='U' it's showing a table called dtProperties which is not a user table.
> This table shows as a system table in the enterprise manager. Why is it
> specified as 'U' in the sysobjects?
Historical screw-up. Use OBJECTPROPERTY and the IsMsShipped option.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Antony" <Antony@.discussions.microsoft.com> wrote in message
news:C5FC191E-E418-44CA-9DA4-1C4B13C7C4CD@.microsoft.com...
> Hi, All
> I need to drop all indexes in a DB and then recreate them with a script. The
> recreate is not a problem, but I've struggling to find an example script of
> how to drop all user indexes in a DB. I will need to know if the index is
> being used to enforce a primary key and if so I'll have to remove any
> contraints before attempting the drop.
> The other thing I've notice when selecting all tbl's from sysobject where
> type='U' it's showing a table called dtProperties which is not a user table.
> This table shows as a system table in the enterprise manager. Why is it
> specified as 'U' in the sysobjects?
> I'd really appreciate any help I can get
> Thanks
> Antony

Drop All DB Indexes

Hi, All
I need to drop all indexes in a DB and then recreate them with a script. The
recreate is not a problem, but I've struggling to find an example script of
how to drop all user indexes in a DB. I will need to know if the index is
being used to enforce a primary key and if so I'll have to remove any
contraints before attempting the drop.
The other thing I've notice when selecting all tbl's from sysobject where
type='U' it's showing a table called dtProperties which is not a user table.
This table shows as a system table in the enterprise manager. Why is it
specified as 'U' in the sysobjects?
I'd really appreciate any help I can get
Thanks
AntonyIf you automate the drop, how will you automate the re-create? The re-create is the difficult part
as you need to know the columns, whether it is unique, whether it is clustered, PK, UNIQUE etc.
Anyhow, some tips on automating the DROP:
> I need to drop all indexes in a DB and then recreate them with a script. The
> recreate is not a problem, but I've struggling to find an example script of
> how to drop all user indexes in a DB.
Use a cursor to loop sysindexes and inside the cursor use dynamic SQL to drop the indexes. Use
INDEXPROPERTY to filter out statistics and hypothetical indexes. Also, OBJECTPROPERTY to filter out
non-system tables.
> I will need to know if the index is
> being used to enforce a primary key and if so I'll have to remove any
> contraints before attempting the drop.
Use the status column for this (see Books Online, sysindexes).
> The other thing I've notice when selecting all tbl's from sysobject where
> type='U' it's showing a table called dtProperties which is not a user table.
> This table shows as a system table in the enterprise manager. Why is it
> specified as 'U' in the sysobjects?
Historical screw-up. Use OBJECTPROPERTY and the IsMsShipped option.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Antony" <Antony@.discussions.microsoft.com> wrote in message
news:C5FC191E-E418-44CA-9DA4-1C4B13C7C4CD@.microsoft.com...
> Hi, All
> I need to drop all indexes in a DB and then recreate them with a script. The
> recreate is not a problem, but I've struggling to find an example script of
> how to drop all user indexes in a DB. I will need to know if the index is
> being used to enforce a primary key and if so I'll have to remove any
> contraints before attempting the drop.
> The other thing I've notice when selecting all tbl's from sysobject where
> type='U' it's showing a table called dtProperties which is not a user table.
> This table shows as a system table in the enterprise manager. Why is it
> specified as 'U' in the sysobjects?
> I'd really appreciate any help I can get
> Thanks
> Antony

Sunday, February 26, 2012

Drop & Create sProc

I generated SQL Script to drop and recreate some tables in my database
that I will want to perform monthly. I would like to put the script into a
sproc, however when I try to compile it I receive an error that the tables
and indexes already exist in my database.
Is there any way around this other than dropping all the tables before I
compile
the sproc?
Thanks,
MarcEncapsulate the CREATE / DROP statements within an IF ?
IF OBJECT_ID('[YourObject]','U') IS NOT NULL
BEGIN
DROP ....
CREATE ...
END
"Marc Miller" wrote:

> I generated SQL Script to drop and recreate some tables in my database
> that I will want to perform monthly. I would like to put the script into
a
> sproc, however when I try to compile it I receive an error that the tables
> and indexes already exist in my database.
> Is there any way around this other than dropping all the tables before I
> compile
> the sproc?
> Thanks,
> Marc
>
>|||Marc Miller (mm1284@.hotmail.com) writes:
> I generated SQL Script to drop and recreate some tables in my database
> that I will want to perform monthly. I would like to put the script
> into a sproc, however when I try to compile it I receive an error that
> the tables and indexes already exist in my database.
> Is there any way around this other than dropping all the tables before I
> compile the sproc?
Unless you are running SQL 6.5, you should not get that error.
I suspect that you have something that looks like:
CREATE PROCEDURE yoursp AS
CREATE TABLE abc
CREATE INDEX abc_ix ON abc(def)
go
CREATE TABLE xyz
CREATE INDEX xyz_ix ON xyz(www)
go
This is a script that first creates a procedure, and then creates a table.
This is because the script includes "go" which is an instruction to
the query tool that this is where the batch ends. "go" is not an SQL
command.
Thus, you would have to remove the "go" to include all code in the
stored procedure. However, you may then find that run into other
problems, because you have commands that must be in separate batches.
I would suggest that you store the script as a separate file. This is
would you should with stored procedures as well. The database should
be seen as a binary repository.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Monday, March 19, 2012

DROP PROCS

DROP PROCS

DROP PROCS

Friday, March 9, 2012

drop and recreate views in mssql server

Drop and Recreate subscription

Drop and recreate indexes

Drop and recreate indexes

Drop and recreate indexes

Drop and Recreate Excel table

Drop and Recreate Excel table

Wednesday, March 7, 2012

Drop and Recreate all objects except tables

Drop and Recreate all objects except tables

Drop and Recreate all objects except tables

Drop and recreate all indexes

Drop and recreate all indexes

Drop and recreate all indexes

Drop All DB Indexes

Drop All DB Indexes

Sunday, February 26, 2012

Drop & Create sProc

DSO in SQL2005

Blog Archive

About Me