Scripting off indexes.
-
Looking for T-SQL that scripts off all indexes for a table in a SQL Server 2000 database (Windows 2000). Scenario: Need T-SQL to drop all indexes on a table and rebuild them after a BCP. Therefore, I am looking to see if someone has already coded this.
-
You can check the script library, but offhand, a quick cursor through sysindexes should get you the DROPs and creates. Or use the script generation facility in EM to script the table and it's indexes, that's probably quicker.
-
Thanks for the information but I am looking for something deeper. I failed to mention that I want this to work in a batch mode. Therefore, EM will not work. Yes, I did consider writing my own but I thought if someone has already created a similar script, I could leaverage off of it. I am trying to make my script smart enough so that if another DBA adds or removes indexes, my script will cover dropping all indexes and rebuilding them without having to worry about what someone did during the day.
I found a stored procedure another person created called sp_NCHelpIndex which provides index information for tables. I thought about using the information from this stored procedure to build my DROP INDEX and CREATE INDEX statements.
-
DarylAndDaryl,
Sent you a private message regarding scripts.
Steve
-
This should do what you want:
http://www.sqlservercentral.com/scripts/contributions/1035.asp
DO NOT ACCIDENTALLY RUN THIS SCRIPT IN A PRODUCTION DATABASE unless you know what you are doing. It will drop all your FK relationships.
Run it in Text Mode in Query analyzer.
The output is in 2 forms:
(1) The actual results in QA which is itself a script that will let you reconstruct all the FKs.
(2) a table, one column of which also contains the SQL you can use to reconstruct your FKs.
Don't be afraid of it. It really is good and works well. Just try it out a few times in a test database first an become familiar with how it works.
G. Milner
-
IMO it's tricky to drop all indexes just to do a BCP.
This will hurt you system _and_ it will hurt again when you recreate these indexes !
And in the mean time, your applications (and other connections ? ) will have the opportunity to put in some messy data because of the lack of constraints that rely on indexes (pk - unique).
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I'm with you here. Isn't this one of the reasons why the idea of a staging environment was conceived?
BCP into staging tables that are sans indexes. Then insert/update/delete production tables as necessary.
--------------------
Colt 45 - the original point and click interface
-
Thanks for all the info.
I need to add that this data is being BCPed to staging tables. These are the tables I want to drop the indexes on and recreate them after the BCP completes. From experience with reloading the data, we gained significate reload time by dropping the indexes and rebuilding them afterwards.
-
maybe sp_helpindex 'yourobject' as a base for your generate script may be quiet usable combined with the undocumend
sp_meforeachtb.Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Try this
/****** Object: Stored Procedure dbo.index_all_tables Script Date: 5/19/00 12:24:02 PM ******/
CREATE PROCEDURE index_all_tables
AS
/*
This procedure will generate a short report of the sp_helpindex
output for all tables within this database.
*/
DECLARE @tablename varchar(30)
DECLARE @tablename_header varchar(75)
DECLARE tnames_cursor CURSOR FOR SELECT name
FROM sysobjects
WHERE type = 'U' ORDER BY name
OPEN tnames_cursor
FETCH NEXT FROM tnames_cursor INTO @tablename
WHILE (@@FETCH_STATUS <> -1)
BEGIN
/*
Because @@FETCH_STATUS will return one of three values, -2,
-1, or 0, all three cases must be tested. In this case, if
a table has been dropped since the time this stored
procedure was executed, it will be skipped. A
successful fetch (0) will cause the sp_helpindex within the
BEGIN..END loop to execute.
*/
IF (@@FETCH_STATUS <> -2)
BEGIN
PRINT " "
SELECT @tablename_header = "************** " +
RTRIM(@tablename) + " *************"
PRINT @tablename_header
PRINT " "
EXEC sp_helpindex @tablename
END
FETCH NEXT FROM tnames_cursor INTO @tablename
END
PRINT " "
PRINT " "
SELECT @tablename_header = "************* NO MORE TABLES"
+ " *************"
PRINT @tablename_header
PRINT " "
PRINT "sp_helpindex has been run against all user-defined tables."
DEALLOCATE tnames_cursor
GO
-
Here's some thing that should work for you.. I've not tested this extensively for all types of constraints, but it works with basic Primary Keys, Foreign Keys and Standard Indexes. I think it might be better if all the Foreign keys were dropped separately versus because of their Primary key dependancy. You should obviously script all your indexes and foreign keys first so you can put them back.
CREATE PROCEDURE usp_DropAllIndexesForaTable
@TableName varchar(255)
AS
DECLARE @IndexName varchar(500)
DECLARE @strSQL varchar(8000)
DECLARE @fkeyName varchar(500)
DECLARE @fKeyTableName varchar(255)
CREATE TABLE #tmpIndexes
(Index_Name sysname
,index_Description varchar(500)
,index_keys varchar(1000))
CREATE TABLE #tmpPrimaryKey
(Table_Qualifier varchar(255)
,Table_Owner varchar(255)
,Table_Name varchar(255)
,Column_Name varchar(255)
,Key_Seq int
,pk_Name varchar(500))
CREATE TABLE #tmpFKeys
(PKTable_Qualifier varchar(255)
,pktable_owner varchar(255)
,pkTable_Name varchar(255)
,pkColumn_Name varchar(255)
,fkTable_Qualifier varchar(255)
,fkTable_owner varchar(255)
,fkTable_Name varchar(255)
,fkColumn_Name varchar(255)
,Key_Seq int
,Update_Rule int
,Delete_Rule int
,fk_Name varchar(500)
,PK_Name varchar(500)
,Deferrability int
)
--// Get the Primary Keys, Foreign Keys and Indexes.
EXEC ('INSERT INTO #tmpPrimaryKey EXEC sp_pKeys ' + @tableName)
EXEC ('INSERT INTO #tmpFKeys EXEC sp_fKeys ' + @tableName)
EXEC ('Insert into #tmpIndexes exec sp_helpIndex ' + @TableName)
--//Loop through all the declare indexes
DECLARE idx CURSOR FOR
SELECT Index_Name from #tmpIndexes
OPEN idx
FETCH NEXT FROM idx into @IndexName
WHILE (@@Fetch_Status <> -1)
BEGIN
SELECT @strSQL = ''
IF EXISTS (select 1 from #tmpPrimaryKey where pk_Name = @IndexName)
BEGIN
--// Before you can drop a primary key, you must drop all the Foreign Keys that are constraining it
--// Drop the foreign Keys that use the primary Key if any
DECLARE fkey CURSOR FOR
SELECT DISTINCT fkTable_Name,fk_Name
FROM #tmpfKeys where pk_Name = @IndexName
OPEN fKey
FETCH NEXT FROM fkey into @fKeyTableName,@fkeyName
WHILE (@@Fetch_Status <> -1)
BEGIN
EXEC ('Alter Table ' + @fKeyTableName + ' Drop Constraint ' + @fkeyName)
FETCH NEXT FROM fkey into @fKeyTableName,@fkeyName
END
close fkey
deallocate fkey
--// All the fKey constraints should be gone, so drop the Primary key
EXEC ('Alter Table ' + @TableName + ' Drop Constraint ' + @IndexName)
END ELSE BEGIN
--// Drop all the other indexes not bound by constraints
SELECT @strSQL = 'Drop Index ' + @TableName + '.' + @IndexName
EXEC (@strSQL)
END
FETCH NEXT FROM idx into @IndexName
END
Close idx
Deallocate idx
hth
Bill
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply