Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Updated SQL Server 2005: Script all Indexes Expand / Collapse
Author
Message
Posted Monday, June 8, 2009 11:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 29, 2014 3:12 PM
Points: 3, Visits: 89
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

Post #730839
Posted Monday, June 8, 2009 11:45 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 7:47 AM
Points: 1,800, Visits: 1,543
I am sorry, but whats the question here?
Post #730853
Posted Monday, June 8, 2009 12:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 29, 2014 3:12 PM
Points: 3, Visits: 89
no question; updated / improved a script and wanted the code at the top level rather than the bottom of a thread.
Post #730918
Posted Saturday, December 3, 2011 11:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 10:29 AM
Points: 1, Visits: 197
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:



Post #1215881
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse