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