February 15, 2004 at 3:55 pm
Hi All,
Does anyone know of the existence of a script which I can use to script the drop and create of all indexes in a database. The script is to include the original fill factor of any clustered indexes.
Thanks,
Terry
February 16, 2004 at 7:18 am
Hi, Run generate script option by right clicking on database option in SQl Server Enterprise Manager, go to Option tab in screen and click the Index check box
February 16, 2004 at 2:44 pm
Hi,
Unfortunately this method includes a drop and create statement for all tables. Im pretty sure that no drop index command is even performed because the tables are dropped.
Thanks,
Terry
February 16, 2004 at 3:29 pm
You can eliminate the drop table statement by chosing the formatting tab and removing the top two check boxes.
For dropping the indexes you will again have to use the sql
Drop index table_name.index,table_name.index but they can go in one line
Hope it helps
Thanks
February 17, 2004 at 7:30 am
Here is what you may need - directly from sqlserver central web site ....
------------------------------------------------------------------------
-- Name : CreateDBREINDEX.sql
-- Author : Jeff Weisbecker October 1, 2003
-- RDBMS : SQL Server 2000
-- Desc : This script will generate a set of DBCC DBREINDEX commands
-- that can be executed to rebuild the indexes. Additional
-- information is provided to assist with sizing of the fill
-- factors.
--
-- To execute the DBREINDEX commands the line will have to
-- be uncommented (It is commented out to prevent accidental
-- execution). Also, the value for the fill factor is not
-- specified in the command. If you do not want to change
-- the fill factor remove the last comma in the DBREINDEX
-- statement. See Books Online for proper syntax.
--
-- @MinPages - You may not want to build a reindexing
-- script for small indexes. This value specifies the
-- minimum number the pages the index must contain. The
-- number of rows is not a good indicator of the size of the
-- index.
--
-- @MaxScanDensity - You may not want to build a reindexing
-- script for indexes with a high scan density. For example,
-- you may not want to rebuild indexes that have a scan
-- density of 90% or higher so you could set this variable
-- to 90.
--
-- Comments : I created this script to help assist with
-- fill factors. Heap tables are not included in the results,
-- but indexes on heaps will be. As always, use a script
-- with caution.
--
------------------------------------------------------------------------
SET NOCOUNT ON
--
DECLARE @SQLString VARCHAR(2000) -- String used to hold SQL Statements to be executed.
DECLARE @ObjectID INT -- Not used, but may be useful for enhancements
DECLARE @TableName VARCHAR(120) -- Table name
DECLARE @TableSchema VARCHAR(40) -- Owner of the table
DECLARE @MinPages INT -- The minimum number of pages needed reindex
DECLARE @MaxScanDensity INT -- The maximum scan density that will be reindexed
--
SET @MinPages = 0 -- Modify to reduce results
SET @MaxScanDensity = 100 -- Modify to reduce results
--
CREATE TABLE #TempForShowContig(ObjectName VARCHAR (60),
ObjectId INT,
IndexName VARCHAR (60),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
--
CREATE TABLE #TempForTableName (TableName VARCHAR(120),
TableSchema VARCHAR(40),
ObjectID INT)
--
DECLARE c_table CURSOR FOR
SELECT TableName, TableSchema, ObjectID
FROM #TempForTableName
SET @SQLString = 'SELECT o.name , USER_NAME(o.uid), o.id ' +
'FROM sysobjects o, ' +
' sysindexes i ' +
'WHERE o.type = ''U''' +
' AND o.id = i.id ' +
' AND i.indid IN (0,1) ' +
' AND o.name != ''dtproperties'''
INSERT #TempForTableName
EXECUTE(@SQLString)
------------------------------------------------------------------------
OPEN c_table
FETCH NEXT FROM c_table INTO @TableName, @TableSchema, @ObjectID
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT #TempForShowContig
EXEC ('DBCC SHOWCONTIG (''[' + @TableSchema +'].['+ @TableName + ']'') WITH ALL_INDEXES, TABLERESULTS, NO_INFOMSGS')
FETCH NEXT FROM c_table INTO @TableName, @TableSchema, @ObjectID
END
CLOSE c_table
--
-------------------------------------------------------------------------
DEALLOCATE c_table
-------------------------------------------------------------------------
-- The following SELECT can be uncommented to display the SHOWCONTIG
-- results for all of the tables and indexes.
-------------------------------------------------------------------------
-- SELECT '-- ', * FROM #TempForShowContig
--
PRINT '---------------------------------'
PRINT '-- Date: ' + CAST(GETDATE() AS CHAR(20))
PRINT '-- Database: ' + DB_NAME()
PRINT '---------------------------------'
PRINT 'USE ' + DB_NAME()
PRINT 'GO'
--
SELECT '---------------------------------' + CHAR(10) +
'---------------------------------' + CHAR(10) +
'-- Table : ' + RTRIM(t.ObjectName) + CHAR(10) +
'-- Owner : ' + RTRIM(USER_NAME(o.uid)) + CHAR(10) +
'-- Index : ' + RTRIM(t.IndexName) + CHAR(10) +
'-- ScanDensity : ' + CAST(t.ScanDensity AS CHAR(3)) + CHAR(10) +
'-- FillFactor : ' + CAST(i.OrigFillFactor AS CHAR(3)) + CHAR(10) +
'-- AvgPageDensity : ' + CAST(t.AvgPageDensity AS CHAR(5)) + CHAR(10) +
'-- AvgRecordSize : ' + CAST(t.AvgRecSize AS CHAR(10)) + CHAR(10) +
'-- Row Count : ' + CAST(t.CountRows AS VARCHAR(10)) + CHAR(10) +
'-- Page Count : ' + CAST(t.CountPages AS VARCHAR(10)) + CHAR(10) +
CAST('-- DBCC DBREINDEX(''' + RTRIM(USER_NAME(o.uid)) + '.' + RTRIM(t.ObjectName)
+ ''',''' + RTRIM(t.IndexName) + ''',)' AS VARCHAR(180))
FROM #TempForShowContig t,
sysindexes i,
sysobjects o
WHERE i.id = t.ObjectId
AND i.name = t.IndexName
AND i.id = o.id
AND t.ScanDensity < @MaxScanDensity
AND t.CountPages > @MinPages
------------
DROP TABLE #TempForShowContig
DROP TABLE #TempForTableName
--
SET NOCOUNT OFF
February 17, 2004 at 11:54 am
IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_script_PK'), 'IsProcedure') = 1
DROP PROCEDURE sp_script_PK
GO
CREATE PROCEDURE sp_script_PK
@tabname sysname = ' '
,@dropflag tinyint = 0
AS
SET NOCOUNT ON
SET IMPLICIT_TRANSACTIONS OFF
DECLARE @buffer varchar(255)
IF @tabname = ' ' RETURN
IF @dropflag IN (0,1)
BEGIN
SELECT 'ALTER TABLE dbo.' + ISNULL(so.name, '') +CHAR(13)+CHAR(10)+
' DROP CONSTRAINT ' + ISNULL(si.name, '') + CHAR(13)+CHAR(10)+'GO'+CHAR(13)+CHAR(10)
FROM sysindexes si (NOLOCK)
JOIN
sysobjects so (NOLOCK)
ON si.id = so.id
AND INDEX_COL(so.name, si.indid, 1) IS NOT NULL
AND so.id = OBJECT_ID(@tabname)
AND so.type = 'U'
WHERE
si.indid > 0
AND (si.status & 0x800 = 0x800 OR si.status & 0x1000 = 0x1000)
END
IF @dropflag = 1
BEGIN
IF @@TRANCOUNT > 0
ROLLBACK
RETURN
END
SELECT 'ALTER TABLE dbo.' + so.name + CHAR(13)+CHAR(10)+' '+
' ADD CONSTRAINT ' + si.name +
CASE
WHEN OBJECTPROPERTY(OBJECT_ID(si.name), 'IsUniqueCnst') = 1 THEN ' UNIQUE '
WHEN OBJECTPROPERTY(OBJECT_ID(si.name), 'IsPrimaryKey') = 1 THEN ' PRIMARY KEY '
END +
CASE indid WHEN 1 THEN 'CLUSTERED ' ELSE 'NONCLUSTERED ' END +
REPLACE('([' + SUBSTRING((SUBSTRING ((INDEX_COL(so.name,si.indid,1) + ']' +
', ' + ISNULL('[' + INDEX_COL(so.name,si.indid,2) + ']', '') +
', ' + ISNULL('[' + INDEX_COL(so.name,si.indid,3) + ']', '') +
', ' + ISNULL('[' + INDEX_COL(so.name,si.indid,4) + ']', '') +
', ' + ISNULL('[' + INDEX_COL(so.name,si.indid,5) + ']', '') +
', ' + ISNULL('[' + INDEX_COL(so.name,si.indid,6) + ']', '') +
', ' + ISNULL('[' + INDEX_COL(so.name,si.indid,7) + ']', '') +
', ' + ISNULL('[' + INDEX_COL(so.name,si.indid,8) + ']', '') +
', ' + ISNULL('[' + INDEX_COL(so.name,si.indid,9) + ']', '') +
', ' + ISNULL('[' + INDEX_COL(so.name,si.indid,10) + ']', '') +
', ' + ISNULL('[' + INDEX_COL(so.name,si.indid,11) + ']', '') +
', ' + ISNULL('[' + INDEX_COL(so.name,si.indid,12) + ']', '') +
', ' + ISNULL('[' + INDEX_COL(so.name,si.indid,13) + ']', '') +
', ' + ISNULL('[' + INDEX_COL(so.name,si.indid,14) + ']', '') +
', ' + ISNULL('[' + INDEX_COL(so.name,si.indid,15) + ']', '') +
', ' + ISNULL('[' + INDEX_COL(so.name,si.indid,16) + ']', '')),1,
CHARINDEX(', ,',('[' + ISNULL(INDEX_COL(so.name,si.indid,1) + ']', '') +
', ' + ISNULL('[' + INDEX_COL(so.name,si.indid,2) + ']', '') +
', ' + ISNULL('[' + INDEX_COL(so.name,si.indid,3) + ']', '') +
', ' + ISNULL('[' + INDEX_COL(so.name,si.indid,4) + ']', '') +
', ' + ISNULL('[' + INDEX_COL(so.name,si.indid,5) + ']', '') +
', ' + ISNULL('[' + INDEX_COL(so.name,si.indid,6) + ']', '') +
', ' + ISNULL('[' + INDEX_COL(so.name,si.indid,7) + ']', '') +
', ' + ISNULL('[' + INDEX_COL(so.name,si.indid,8) + ']', '') +
', ' + ISNULL('[' + INDEX_COL(so.name,si.indid,9) + ']', '') +
', ' + ISNULL('[' + INDEX_COL(so.name,si.indid,10) + ']', '') +
', ' + ISNULL('[' + INDEX_COL(so.name,si.indid,11) + ']', '') +
', ' + ISNULL('[' + INDEX_COL(so.name,si.indid,12) + ']', '') +
', ' + ISNULL('[' + INDEX_COL(so.name,si.indid,13) + ']', '') +
', ' + ISNULL('[' + INDEX_COL(so.name,si.indid,14) + ']', '') +
', ' + ISNULL('[' + INDEX_COL(so.name,si.indid,15) + ']', '') +
', ' + ISNULL('[' + INDEX_COL(so.name,si.indid,16) + ']', ''))) - 1 )), 1 ,300) + ')', ',)', ')') +
CASE OrigFillFactor
WHEN 0 THEN ' '
ELSE ' WITH FILLFACTOR = ' + ISNULL(CONVERT(char(2),si.OrigFillFactor), '')
END + CHAR(13)+CHAR(10)+'GO'+CHAR(13)+CHAR(10)
FROM
sysindexes si (NOLOCK)
JOIN
sysobjects so (NOLOCK)
ON si.id = so.id
AND so.type = 'U'
AND INDEX_COL(so.name,si.indid,1) IS NOT NULL
AND so.id = OBJECT_ID(@tabname)
WHERE
si.indid > 0
AND (OBJECTPROPERTY(OBJECT_ID(si.name), 'IsUniqueCnst') = 1 OR OBJECTPROPERTY(OBJECT_ID(si.name), 'IsPrimaryKey') = 1)
GO
USE master
GO
IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_script_index'), 'IsProcedure') = 1
DROP PROCEDURE sp_script_index
GO
/*
Title: sp_script_index
Author: Jonathan Ausubel
Date: 6/24/97
Description: Generates a script to re-create table indexes.
*/
CREATE PROCEDURE sp_script_index
@objname varchar(92) = NULL
,@option tinyint = 0
AS
SET ANSI_DEFAULTS ON
SET NOCOUNT ON
SET IMPLICIT_TRANSACTIONS OFF
DECLARE @i int
DECLARE @thiskey varchar(40)
DECLARE @lastindid int
DECLARE @objid int
,@indid int
,@keys varchar(200)
,@cr char(2)
DECLARE @tables TABLE
(
[tablename] sysname
)
DECLARE @indkeys TABLE
(
[indid] int NULL
,[keys] varchar(500) NULL
)
DECLARE @indx TABLE
(
[cmd] varchar(255)
)
IF @objname IS NULL
INSERT @tables ([tablename])
SELECT [name]
FROM sysobjects
WHERE [xtype] IN ('U', 'V')
ELSE
BEGIN
SET @objid = OBJECT_ID(@objname)
IF @objid IS NULL
RETURN
INSERT @tables ([tablename])
VALUES (@objname)
END
SET @cr = CHAR(13) + CHAR(10)
WHILE (1 = 1)
BEGIN
SET @objname = NULL
DELETE @indkeys
DELETE @indx
SELECT TOP 1 @objname = [tablename]
FROM @tables
IF @objname IS NULL
RETURN
DELETE @tables
WHERE [tablename] = @objname
SET @objid = OBJECT_ID(@objname)
SELECT
@indid = MIN([indid])
FROM
sysindexes (NOLOCK)
WHERE
[id] = @objid
AND [indid] > 0
AND [indid] < 255
IF @indid IS NULL
CONTINUE
WHILE @indid IS NOT NULL
BEGIN
SET @i = 1
WHILE @i <= 16
BEGIN
SET @thiskey = '[' + INDEX_COL(@objname, @indid, @i) + ']'
IF @thiskey IS NULL
BREAK
IF @i = 1
SET @keys = ISNULL('[' + INDEX_COL(@objname, @indid, @i) + ']', '')
ELSE
SET @keys = ISNULL(@keys, '') + ', ' + '[' + ISNULL(INDEX_COL(@objname, @indid, @i) + ']', '')
SET @i = @i + 1
END
INSERT @indkeys
SELECT @indid
,@keys
SET @lastindid = @indid
SET @indid = NULL
SELECT
@indid = MIN([indid])
FROM
sysindexes (NOLOCK)
WHERE
[id] = @objid
AND [indid] > @lastindid
AND [indid] < 255
END
IF @option IN (0, 1)
INSERT @indx
SELECT
'DROP INDEX ' +
OBJECT_NAME(OBJECT_ID(@objname)) + '.' + ISNULL([name], '') + @cr + 'GO' + @cr
FROM
sysindexes (NOLOCK)
WHERE
[id] = @objid
AND [indid] > 0
AND [indid] < 255
AND [status] & 0x800 <> 0x800
AND [status] & 0x1000 <> 0x1000
AND [status] & 0x20 <> 0x20
IF @option = 1
BEGIN
SELECT [cmd] AS ' '
FROM @indx
CONTINUE
END
INSERT @indx
SELECT ''
INSERT @indx
SELECT 'CREATE' +
CASE WHEN a.status & 0x2 = 0x2
THEN ' UNIQUE'
ELSE RTRIM('')
END +
CASE WHEN a.status & 0x10 = 0x10
THEN ' CLUSTERED'
ELSE RTRIM('')
END +
' INDEX ' + a.name + ' ON dbo.'+ OBJECT_NAME(a.id) +' (' + ISNULL(b.keys, '') + ')' +
ISNULL(CASE WHEN a.[OrigFillFactor] > 0 OR EXISTS (
SELECT '1'
FROM master.dbo.spt_values c (NOLOCK)
WHERE c.[type] = 'I'
AND c.[number] IN (1, 4, 64)
AND c.[number] & a.[status] = c.[number]
)
THEN @cr + ' WITH ' +
CASE WHEN a.[OrigFillFactor] > 0
THEN 'FILLFACTOR = ' + ISNULL(CONVERT(varchar(10), [OrigFillFactor]), '')
ELSE (
SELECT CASE d.[name]
WHEN 'ignore duplicate keys' THEN 'IGNORE_DUP_KEY'
WHEN 'ignore duplicate rows' THEN 'IGNORE_DUP_ROW'
WHEN 'allow duplicate rows' THEN 'ALLOW_DUP_ROW'
ELSE ''
END
FROM master.dbo.spt_values d (NOLOCK)
WHERE d.[type] = 'I'
AND d.[number] <> -1
AND d.[number] IN (1, 4, 64)
AND d.[number] & a.[status] = d.[number])
END
END, '') + @cr + 'GO' + @cr
FROM
sysindexes a (NOLOCK)
JOIN
@indkeys b
ON b.[indid] = a.[indid]
WHERE a.[id] = @objid
AND a.[indid] > 0
AND a.[indid] < 255
AND a.[status] & 0x800 <> 0x800
AND a.[status] & 0x1000 <> 0x1000
AND a.[status] & 0x20 <> 0x20
EXEC sp_script_PK @tabname = @objname
,@dropflag = @option
SELECT [cmd] AS ' '
FROM @indx
END
GO
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply