I am working in SQL Server 2008 R2 and I have written a stored procedure that adds constraints to several columns in several tables in my database. However, I am finding that the stored procedure fails because it cannot set a particular column as a Primary Key due to an index on the column. This is the case for many of the columns in various tables.
Is there a way to ALTER COLUMN to NOT NULL and set it as the PRIMARY KEY without removing the index? Otherwise, is there a good way to disable or drop the index then enable or recreate it after the ALTER COLUMN statements?
Thank you all for your comments. After some research, I was able to determine how to do this dynamically using the exact tables mentioned by @mwigdahl. See the code below:
DECLARE @indexName nvarchar(254),
@tableName nvarchar(254),
@indexType nvarchar(254),
@columnName nvarchar(254),
@isPadded integer,
@ignore_dup_key integer,
@allow_row_locks integer,
@allow_page_locks integer,
@fillFactor integer
SELECT
@indexName = i.name,
@tableName = o.name,
@indexType = i.type_desc,
@columnName = co.[name],
@isPadded = i.is_padded,
@ignore_dup_key = i.[ignore_dup_key],
@allow_row_locks = i.[allow_row_locks],
@allow_page_locks = i.[allow_page_locks],
@fillFactor = i.fill_factor
FROM sys.indexes AS i
INNER JOIN sys.objects AS o ON i.object_id = o.object_id
INNER JOIN sys.index_columns ic on ic.object_id = i.object_id AND ic.index_id = i.index_id
INNER JOIN sys.columns co on co.object_id = i.object_id AND co.column_id = ic.column_id
WHERE
i.[type] = 2 AND
o.[type] = 'U'
DECLARE @sql varchar(max)
SET @sql = 'DROP INDEX [' + @indexName + '] ON [' + @tableName + ']'
EXEC (@sql)
PRINT 'Dropped Index'
-- Do work here
DECLARE @pad_index nvarchar(3),
@ignore_dup nvarchar(3),
@row_locks nvarchar(3),
@page_locks nvarchar(3)
IF @isPadded = 0 SET @pad_index = 'OFF'
ELSE SET @pad_index = 'ON'
IF @ignore_dup_key = 0 SET @ignore_dup = 'OFF'
ELSE SET @ignore_dup = 'ON'
IF @allow_row_locks = 0 SET @row_locks = 'OFF'
ELSE SET @row_locks = 'ON'
IF @allow_page_locks = 0 SET @page_locks = 'OFF'
ELSE SET @page_locks = 'ON'
SET @sql = 'CREATE ' + @indexType + ' INDEX [' + @indexName + '] ON [' + @tableName + ']' +
'(' +
'[' + @columnName + '] ASC' +
') WITH (' +
'PAD_INDEX = ' + @pad_index + ',' +
'STATISTICS_NORECOMPUTE = OFF,' +
'SORT_IN_TEMPDB = OFF,' +
'IGNORE_DUP_KEY = ' + @ignore_dup + ',' +
'DROP_EXISTING = OFF,' +
'ONLINE = OFF,' +
'ALLOW_ROW_LOCKS = ' + @row_locks + ',' +
'ALLOW_PAGE_LOCKS = ' + @page_locks + ',' +
'FILLFACTOR = ' + CONVERT(nvarchar(100),@fillFactor) + ')' +
'ON [PRIMARY]'
PRINT @sql
EXEC (@sql)
PRINT 'Created Index'
I used the Script Index As
function in SSMS to see how the index would be created in T-SQL.
Then I queried the sys.indexes
and sys.index_columns
tables to determine which properties were available.
The code shows a query against those tables saving those properties to variables and then dropping and recreating the index. There were a few options that were not available in those tables that I had to hard code. Does anyone know if those OPTIONS are available somewhere in a system table?
I hope this helps someone else in the future.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With