How To Script Off Indexes Prior To Dropping Them?

Oct 10, 2007

This is for SQL 2005 and I know how to right click an index and do a "script index as create to new query window"

Basically, the one of the Microsoft scripts or views will tell us that we have 100+ indexes that exist for a database but that they are not being used by SQL server.

I will probably take them "offline" for a while and then drop them later on.

Before dropping them, I would like to be able to recreate them within minutes if system performance is degraded after this happens.

I was working on a script to pull this info out of the sys. tables like below but was wondering if anyone already has a script or an easier way to do this. (...and I don't want to right click 100 indexes within 137 tables and script to a new window and then compile a big script)



SELECT

'Create ' +

i.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS +

' Index [' +

i.name +

'] ON ' +

t.name +

CHAR(10) +

c.name +

' ASC'

FROM sys.index_columns ic

Join sys.indexes i ON i.index_id = ic.index_id

JOIN sys.tables t ON ic.object_id = t.object_id

Join sys.columns c ON ic.column_id = c.column_id

WHERE

i.name = 'IX_Sellers_StatusID' and

c.object_id = t.object_id and

i.object_id = ic.object_id

Thanks,
Brian

View 3 Replies


ADVERTISEMENT

Dropping All Indexes

Jan 3, 2007

Looking for suggestions.

I have a database that is giving me a bad Index error. When I go to drop the necessary Index it is telling me that it either does not exist or cannot be dropped. However when I try to build that index, it tells me one already exists.

Is there any way to drop all of the indexes or at the very least see what the indexes are? This particular database is using 2005 Express.

Any help would be great!
Shawn

View 4 Replies View Related

Dropping Clustered Indexes

Sep 20, 2001

SQL 7 created by default a clustered index on my primary key field. I would like to drop this index and recreate it on another field, but it is not allowing me. Error message states: "An explicit DROP INDEX is not allowed... It is being used for PRIMARY KEY CONSTRAINT enforcement." Can anybody advise how I can solve this? TIA

View 1 Replies View Related

MS SQL Server 7 Dropping Indexes

Mar 2, 2000

Hi
Has anyone heard of MS SQL Server 7 dropping indexes?
I had created an index on a table. The next day
the index had disappeared. Has anyone expirienced such
a problem?

Thanks in advance

Winston

View 1 Replies View Related

Dropping Indexes And Recreating The Same

May 1, 2008

I would like to know if there is a way to drop/ disenable all the indexes in a maintenance plan?
Or is it better to write scripts for dropping indexes and recreatig the same?

Purpose: Need to drop indexes(not the Primary key) before loading data and recreate the same after loading

Looking forward for suggestions/Solutions!

Thanks,
Janani

View 2 Replies View Related

SP To Check For Existing Indexes Before Dropping.

Nov 7, 2000

I need to add syntax to a stored procedure to check for the existence of a specific indexes on a table before dropping it. If they do not exist I need it to NOT through an error message. Performing this on a table is relatively simple:
if exists (select * from sysobjects where id = object_id(N'[dbo].[TABLENAME]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TABLENAME]

Since indexes are not represented in sysobjects how can I do this?

View 5 Replies View Related

Dropping The Indexes On Production Server

Jun 24, 2008

Dear All,
i'm planning to drop all the non clustered indexes (as they are not congigured well) on production database, and run the latest script to create fresh non clustered indexes on specific columns.

now my doubts are
1)will the replication affect with dropping and recreating of indexes?
2)query to drop all the non clustered indexes on that database....
can i use the query delete from sysindexes where indid>1
will the query works for me to drop all the non clustered indexes?
3)is it necessary to generate a snapshot again after creating the new indexes? or can i drop and run at subscriber also?

please guide me in this regard




Arnav
Even you learn 1%, Learn it with 100% confidence.

View 3 Replies View Related

Dropping Indexes Before Bulk Insert

Aug 16, 2007

Hi all,


I have a huge table 170 Gb of size. On that table we have 10 indexes
of around 12 GB in size.

The application is designed such that it bulk inserts the file in to
sql server. But , often we are getting time outs and some latching
isssues ( as can be seen in activity monitor).

So, will this be a good idea of dropping those indexes and then
recreating them again for better performance.

1) Its SQL 2005 Standard Edition SP1

2) Databases are in SIMPLE Recovery mode.

3) Database is not OLTP.

Thanks.

//N

View 2 Replies View Related

Removal Of Selected Indexes / Script Index Create For List Of Indexes

Jul 1, 2014

I'm working to improve performance on a database I've inherited, and there are several thousand indexes. I've got a list of ones which should definitely exist within the database, and I'm looking to strip out all the others and start fresh, though this list is still quite large (1000 or so).

Is there a way I can remove all the indexes that are not in my list without too much trouble? I.e. without having to manually go through them all individually. The list is currently in a csv file.

I'm looking to either automate the removal of indexes not in the list, or possibly to generate the Create statements for the indexes on the list and simply remove all indexes and then run these statements.

As an aside, when trying to list all indexes in the database, I've found various scripts to do this, but found they all seem to produce differing results. What is the best script to list all indexes?

View 5 Replies View Related

Connect By Prior

Aug 4, 2004

Hello,
I have to migrate an application from oracle to sqlserver but i habe query with
connect by prior
What is the equivalent in sqlserver 2000.
Please help me.

View 2 Replies View Related

Prior Sql Queries

Mar 1, 2006

Is there any way to check 1-2 days prior sql queries executed..??

However, i am going for auditing now.

Thanks,
Pavan

View 1 Replies View Related

Prior Omit

Apr 13, 2006

Hello, Is there a way to do a prior omit in a SELECT?

I currently have multiple selections separated by a GO, but I need to prior Omit the rows I selected prior

so I have

select * from mytabe where
Address <> ' ' and
Zip4 <> ' ' and
zip in ('12345')
Go

select * from mytabe where
Address <> ' ' and
Zip4 <> ' ' and
Purchcode in ('A','C')
Go

This continues for another 10 selections I need to constantly omit the selections made above.

Is there a way to do this in SQL?



Thanks!

View 2 Replies View Related

A Question About Clustered Indexes Forcing Rebuild Of Non-clustered Indexes.

Sep 18, 2007

So I'm reading http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx and I come across this:
When selecting a column to base your clustered index on, try to avoid columns that are frequently updated. Every time that a column used for a clustered index is modified, all of the non-clustered indexes must also be updated, creating additional overhead. [6.5, 7.0, 2000, 2005] Updated 3-5-2004
Does this mean if I have say a table called Item with a clustered index on a column in it called itemaddeddate, and several non-clustered indexes associated with that table, that if a record gets modified and it's itemaddeddate value changes, that ALL my indexes on that table will get rebuilt? Or is it referring to the table structure changing?
If so does this "pseudocode" example also cause this to occur:
sqlstring="select * from item where itemid=12345"
rs.open sqlstring, etc, etc, etc
rs.Fields("ItemName")="My New Item Name"
rs.Fields("ItemPrice")=1.00
rs.Update
Note I didn't explicitly change the value of rs.fields("ItemAddedDate")...does rs.Fields("ItemAddedDate")=rs.Fields("ItemAddedDate") occur implicitly, which would force the rebuild of all the non-clustered indexes?

View 4 Replies View Related

Trying To Get The Value From A Select Prior To An Insert

Dec 29, 2003

I'm trying to find out if a certain row exists in my table, if it does exist then I do nothing if it doesn't exist I will insert a value. Only problem is i'm quite new and not sure how to get the values from the insert to correctly check if they already exist. Heres my attempt using query analyser

DECLARE @FK_UserID int, @NoteID int, @BeenRead NVARCHAR(10), @TMPuserID INT, @TMPnoteID INT

SET @FK_UserID = 1
SET @NoteID = 254
SET @BeenRead = 'MyTestHere'



SELECT DISTINCT Backup_UserNotes.BackUp_UserID, Backup_UserNotes.BackUp_NoteID
FROM User_Notes INNER JOIN
Note ON User_Notes.FK_UN_NoteID = Note.NoteID INNER JOIN
Backup_UserNotes ON Note.NoteID = Backup_UserNotes.BackUp_NoteID

WHERE BackUp_UserID = @FK_UserID AND BackUp_NoteID = @NoteID


