Re: cannot script ONLY indexes

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Hari (hari_prasad_k_at_hotmail.com)
Date: 03/18/04 

Date: Thu, 18 Mar 2004 09:07:49 +0530

Hi ,

I have Wrote a vry good utility to script the Indexes (Drop and Create). Use
this script and put in your suggestions, so that I can enhance the script
before publishing.

This will consider the following,

1. CLustered Index
2. Primary key clusterd index
3. Primary key non clustered index
4. Unique CLsutered
5. Unique non clustered
6. Clusterd Index

Attached the script , I will be publishing this in MSDB India site shortly
with a document.

CREATE PROC INDX_SCRIPING(
@iTableName SYSNAME = NULL
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @tableId INT
DECLARE @indexId INT
DECLARE @indexName SYSNAME
DECLARE @tableName SYSNAME
DECLARE @colId INT
DECLARE @keyNo INT
DECLARE INDEX_CURSOR CURSOR FOR
SELECT I.ID,I.INDID,I.NAME, OBJECT_NAME(O.ID)
FROM SYSINDEXES I
INNER JOIN SYSOBJECTS O
ON I.ID = O.ID
WHERE O.XTYPE = 'U'
AND ((@iTableName IS NULL) OR (O.ID = OBJECT_ID(@iTableName)))

CREATE TABLE #indexInfo
            (tableId INT,
            indexId INT,
            indexName SYSNAME,
            tableName SYSNAME,
            keys VARCHAR(7000),
            isClustered BIT,
            isPrimaryKey BIT,
            IsUnique BIT,
            IsUniqueCnst BIT)

OPEN INDEX_CURSOR

FETCH NEXT FROM INDEX_CURSOR
INTO @tableId, @indexId, @indexName, @tableName

WHILE @@FETCH_STATUS = 0
BEGIN
            INSERT INTO #indexInfo Values
            (@tableId,
            @indexId,
            @indexName,
            @tableName,
            '',
            CASE @indexId
                        WHEN 1 then 1
                        ELSE 0
            END,
ISNULL(OBJECTPROPERTY(object_id(@indexName),'IsPrimaryKey'),0),
ISNULL(INDEXPROPERTY(OBJECT_ID(@tableName), @indexName,'IsUnique'),0),
ISNULL(OBJECTPROPERTY(object_id(@indexName),'IsUniqueCnst'),0))

--PRINT 'Table ' + @tableName
DECLARE indexKey_Cursor CURSOR FOR
SELECT colid,KeyNo
FROM SYSINDEXKEYS
WHERE ID = @tableId
AND INDID = @indexId

OPEN indexKey_Cursor
FETCH NEXT FROM indexKey_Cursor
INTO @colId,@keyNO

WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE #indexInfo SET
keys = keys + COL_NAME(@tableid,@colId) + ','
WHERE tableId = @tableId
AND indexId = @indexId

--PRINT 'COL ' + COL_NAME(@tableid,@colId)

FETCH NEXT FROM indexKey_Cursor
INTO @colId,@keyNO
END
CLOSE indexKey_Cursor
DEALLOCATE indexKey_Cursor
FETCH NEXT FROM INDEX_CURSOR
INTO @tableId, @indexId, @indexName, @tableName
END

DELETE #indexInfo WHERE keys = ''

UPDATE #indexInfo SET keys = LEFT(keys,LEN(Keys)-1)

declare @index table(sql varchar(8000))

--Drop clustered primaty key index
INSERT INTO @INDEX SELECT 'ALTER TABLE '+ tablename + ' DROP CONSTRAINT ' +
indexName +
CHAR(13)
+ 'GO'
FROM #indexInfo
WHERE isClustered = 1 and isPrimarykey = 1
and IsUniqueCnst = 0 and IsUnique = 1

-- Drop unique Constraint
INSERT INTO @INDEX SELECT 'ALTER TABLE '+ tablename + ' DROP CONSTRAINT ' +
indexName +
CHAR(13)
+ 'GO'
FROM #indexInfo
WHERE isClustered = 0 and isPrimarykey = 0
and IsUniqueCnst = 1 and IsUnique = 1

-- Drop unique Constraint
INSERT INTO @INDEX SELECT 'ALTER TABLE '+ tablename + ' DROP CONSTRAINT ' +
indexName +
CHAR(13)
+ 'GO'
FROM #indexInfo
WHERE isClustered = 1 and isPrimarykey = 0
and IsUniqueCnst = 1 and IsUnique = 1

--Drop clustered index
INSERT INTO @INDEX SELECT 'DROP INDEX' + tablename+'.'+indexName + CHAR(13)
+ 'GO'
FROM #indexInfo
WHERE isClustered = 1 and isPrimarykey = 0
and IsUniqueCnst = 0 and IsUnique = 0

--Drop non clustered index
INSERT INTO @INDEX SELECT 'DROP INDEX ' + tablename+'.' + indexName +
CHAR(13) + 'GO'
FROM #indexInfo WHERE isClustered = 0 and isPrimarykey = 0
and IsUniqueCnst = 0 and IsUnique = 0

--Drop unique Clustred Index
INSERT INTO @INDEX SELECT 'DROP INDEX ' + tablename+'.' + indexName +
CHAR(13) + 'GO'
FROM #indexInfo WHERE isClustered = 1 and isPrimarykey = 0
and IsUniqueCnst = 0 and IsUnique = 1

--Drop unique nonClustred Index
INSERT INTO @INDEX SELECT 'DROP INDEX ' + tablename+'.' + indexName +
CHAR(13) + 'GO'
FROM #indexInfo WHERE isClustered = 0 and isPrimarykey = 0
and IsUniqueCnst = 0 and IsUnique = 1

--Drop non clustered primary key index
INSERT INTO @INDEX SELECT 'ALTER TABLE '+tablename + ' DROP CONSTRAINT ' +
indexName +
CHAR(13) + 'GO'
FROM #indexInfo
WHERE isClustered = 0 and isPrimarykey = 1
and IsUniqueCnst = 0 and IsUnique = 0

-- Create Index Statement

-- Create Clustered Primary key index
INSERT INTO @INDEX SELECT 'ALTER TABLE ' + tableName +
           ' ADD CONSTRAINT PK_' + tableName +
           ' PRIMARY KEY CLUSTERED (' + keys + ')' + CHAR(13) + 'GO'
FROM #indexInfo WHERE isClustered = 1 and isPrimarykey = 1
and IsUniqueCnst = 0 and IsUnique = 1

-- Create Unique nonClustered index
INSERT INTO @INDEX SELECT 'ALTER TABLE ' + tableName +
           ' ADD CONSTRAINT ' + indexName +
           ' UNIQUE NONCLUSTERED (' + keys + ')' + CHAR(13) + 'GO'
FROM #indexInfo WHERE isClustered = 0 and isPrimarykey = 0
and IsUniqueCnst = 1 and IsUnique = 1

-- Create Unique Clustered index
INSERT INTO @INDEX SELECT 'ALTER TABLE ' + tableName +
           ' ADD CONSTRAINT ' + indexName +
           ' UNIQUE CLUSTERED (' + keys + ')' + CHAR(13) + 'GO'
FROM #indexInfo WHERE isClustered = 1 and isPrimarykey = 0
and IsUniqueCnst = 1 and IsUnique = 1

-- Create NonClustered Primary key index
INSERT INTO @INDEX SELECT 'ALTER TABLE ' + tableName +
           ' ADD CONSTRAINT PK_' + tableName +
           ' PRIMARY KEY NONCLUSTERED (' + keys + ')' + CHAR(13) + 'GO'
FROM #indexInfo WHERE isClustered = 0 and isPrimarykey = 1
and IsUniqueCnst = 0 and IsUnique = 0

--Create NonClustered Index
INSERT INTO @INDEX SELECT 'CREATE NONCLUSTERED INDEX '
+ indexName + ' ON '
+ tableName + '('
+ keys + ')' + CHAR(13) + 'GO'
FROM #indexInfo
WHERE isClustered = 0 and isPrimarykey = 0
and IsUniqueCnst = 0 and IsUnique = 0

--Create Clustered index
INSERT INTO @INDEX SELECT 'CREATE CLUSTERED INDEX '
+ indexName + ' ON '
+ tableName + '('
+ keys + ')' + CHAR(13) + 'GO'
FROM #indexInfo
WHERE isClustered = 1 and isPrimarykey = 0
and IsUniqueCnst = 0 and IsUnique = 0

-- Create Unique Clustered Index
INSERT INTO @INDEX SELECT 'CREATE UNIQUE CLUSTERED INDEX '
+ indexName + ' ON '
+ tableName + '('
+ keys + ')' + CHAR(13) + 'GO'
FROM #indexInfo
WHERE isClustered = 1 and isPrimarykey = 0
and IsUniqueCnst = 0 and IsUnique = 1

-- Create Unique nonClustered Index
INSERT INTO @INDEX SELECT 'CREATE UNIQUE NONCLUSTERED INDEX '
+ indexName + ' ON '
+ tableName + '('
+ keys + ')' + CHAR(13) + 'GO'
FROM #indexInfo
WHERE isClustered = 0 and isPrimarykey = 0
and IsUniqueCnst = 0 and IsUnique = 1
TRUNCATE TABLE #indexInfo
DROP TABLE #indexInfo
CLOSE INDEX_CURSOR
DEALLOCATE INDEX_CURSOR
SELECT * FROM @INDEX
END

How to execute

RECREATE_INDEX 'TEST_INDEX'

If you not giving the table name the script will generated for all user
tables.
Thanks
Hari
MCDBA

"Paul Eccleston" <paul.eccleston@get-rid-of-this-bell-centres.com> wrote in
message news:40587e3b_1@nnrp1.news.uk.psi.net...
> hi i am trying to use "Generate SQL Script" to script out ONLY the indexes
> and keys defaults and contraints, i do NOT want to generate anything else
as
> these already exist in the destination database that im going to run the
> script on. what options will i need to tick on the "general" tab in order
> for sql to generate the script correctly?, i have tried scripting all
> objects and then unticking the create and drop objects commands, but this
> then creates an empty script, i have also tried leaving the create and
drop
> objects commands ticked, but this does not seem to script any of the
> existing indexes, anybody have a clue as to how i get around this?.
>
> many thanks!,
> paul.
>
>

Relevant Pages

  • Re: cannot script ONLY indexes
    ... I have Wrote a vry good utility to script the Indexes. ... Primary key non clustered index ... DECLARE @tableId INT ... UPDATE #indexInfo SET ...
    (microsoft.public.sqlserver.server)
  • Re: cannot script ONLY indexes
    ... I have Wrote a vry good utility to script the Indexes. ... Primary key non clustered index ... DECLARE @tableId INT ... UPDATE #indexInfo SET ...
    (microsoft.public.sqlserver.tools)
  • Re: cannot script ONLY indexes
    ... I have Wrote a vry good utility to script the Indexes. ... Primary key non clustered index ... DECLARE @tableId INT ... UPDATE #indexInfo SET ...
    (microsoft.public.sqlserver.dts)
  • Re: cannot script ONLY indexes
    ... > I have Wrote a vry good utility to script the Indexes. ... DECLARE @tableId INT ... DECLARE @indexName SYSNAME ... CREATE TABLE #indexInfo ...
    (microsoft.public.sqlserver.server)
  • Bug
    ... I have a client that I wrote a script for them to change the clustered index ... servers and found that on one server it had the problem but on two others it ... default getdate(), ...
    (microsoft.public.sqlserver.server)