back indexes of a database

Question

back indexes of a database

ALI-408105

Say Hey Kid

Points: 709
More actions
February 13, 2007 at 11:52 am

#172250

can I write a sql script or stored procedure that backsup all the indexes on a give database so that can be re-applied if required?
I treid but I am not sure about how to find the columns involved in a give index and type pf index. I could just get index name from sysindexes table. But I cannot recreate the same index in some other database by just knowing its name right. please help he with sample script that would do so.
Backup of indexes in any format is OK preferrable table format.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 1

In 2005 Management Studio you can right click the database, Tasks -> Generate Scripts.... Follow the instructions and in the "what to script" section set everything to false except Indexes.

jezemine SSCrazy Eights Points: 8059 More actions · Answer 2

this free tool will script out all objects in any 2000 or 2005 database, using SMO. source is available so you can modify it as you please - it would be easy to change it so it only scripts a particular type of object, such as indexes.

it's very useful for getting all your objects under source control. also there's a .bat file that executes all the scripts to build a fresh db - I use it as a sort of autobuilder in conjunction with an app that monitors my source control. it's a way to catch build breaks early.

http://www.elsasoft.org/tools.htm

---------------------------------------
elsasoft.org

Lowell SSC Guru Points: 323482 More actions · Answer 3

i wrote this a while back:

it scritps out all PK, Uq and regular indexes. remove the top 100 to get all results, this can be a HUGE resultset, depending on yout schema:

Results:

CREATE UNIQUE CLUSTERED INDEX [SYSOBJECTS] ON [SYSOBJECTS] (ID)

CREATE UNIQUE INDEX [NCSYSOBJECTS] ON [SYSOBJECTS] (NAME,UID)

CREATE INDEX [NCSYSOBJECTS2] ON [SYSOBJECTS] (PARENT_OBJ)

SELECT TOP 100

REPLICATE(' ',4000) AS COLNAMES ,

OBJECT_NAME(I.ID) AS TABLENAME,

I.ID AS TABLEID,

I.INDID AS INDEXID,

I.NAME AS INDEXNAME,

I.STATUS,

INDEXPROPERTY (I.ID,I.NAME,'ISUNIQUE') AS ISUNIQUE,

INDEXPROPERTY (I.ID,I.NAME,'ISCLUSTERED') AS ISCLUSTERED,

INDEXPROPERTY (I.ID,I.NAME,'INDEXFILLFACTOR') AS INDEXFILLFACTOR

INTO #TMP

FROM SYSINDEXES I

WHERE I.INDID > 0

AND I.INDID < 255

AND (I.STATUS & 64)=0

--uncomment below to eliminate PK or UNIQUE indexes;

--what i call 'normal' indexes

--AND INDEXPROPERTY (I.ID,I.NAME,'ISUNIQUE') =0

--AND INDEXPROPERTY (I.ID,I.NAME,'ISCLUSTERED') =0

DECLARE

@ISQL VARCHAR(4000),

@TABLEID INT,

@INDEXID INT,

@MAXTABLELENGTH INT,

@MAXINDEXLENGTH INT

--USED FOR FORMATTING ONLY

SELECT @MAXTABLELENGTH=MAX(LEN(TABLENAME)) FROM #TMP

SELECT @MAXINDEXLENGTH=MAX(LEN(INDEXNAME)) FROM #TMP

DECLARE C1 CURSOR FOR

SELECT TABLEID,INDEXID FROM #TMP

OPEN C1

FETCH NEXT FROM C1 INTO @TABLEID,@INDEXID

WHILE @@FETCH_STATUS <> -1

BEGIN

SET @ISQL = ''

SELECT @ISQL=@ISQL + ISNULL(SYSCOLUMNS.NAME,'') + ',' FROM SYSINDEXES I

INNER JOIN SYSINDEXKEYS ON I.ID=SYSINDEXKEYS.ID AND I.INDID=SYSINDEXKEYS.INDID

INNER JOIN SYSCOLUMNS ON SYSINDEXKEYS.ID=SYSCOLUMNS.ID AND SYSINDEXKEYS.COLID=SYSCOLUMNS.COLID

WHERE I.INDID > 0

AND I.INDID < 255

AND (I.STATUS & 64)=0

AND I.ID=@TABLEID AND I.INDID=@INDEXID

ORDER BY SYSCOLUMNS.COLID

UPDATE #TMP SET COLNAMES=@ISQL WHERE TABLEID=@TABLEID AND INDEXID=@INDEXID

FETCH NEXT FROM C1 INTO @TABLEID,@INDEXID

END

CLOSE C1

DEALLOCATE C1

--AT THIS POINT, THE 'COLNAMES' COLUMN HAS A TRAILING COMMA

UPDATE #TMP SET COLNAMES=LEFT(COLNAMES,LEN(COLNAMES) -1)

SELECT 'CREATE '

+ CASE WHEN ISUNIQUE = 1 THEN ' UNIQUE ' ELSE ' ' END

+ CASE WHEN ISCLUSTERED = 1 THEN ' CLUSTERED ' ELSE ' ' END

+ ' INDEX [' + UPPER(INDEXNAME) + ']'

+ SPACE(@MAXINDEXLENGTH - LEN(INDEXNAME))

+' ON [' + UPPER(TABLENAME) + '] '

+ SPACE(@MAXTABLELENGTH - LEN(TABLENAME))

+ '(' + UPPER(COLNAMES) + ')'

+ CASE WHEN INDEXFILLFACTOR = 0 THEN '' ELSE ' WITH FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) END --AS SQL

FROM #TMP

--SELECT * FROM #TMP

DROP TABLE #TMP

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jezemine SSCrazy Eights Points: 8059 More actions · Answer 4

This is an ok script for some purposes perhaps, but it's missing a lot. There are many options in CREATE INDEX that you are not covering, as you can see here: http://msdn2.microsoft.com/en-us/library/ms188783.aspx.

That's why I think it's safer to use SMO to script them - hundreds of hours of work went into getting the scripting capabilities of SMO correct - why not leverage them?

---------------------------------------
elsasoft.org