SELECT @NoteID = @@IDENTITY
SELECT @FK_UserID = @@IDENTITY

IF @FK_UserID = NULL AND @NoteID = NULL

BEGIN
INSERT BackUp_UserNotes (
BackUp_NoteID,
BackUp_UserID,
BackUp_Read)

SELECT @NoteID, @FK_UserID, @BeenRead


END

-- RETURN 0
------------------------------------------------
GO

Anybody know the best solution? Thanks

View 1 Replies View Related

Select Max Value From Prior Where Conditions

Apr 7, 2006

I can't figure this out for the life of me. Wanted to know if it's possible to select certain date conditions in a query, then later reference those conditions and to only select the max of them.

I need to do this dynamically as I do not know what the max value is. I've provided an example below:

Select var1
From table1
where
(
(Date1 = '11/30/2005')
OR
(Date1 = '12/31/2005')
)
and Date1 = (Max of previously selected values e.g. '12/31/2005')

What I can't figure out is how to dynamically retrieve the max of 11/31/2005 and 12/31/2005. Any ideas are greatly appreciated.

View 1 Replies View Related

Using Date From Prior Record

Apr 8, 2004

Hello gurus, I have a table of data containing stop and start times of equipment, such as this:

StartTime....................EndTime
12/01/01 15:44...........12/01/01 18:44
12/02/01 3:44............12/02/01 14:44
12/02/01 15:22...........12/02/01 15:33

etc.

With this, I can build a query that subtracts the start and end dates to give me the total differences between each record... But, how do I write a query that can count the elapsed time between the current record's "start time" and the prior record's "end time" to give me the elapsed time between each record?

View 14 Replies View Related

Prior Omint Using Select

Feb 29, 2008

One more question.

I need to use select using some type of a prior omit.

for example, I have 2 selects:

select * from table abc where zip in ('07661') and name like 'A%'

go

select * from table abc where zip in ('07661') and company like 'ABC&'


there could be common records in both select statements, but I want to exclude the same records from the first select when I run my second one.

I could say

select * from table abc where zip in ('07661') and company like 'ABC&' and name not like 'A%'

in my second select, but I rather not do that, since my queries has many select criterias and its not just 2 selects.

So is there another option? a subsitution for "go"?

thanks

View 3 Replies View Related

Dates Prior To 1753-01-01

Jul 23, 2005

Hello,I have an Access DB with a column [date] holding dates from present day backto around year 1702, and I am trying to run DTS Import with this MDB, whichfails due to a conversion error.MSDN says the datetime data type only allows 1753-01-01 to 1900-01-01, thusI am under the impression having the [date] field as varchar is the only wayto store the dates.My questions are:a) Can dates prior to 1753-01-01 be stored in a date column?b) If dates are in a column of type varchar (or similar) is it possible tosort them chronologically with T-SQL ?c) 1753-01-01 seems particularly arbitrary, any reason for this?Many thanks for any help, it's driving me mad!John

View 6 Replies View Related

Killing Processes Prior To Restore

May 13, 2002

I have a scheduled job that will do a database restore at given time every day. Sometimes I run into a situation where some people leave themselves logged on to the database, which prevents the job from running.

Is there a way that I can set up my job to include killing any open processes against the database that I'm restoring prior to the restore being done?

View 3 Replies View Related

Dates In Years Prior To 1900?

Jan 13, 2000

I've set up a SQL7 database with MSAccess97 as a front end. I'm trying to enter a person with a birthdate prior to 1900, get an ODBC call error, "Datetime field overflow". How to enter dates prior to year 1900?
Thanks.

View 1 Replies View Related

Send Email Prior To A Datetime

Aug 11, 2003

This is quite a tricky one for me to figure out.

I have a datetime field in a databse for when a tender closes. More often then not its 4pm - for what its worth. And I want to send an email informing the manager 6hrs, 30mins prior and when the tender closes. So I need to somehow check that field either once a day and store those times somewhere or quite regularly ... like every hour.

I would be interested to hear how people would approach such a concept. I havn't got a great deal of experience in triggers or SQL mail.

It is in an asp file that I am doing it.

Any ideas would be hugely appreciated.

Cheers

tom

View 1 Replies View Related

Ask For Password Prior To Allowing Access

Nov 13, 2003

I am trying to make a connection to sql server using sspi (windows authentication), however, I do not wish to use the built in tokenauthentication system. Administrators are worried about somebody using an unattended logged on computer to gain access to the database. So I am left with using sql server authentication (which I am not fond of doing, more password administration) or what I would like to do is use windows authentication but make the user enter thier windows password prior to logging into sql server. Is there a way? Thanks in Advance.

Kent

View 2 Replies View Related

Need A Task That Will Zip A File Prior To Upload Via FTP

Mar 28, 2006

I have a text file I need to zip (compress) before uploading it via an FTP task. Do I have to use a third-party tool to do this? Thanks.

View 15 Replies View Related

SQL Server 2008 :: Logic To Rebuild Only Clustered Indexes / Skipping To Rebuild Non Clustered Indexes In Same Table

Jun 25, 2015

I have a requirement to only rebuild the Clustered Indexes in the table ignoring the non clustered indexes as those are taken care of by the Clustered indexes.

In order to do that, I have taken the records based on the fragmentation %.

But unable to come up with a logic to only consider rebuilding the clustered indexes in the table.

create table #fragmentation
(
FragIndexId BigInt Identity(1,1),
--IDENTITY(int, 1, 1) AS FragIndexId,
DBNAME nvarchar(4000),
TableName nvarchar(4000),

[Code] ....

View 5 Replies View Related

Indexes Vs Clustered Indexes

Sep 17, 2006

What is the difference please?

View 1 Replies View Related

T-SQL (SS2K8) :: First And Last Day Prior Month As Input Parameters

Apr 16, 2014

I have to create a report and I want all activity for the previous month.

I need to calculate the First and Last Day Prior Month to be used as Input Parameters.

Would something like this be the case or is there a better solution?

[code="sql"]
SELECT DATEADD(month, DATEDIFF(month, -1, getdate()) - 2, 0) as FirstDayPreviousMonthWithTimeStamp,
DATEADD(ss, -1, DATEADD(month, DATEDIFF(month, 0, getdate()), 0)) as LastDayPreviousMonthWithTimeStamp
[/code]

I was thinking get the first day of the previous and current month to exclude the Timestamp and use a less then first day of current month?

View 3 Replies View Related

SQL 2012 :: Checking Existence Of Row Prior To Insert?

Aug 11, 2015

I have a situation where our stored procedure inserts records from table_1 to table_2 when they don't already exist (uses the EXIST statement) on that table. If table_1 contains multiple records that are the same, it appears after the 1st record has been inserted, it does not recognize it as being there when it checks the existence when attempting to insert record 2.

Here's an example of the script:

insert into table_2 (col1,col2,col3)
select col1,col2,col3
from table_1 t1
where not exists (select '1'
from table_2 t2
where t1.col1 = t2.col1
and t1.col2 = t2.col2
and t1.col3 = t2.col3)

Data from Table_1 -- Assume that table_2 does not contain these records

col1 col2 col3
AA 11 A1
AA 11 A1
BB 22 B2

All 3 records would be inserted to table_2 in this example.

View 7 Replies View Related

Transact SQL :: Get Rows Not Matching Prior Year

Apr 25, 2015

We were asked to fix a query to get rows from a prior year history table that did not match to rows in the current year to show a variance from one year to the next. Rows must match on [corpnbr],[plincd],[pgrpcd] and [pitmcd].  If the combination has rows in the current and prior year ([hstyr]) then everything is fine. However, if they have rows in the prior year (e.g. [hstyr]='2014') but not in the current year (e.g. [hstyr]='2015') then they do not show in the result.  Below is how they designed the table and below that is the stored procedure to pull the records. 

CREATE TABLE [dbo].[BillingHistory](
[BillingHistoryID] [int] IDENTITY(1,1) NOT NULL,
[entity] [varchar](4) NULL,
[plincd] [varchar](3) NULL,
[pgrpcd] [varchar](4) NULL,
[pitmcd] [varchar](4) NULL,
[newplincd] [varchar](2) NULL,

[Code] ....

View 13 Replies View Related

SQL Server - Oracle Differences - Connect By Prior

Dec 5, 2007

Hello ,

I'm facing a complicated problem and I don't think that the solution will be an easy one.

I have an SQL statement in Oracle which I need to translate it in Sql Server 2005.

select lpad(' ',5*(orderid)) || to_char(descr) as menui
from <table _name>
where MENU_ITEM not in ('test1','test2','test3') and item_parent not in ('test4,'test5,'test6')
start with <item_parent='item_parent' >
connect by prior <menu_item = item_parent and menu_name='ADR_m_adr_frame'; > ?(condition)

Somewhere I have read that SQL server does not support Hierarchical Sql statements. Is this true ? How am I going to do that ?

Any help will be appreciated.

Thank you

View 7 Replies View Related

Synchronizing Data Prior To Initial Snapshot

Aug 23, 2007



Hello folks,

I am trying to set up replication between a central office and multiple sites. This will be merge repl, since either central or satellites can be modified. However, one issue I ran into is setting up subscribers. I don't always have the option of starting with a publisher's snapshot; some of the subscribers have their own data already, which may or may not overlap with central site, and which needs to be synchronized into the central site somehow.

So far, I haven't seen any solution for this kind of scenario, at least nothing out of the box. Am I doomed to having to roll out custom data transfers (I don't think I can use DTS since all sites are running Express), or is there some standard way of doing this?

Thanks so much!

Sonya

View 6 Replies View Related

Transact SQL :: DATEADD - Determine Prior Month

Jul 8, 2015

My overall problem is to do some string manipulation and then DATEADD, but I still don't know why I getting what SQL is giving me.

To get the prior month, I use the following code;

SELECT
(
SELECT
RIGHT('00' + CAST(
CASE CAST(RIGHT(MAX(Eff_Period), 2) AS int) - 1
WHEN 0 THEN 12
ELSE CAST(RIGHT(MAX(Eff_Period), 2) AS int) - 1
END AS varchar(2)), 2)
)--, -- Month
--CAST(CAST(LEFT(MAX(Eff_Period), 4) AS int) - 1 AS varchar(4)) -- Year
FROM
Current_Membership;

This correctly gives me the proper month number for the prior month, in this case, '06' since the current month is '07.'
But, for debugging, I wanted to test to make sure it would properly adjust for January - '01' - by replacing MAX(Eff_Period) with '201501.'

It did. Worked fine. Then, to make really sure, I put in '201502.'

Instead of the single record of '01,' it shot out 177,209 rows of all '02's!

I put back in the MAX(Eff_Period) instead of the date string constant and, worked fine. Swapped back in the date string - boom! - 177,209 rows again.

Like I said, I can use DATEADD - Now I'm really confused. I changed the date string to '201501' just to verify before posting this, and it spit out 177,209 rows of '01's! So, to keep from altering things and to maintain the edit history, I started up another query and C&P'd everything to it. Then, on the original, I backed up to an earlier version, one that worked. That listing is the same as above, but the commented out comma in line 9 and the commented CAST in the line after it - to get the year - were uncommented.

Now, it works with both '201501' and '201502.' But, if I replace the comments, basically removing the CAST with the year, it slams back with 177,209 rows!

View 6 Replies View Related

Maintenance Back Up Plan Not Deleting Prior Backup

Apr 25, 2001

I have a maintenance plan set to back up my 2 primary databases, one about 1 GB, the other about 2GB. I have about 4 GB available on the back up drive. I have set it to delete old backups more than 1 minute old. Problem is, the first night it works fine, the second night it sometimes it refuses to delete the old and therefore does not have enough space to complete the new back-up. Does anyone run into this before? Does it complete both back-ups THEN erase the old, in which case I need to have about 6GB available? Sometimes it works and sometimes it doesn't. It can be going along fine for weeks then stop working. Any info or links to how this is supposed to work would help.

TIA

View 3 Replies View Related

The Timeout Period Elapsed Prior To Completion Of The Operation

Sep 10, 2006

Hi

I'm using two different server for application(.net version 1.1) and database(sqlserver200) with win2k3 environment. I'm getting the below error message all the times. I've veryfied the communication between the two server is fine.

"The timeout period elapsed prior to completion of the operation or the server is not responding"

Any help is greatly appreciated

Cheers

Veeru

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved