SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Updated SQL Server 2005: Script all Indexes


Updated SQL Server 2005: Script all Indexes

Author
Message
jeffryfranksjunk
jeffryfranksjunk
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 90
DECLARE cIX CURSOR FOR
SELECT OBJECT_NAME(SI.Object_ID),
SI.Object_ID,
SI.Name,
SI.Index_ID,
SI.fill_factor,
SI.is_padded,
SI.allow_row_locks,
SI.allow_page_locks,
DataSpaceName = ds.name,
(select TABLE_SCHEMA from INFORMATION_SCHEMA.TABLES where TABLE_NAME = OBJECT_NAME(SI.Object_ID))
FROM sys.indexes SI
inner join sys.data_spaces ds on ds.data_space_id = si.data_space_id
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON SI.Name = TC.CONSTRAINT_NAME AND OBJECT_NAME(SI.Object_ID) = TC.TABLE_NAME
WHERE TC.CONSTRAINT_NAME IS NULL
AND OBJECTPROPERTY(SI.Object_ID, 'IsUserTable') = 1
--AND OBJECT_NAME(SI.Object_ID) like 'tableName%'
AND SI.type_desc <> 'HEAP'
ORDER BY OBJECT_NAME(SI.Object_ID), CASE SI.type_desc WHEN 'CLUSTERED' THEN 1 WHEN 'NONCLUSTERED' THEN 2 ELSE 3 END, SI.Index_ID

DECLARE @IxTable sysname
DECLARE @IxTableID INT
DECLARE @IxName sysname
DECLARE @i_schema sysname
DECLARE @IxID INT
DECLARE @fill_factor int
DECLARE @is_padded bit
DECLARE @allow_row_locks bit
DECLARE @allow_page_locks bit
DECLARE @DataSpaceName varchar(255)

DECLARE @CRLF NVARCHAR(2)
set @CRLF = CHAR(10) --+ CHAR(13)

-- Loop through all indexes
OPEN cIX
FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID, @fill_factor, @is_padded, @allow_row_locks, @allow_page_locks, @DataSpaceName, @i_schema
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @IXSQL NVARCHAR(4000)
SET @IXSQL = 'CREATE '

-- Check if the index is unique
IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsUnique') = 1)
SET @IXSQL = @IXSQL + 'UNIQUE '
-- Check if the index is clustered
IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1)
SET @IXSQL = @IXSQL + 'CLUSTERED '


SET @IXSQL = @IXSQL + 'INDEX [' + @IxName + '] ON [' + @i_schema +'].['+ @IxTable + ']('


-- Get all columns of the index
DECLARE cIxColumn CURSOR FOR
SELECT SC.Name, IC.is_included_column, IC.is_descending_key
FROM sys.index_columns IC JOIN sys.columns SC ON IC.Object_ID = SC.Object_ID AND IC.Column_ID = SC.Column_ID
WHERE IC.Object_ID = @IxTableID AND Index_ID = @IxID
ORDER BY IC.Key_Ordinal

DECLARE @IxColumn sysname
DECLARE @IxFirstColumn BIT SET @IxFirstColumn = 1
declare @i_include bit
declare @i_desc bit
declare @i_sql_col varchar(4000) set @i_sql_col = ''
declare @i_sql_inc varchar(4000) set @i_sql_inc = ''

-- Loop throug all columns of the index and append them to the CREATE statement
OPEN cIxColumn
FETCH NEXT FROM cIxColumn INTO @IxColumn,@i_include,@i_desc
WHILE (@@FETCH_STATUS = 0)
BEGIN
if (@i_include = 1)
set @i_sql_inc = @i_sql_inc +', ' + '[' + @IxColumn + ']'
else
begin
set @i_sql_col = @i_sql_col + ', ' + '[' + @IxColumn + ']'
if (@i_desc = 1)
set @i_sql_col = +@i_sql_col + ' DESC'
end
FETCH NEXT FROM cIxColumn INTO @IxColumn,@i_include,@i_desc
END
CLOSE cIxColumn
DEALLOCATE cIxColumn

-- remove leading ','
if (left(@i_sql_inc,1) = ',')
set @i_sql_inc = right(@i_sql_inc,len(@i_sql_inc)-1)

if (left(@i_sql_col,1) = ',')
set @i_sql_col = right(@i_sql_col,len(@i_sql_col)-1)

SET @IXSQL = @IXSQL + @i_sql_col + ')'
if (@i_sql_inc <> '')
set @IXSQL = @IXSQL + ' INCLUDE (' + @i_sql_inc + ')'

set @IXSQL = @IXSQL + ' WITH (PAD_INDEX = ' + CASE WHEN @is_padded = 1 THEN 'ON' ELSE 'OFF' END + ', ' +
+ 'ALLOW_ROW_LOCKS = ' + CASE WHEN @allow_row_locks = 1 THEN 'ON' ELSE 'OFF' END + ', ' +
+ 'ALLOW_PAGE_LOCKS = ' + CASE WHEN @allow_page_locks = 1 THEN 'ON' ELSE 'OFF' END + ', ' +
+ 'FILLFACTOR = ' + rtrim(@fill_factor) + ') ' +
+ 'ON [' + @DataSpaceName + ']'

print @IXSQL

FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID, @fill_factor, @is_padded, @allow_row_locks, @allow_page_locks, @DataSpaceName, @i_schema
END

CLOSE cIX
DEALLOCATE cIX
Mayank Khatri
Mayank Khatri
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2084 Visits: 1717
I am sorry, but whats the question here?
jeffryfranksjunk
jeffryfranksjunk
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 90
no question; updated / improved a script and wanted the code at the top level rather than the bottom of a thread.
wm.thompson1
wm.thompson1
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 281
This is precisely what I was needing. I imagine that was a lot of work, so thanks. The script at http://connectsql.blogspot.com looked familiar. I'll add drop code here with tildes sprinkled throughout to prevent accidental deletion.
~ William



-- Script to Generate Drop to go with the adds in the post
-- From web resource: http://connectsql.blogspot.com/2009/07/script-to-create-all-primary-and.html
-- For TSQL Syntax see http://msdn.microsoft.com/en-us/library/ms190273.aspx
/*

EXEC sp_Opti_PKConstraints_DROP
@TableName = 'Widget'

*/

ALTER PROC sp_Opti_PKConstraints_DROP
@TableName VARCHAR(128) = NULL

AS

DECLARE @DoPrimaryOnly BIT
SET @DoPrimaryOnly = 1
DECLARE cPK CURSOR FOR

SELECT DISTINCT C.TABLE_NAME, C.CONSTRAINT_NAME , F.NAME
FROM SYS.INDEXES I
INNER JOIN SYS.FILEGROUPS F
ON I.DATA_SPACE_ID = F.DATA_SPACE_ID
INNER JOIN SYS.ALL_OBJECTS O
ON I.[OBJECT_ID] = O.[OBJECT_ID]
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON O.NAME = C.TABLE_NAME
WHERE (1=1)
AND C.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND C.Table_Name = @TableName
ORDER BY C.TABLE_NAME

DECLARE @PkTable SYSNAME
DECLARE @PkName SYSNAME
DECLARE @FileName SYSNAME
DECLARE @IfExists VARCHAR(2000)

-- Loop through all the primary keys
OPEN cPK
FETCH NEXT FROM cPK INTO @PkTable, @PkName , @FileName
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @PKSQL NVARCHAR(4000) SET @PKSQL = ''

SET @IfExists =
'' + CHAR(13)

--########################## DROP ##############################

SET @PKSQL = @IfExists + CHAR(13) + CHAR(13) + '-- ' + @PkTable + CHAR(13) +

'IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = ' + CHAR(13) +
'OBJECT_ID(N''[dbo].[' + @PkTable + ']'' ' + ' ' + CHAR(13) +
'AND name = N''' + @PkName + ''')' + ' ' + CHAR(13) +
'BEGIN' + CHAR(13) +
'~~Caution ALTER TABLE [dbo].' + @PkTable + ' ' + CHAR(13) +
' ~~DROP CONSTRAINT ' + @PkName + CHAR(13) +
'END' + ' ' + CHAR(13)

--########################## / DROP ############################

-- For the drop, we're skipping the columns


SET @PKSQL = ~~@PKSQL + ')' + CHAR(13) +
' ON '+@FileName
-- Print the primary key statement
PRINT @PKSQL

FETCH NEXT FROM cPK INTO @PkTable, @PkName, @FileName
END
CLOSE cPK
DEALLOCATE cPK

EndProcessing:




Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search