June 8, 2009 at 11:20 am
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
June 8, 2009 at 11:45 am
I am sorry, but whats the question here?
June 8, 2009 at 12:56 pm
no question; updated / improved a script and wanted the code at the top level rather than the bottom of a thread.
December 3, 2011 at 11:27 am
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)
ANDC.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) +
'~~CautionALTER 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:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy